case when condition for replacing null value to string and non-null values to keep it as same.

[deviv]

Hi team,

I am trying to execute the below condition which is if the column has null values (∅) symbol, I want to replace that into 'no data'  string. If not the value should remain the same as the column.
While trying to execute this, I am getting error because one condition result is string if not the other is a number. How to overcome this issue?

sql:

CASE WHEN ${my_measure} IS NULL

THEN "No Data"

ELSE ${my_measure}

END ;;

[salehaxsid]

What you can do is cast string as an INTEGER like this:

sql:

CASE WHEN ${my_measure} IS NULL

THEN cast ("No Data" as INT)

ELSE ${my_measure}

END ;;

However, this is not the best practice to handle the type mismatch error. Case statements in SQL only works with same data type, instead of "no data" if you can add 0 then it will be better, else you can simply convert it into Integer using Cast function.