SQL "IS NOT NULL" compensation for SAP HANA SQLScript

Question

Actually I tried to run a is null SQL Statement on a SAP HANA Database using the SAP HANA Studio. This does not work because SQLScript has no is not null or is null function. My statement looks like:

Select *
From MSEG
Where KDAUF is null

Unfortunately it does not work. Does anybody know an alternative approach which is practicable using SAP HANA SQLScript? On the internet I found a hint to either use NULLIF or COALESCE. But I neither know how to use this function nor to adapt it to a working WHERE condition.

Solution

NULLIF ( expression , expression ) Returns the same type as the first expression.

NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression WHERE IFNULL(KDAUF , comparison value) I never use this one

The other is coalesce where if the first vaLue is null, the second VALUE is returned:

WHERE VALUEa = COALESCE(KDAUF,valuea)

here, if kdaUf Is null, coalesce will will return valuea, the default if the first value is null. Since valuea = valuea, the where clause will be true, which is just another way of validating that KDAUF IS NULL

Comments

[user3665218]

Hello. Thanks for your answer. Unfortunately I can not understand your COALESCE solution. So maybe, for a better understanding, you can implement the COALESCE solution using my little code example: Select * From MSEG Where KDAUF is null

[Lynne Davidson]

SELECT * FROM MSEG WHERE 1 = COALESCE(KDAUF,1). ..... If Kdauf is NULL, COALESCE will go to the next option, 1, 1=1, and the WHERE clause will return TRUE. If KDAUF is NOT null, COALesce will return the value of kduaf (I'm assuming it will never be 1), kduaf will not be equal to 1, and the where clause will fail. A bit counter-intuitive, but very useful.

[Lynne Davidson]

Note - any value can be used for the where initial value and the coalesce function's second variable, as long as they're equal.