Skip to Content
-1

case statement with isnull ( ) in calculated column in HANA

Feb 07 at 09:37 AM

339

avatar image

Hi Experts,

I created a Calculated column with logic of

set value of calculated column = 0 or column value when the date is between the range of two dates and also check the QTY is null or not.

Code

ISNULL(CASE ( "SALE_DATE_KEY" >= "NEXT_MONTH_BEGIN_DATE_KEY"  AND  "SALE_DATE_KEY" <= "NEXT_MONTH_END_DATE_KEY" , "QTY" ,0))

But it throws an ERROR.

Here it have to check the QTY value if the QTY is null it has to be set to 0 other the QTY value has been set.

any suggestions!!!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Sergio Guerrero Feb 07 at 10:18 AM
-1

could you use the coalesce function ... so it returns the first non null value or your default zero, for example:

Coalesce(QTY, 0)

Then wrap your other logic around it...

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi Sergio Guerrero ,

Thanks for your reply.,

I tried with the coalesce ( )

if("SALE_DATE_KEY" >= "NEXT_MONTH_BEGIN_DATE_KEY"  AND  "SALE_DATE_KEY" <= "NEXT_MONTH_END_DATE_KEY" ,coalesce( "QTY" ,0),0)

this also throws null value only.. I need 0 instead of null.

0
if("SALE_DATE_KEY" >= "NEXT_MONTH_BEGIN_DATE_KEY"  AND  "SALE_DATE_KEY" <= "NEXT_MONTH_END_DATE_KEY" ,coalesce( "QTY" ,0), 0)

should return  0 if QTY is null , or 0 if the condition is false. Most likely your null is not a null, do you have a string "Null" or a null value? (represented as ? in HANA) 
0