How to set null value in calculated column in HANA

[ghennac]

Hello,

I'm trying to create a few count distinct measures, so for some cases i would want to have null value for this dimension, to avoid the recond being counted.
So then when calculating this new dimension hana doens't let me set NULL value in the else.
Also important is to mention that i use column engine.

So does someone know how to set a null value for calculated column?

[Vitaliy-R]

Are you using SQL or calculation views? Could you share an example of the code youo are using and results you are getting?

[ghennac]

Hello Witalij, we're trying to create a calculated column in calculation view, where we're using column engine language.

[Amin_Omidy]

Hi Ghenadie,

To calculate a new dimension in SAP HANA, you can use the 'CASE' statement (similare to Oracle or MSSQL) to specify conditions and return the desired value. If you want to set the value to 'NULL' in the 'ELSE' part of the statement, you can use the keyword 'NULL' as the return value. Here's an example:

CASE
  WHEN <condition1> THEN <value1>
  WHEN <condition2> THEN <value2>
  ...
  ELSE NULL
END

In SAP HANA Column Engine, you can use a similar approach by 'IFNULL' function to handle 'NULL' values in your calculations. You can use it in combination with the 'CASE' statement to set a value to 'NULL' when certain conditions are met. Here's an example you can try:

IFNULL(
  CASE
    WHEN <condition1> THEN <value1>
    WHEN <condition2> THEN <value2>
    ...
    ELSE NULL
  END,
  NULL
)

The 'IFNULL' function will return the first argument if it is not 'NULL', and 'NULL' otherwise. In the above case, the second argument is always 'NULL', so the result will be 'NULL' if the 'CASE' statement returns 'NULL'.

Hope this helps,

[ghennac]

yeah, completely agree and thank you for explanations, i know that for sql language it will work, but a si mentioned in the description to create the calculated column we're using column engine language from hana and seems that null value can't be assigned to varchar for example.