case statement with isnull ( ) in calculated column in HANA

[former_member383962]

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!!!

[SergioG_TX]

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...

[former_member383962]

Hi 9958e4b6df99431a84a41b015b639ac8 ,

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.

[SergioG_TX]

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)