SAP HANA SQL Script where null = null not work.

[former_member822625]

Hi expert,

[1]

select tab1."BUKRS", tab1."AMT", tab2."AMT" from tab1 left join tab2

on tab1."BUKRS" = tab2."BUKRS";

The result is 1000 300 5000.

[2]

But, if field "ACCT", which is NULL, is added to where condition, e.g,

select tab1."BUKRS", tab1."AMT", tab2."AMT" from tab1 left join tab2

on tab1."BUKRS" = tab2."BUKRS" and tab1."ACCT" = tab2."ACCT";

The result is 1000 300 NULL.

The value of tab2."AMT"(5000) is not fetched.

[3]

So , my question is:

Fields which are NULL, can not be used in where condition ?

Tks.

[Jrg_Brandeis · Accepted solution]

Hi Wei,

every comparison with NULL results always in the logical value UNKNOWN. But in JOIN as well as in WHERE conditions, you need the logical value TRUE so that a record is considered.

In a WHERE condition, the IS NULL predicate is useful. Be aware: x = NULL is not working, because it is a comparison with NULL. Use x IS NULL instead.

In a JOIN condition, you can map NULL to a valid value (e.g. the empty string '') with the SQL-Function IFNULL( ):

select tab1."BUKRS", 
       tab1."AMT", 
       tab2."AMT" 
  from tab1 
  left join tab2
    on tab1."BUKRS" = tab2."BUKRS" 
   and IFNULL(tab1."ACCT", '') = IFNULL(tab2."ACCT", '');<br>

In this example, you have to be aware that the empty string could also be part of your data. In this case you have choose an other replacement value.

With best regards,

Jörg

[eason_chen]

Hi,

I consider it should be normal as null = null is not a valid expression.
Alternatively, you may modify your query as following if you want it to be shown in result when both of them are null.
select tab1."BUKRS", tab1."AMT", tab2."AMT" from tab1 left join tab2 on tab1."BUKRS" = tab2."BUKRS" and (tab1."ACCT" = tab2."ACCT" or (tab1."ACCT" IS NULL and tab2."ACCT" is NULL));

Best Regards,
Eason Chen

[former_member822625]

thanks a lot .