Search code examples
sqlhivecoalesce

Coalesce in Hive returns null

I want to take first non-missing value:

and it doesnt work properly

,coalesce(var1,var2,var3)

although 

var1=null
var2=null
var3=variable

i got null as a result of coalesce.

Solution

  • Two possibilities. The first is that all three are null. You can test this by adding a fourth value that is a default to see if it is chosen.

    My suspicion is the second possibility -- that null is really 'null'. If so, coalesce() will not work directly.

    If this is the case, you can use nullif() or a case expression:

    coalesce(case when var1 <> 'null' then var1 end), 
         case when var2 <> 'null' then var2),
         var3)