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
...
Parent topic:Conditional Expressions