Skip to Content
-1

case statement with isnull ( ) in calculated column in HANA

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

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

    Add comment
    10|10000 characters needed characters exceeded

    • 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)