sqlamazon-redshiftnvl

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.

Solution

  • You can try with case 

    select case gender when is null then 'not available' else gender 
from my_table