sia sia -5 years ago 116
SQL Question

can nvl return different data type as its argument?

in the following query,

select nvl(gender, 'not available') from member

I want the query returns 'Not Available' when gender is null. but it seems that the second argument has to be the same data type as first argument.(in this example gender is CHAR(1) but second argument is varchar or char(n) rather than CHAR(1). Is there any solution besides changing data type in main table?
I am using Amazon Redshift.
I am using the nvl statement in group by as well so I need the solution to be concise as much as possible.

Answer Source

You can try with case

select case gender when is null then 'not available' else gender 
from my_table 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download