CODEWITHSUNDEEP
sqlamazon-redshiftnvl
sia
sia

Reputation: 577

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.

Upvotes: 0

Views: 1661

Answers (2)

abcool
abcool

Reputation: 95

This will work:

find gender first (SELECT gender FROM member WHERE .....)

if gender is null
begin
select 'not available' as result
end
else
begin
<-- do your query-->
end

Upvotes: -1

ScaisEdge
ScaisEdge

Reputation: 133410

You can try with case 

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

Upvotes: 1

Related Questions