NVL2

Return a specific value when an expression evaluates to not null and a specific value when the same expression evaluates to null.

NVL2( expression, not_null_return_value, null_return_value )

The second and third arguments to the function must have compatible data types.

For example, when the city column is not null, return the city name, and when it is null, return

No
        City

premdb=# select teamid, nvl2(city, city, 'No City') from team order by 1 desc;
 teamid |     city      
--------+---------------
     50 | No City
     49 | No City
     48 | No City
     47 | Wolverhampton
     46 | London
     45 | Wigan
     44 | London
...

You can use the DECODE function to produce the same results. For example:

premdb=# select teamid, decode(city, null, 'No City', city) from team order by 1 desc;
 teamid |     city      
--------+---------------
     50 | No City
     49 | No City
     48 | No City
     47 | Wolverhampton
     46 | London
     45 | Wigan
     44 | London
...