ISNULL function in SQL script calc view

[patrickbachmann]

Hello everyone,

Has anybody had any success using the ISNULL SQL function in SQL Script calculation view?  I can not seem to get it to work in my view nor in SQL Editor.  I searched the HANA SQL Reference manual and I do not see any mention of it.

Thanks,

-Patrick

[Former Member · Accepted solution]

Hi Patrick,

There is "isnull" function explained in the SQL script guide - http://help.sap.com/hana/hana_dev_sqlscript_en.pdf

Thanks,

Anooj

[patrickbachmann · Accepted solution]

Weird, the SP04 guide you posted is 56 pages and shows ISNULL yet I seem to have 127 page version of same SP04 document and do not see ISNULL mentioned.  Thank you for the link.  I still can't get the syntax to work using direct SQL in the SQL editor. 

[patrickbachmann · Accepted solution]

Anybody have example of syntax?  ie: I want to select fields A, B, C and if B is null then I want to fill with something else.

[Ravi_Channe · Accepted solution]

Find the syntax below:

, CE_CALC('IF (isnull("B1"), "B2", '''' )', varchar(2)) AS "CC"]);

Here it checks if the value of B1 is null, then use value from B2 or else use blank '''' (4 single quotes).

You can modify this as per your requirement.

Regards,

Ravi

[Ravi_Channe · Accepted solution]

If it is SQL based script (I am not sure if your SQL script based view is based on CE functions or simple SQL), then you can use it following way:

ifnull(SPRAS, 'E')

Here it would check if SPRAS is null, then it will replace it with 'E'.

Regards,

Ravi

[Former Member · Accepted solution]

Hi All,

For SQL Script direct you can use like:

SELECT

case when field IS NULL then 'kkk' else 'lll' end as AAA

FROM....

or on WHERE

where field IS NULL;

So, you can use IS NULL or IS NOT NULL predicates, or functions like ifnull, nullif, coalesce...

Regards, Fernando Da Rós

[patrickbachmann · Accepted solution]

Can I only do it using a CE function? 

[Ravi_Channe · Accepted solution]

Hi Patrick,

I posted it using CE_CALC function above. How do you wish to use it anyway ?

Regards,

Ravi

[patrickbachmann · Accepted solution]

Oh sorry I did not see your other post.  Let me try that.  Thanks

[patrickbachmann · Accepted solution]

Ravi did you mean to type ifnull or isnull?

[patrickbachmann · Accepted solution]

Thanks everyone.  Ravi it seems IFNULL is what I wanted instead of ISNULL.  Strange but the HANA_SQL_EN.PDF that I was looking at did not even have ISNULL mentioned.  But once I searched for IFNULL it led me to the correct function I was looking for with examples.

[Ravi_Channe · Accepted solution]

Ah.. the mysterious ways in which HANA works.

IFNULL used in SQL script takes and argument and if the value is null uses the second argument value supplied.

ISNULL works like a Boolean function which returns 1 (TRUE) if the value of argument is NULL.

Both the functions are used in different context and I am sure interpreted by different engines (probably developed by different teams in SAP, who knows ).

So depending upon where it is used, you may have to use the appropriate function.

Regards,

Ravi