I'm trying to write a SQL select on a column with both numeric and NULL values - if the value is NUMERIC, it should be returned, but if NULL, then I want a string 'none' to be returned. I came up with the following, but it's giving an error

select ..., case when A.id is not null then A.id else (cast (A.id as string) 'none') end from ...

Can someone please advise what I'm doing wrong here?

CodePudding user response：

ALL of the when else portions of a CASE expression must resolve to compatible types. In this case, the when resolves as an int, but the else resolves as a string. If you want 'none' as one of the results, you must cast the numeric value on the when side so it's compatible:

case when A.id is not null then cast(A.id as string) else 'none' end

But I'd do it without a case expression at all:

coalesce(cast(a.id as string), 'none')