Question Re: ISNULL function in SQL script calc view in Technology Q&A

ISNULL function in SQL script calc view

patrickbachmann — Mon, 19 Nov 2012 16:17:52 GMT

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

Re: ISNULL function in SQL script calc view

Former Member — Mon, 19 Nov 2012 16:24:14 GMT

Hi Patrick,

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

Thanks,

Anooj

Re: ISNULL function in SQL script calc view

patrickbachmann — Mon, 19 Nov 2012 16:31:55 GMT

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.

Re: ISNULL function in SQL script calc view

patrickbachmann — Mon, 19 Nov 2012 16:53:41 GMT

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.

Re: ISNULL function in SQL script calc view

Ravi_Channe — Mon, 19 Nov 2012 17:13:51 GMT

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

Re: ISNULL function in SQL script calc view

Ravi_Channe — Mon, 19 Nov 2012 17:16:24 GMT

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

Re: ISNULL function in SQL script calc view

Former Member — Mon, 19 Nov 2012 17:47:22 GMT

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

Re: ISNULL function in SQL script calc view

patrickbachmann — Mon, 19 Nov 2012 18:48:52 GMT

Can I only do it using a CE function?

Re: ISNULL function in SQL script calc view

Ravi_Channe — Mon, 19 Nov 2012 18:54:39 GMT

Hi Patrick,

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

Regards,

Ravi

Re: ISNULL function in SQL script calc view

patrickbachmann — Mon, 19 Nov 2012 19:00:19 GMT

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

Re: ISNULL function in SQL script calc view

patrickbachmann — Mon, 19 Nov 2012 19:14:07 GMT

Ravi did you mean to type ifnull or isnull?

Re: ISNULL function in SQL script calc view

patrickbachmann — Mon, 19 Nov 2012 19:37:19 GMT

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.

Re: ISNULL function in SQL script calc view

Ravi_Channe — Mon, 19 Nov 2012 19:46:01 GMT

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