Hana and 'is distinct from' operator

[former_member509621]

Hello!

In PostgreSQL and MySQL exists 'is distinct from' operator (SQL:2003).
Is it possible to achieve the same functionality in SAP Hana?
It is very important for our usage, we have a large number of such queries.

Example: CONN_ID column is defined as nullable integer. I have to select rows where CONN_ID has changed (also to NULL or from NULL).

SELECT
   A."MATNR" as a_material,
   B."MATNR" as b_material,
   A."CONN_ID" as old_id,
   B."CONN_ID" as new_id
  FROM "MATERIAL_CONN_VIEW" A full outer join "MATERIAL_CONN_LATEST" B on (A."MATNR" = B."MATNR")
  WHERE
    (A."CONN_ID" != B."CONN_ID");  /* <=> or 'is distinct from' ? */

[lbreddemann]

The IS DISTINCT FROM/IS NOT DISTINCT FROM predicates can be expressed using IS NULL/IS NOT NULL and the common equality/inequality predicates.

A discussion on stack overflow summarises that nicely:

https://stackoverflow.com/questions/10416789/how-to-rewrite-is-distinct-from-and-is-not-distinct-fro...

a IS DISTINCT FROM b can be rewritten as:
((a <> b OR a IS NULL OR b IS NULL) AND NOT (a IS NULL AND b IS NULL))

a IS NOT DISTINCT FROM b can be rewritten as:
(NOT (a <> b OR a IS NULL OR b IS NULL) OR (a IS NULL AND b IS NULL))

[franois_henrotte]

1) indeed != means the same as NE

2) the COALESCE prevents from having null values.

anyway the explanation from Lars seems to answer to your question ?

[franois_henrotte]

It looks good, only the syntax should be slightly different in pure ABAP it would be :

SELECT a~matnr AS a_material,
       b~matnr AS b_material,
       a~connid AS old_id,
       b~connid AS new_id
  INTO TABLE @DATA(lt_results)
  FROM material_conn_view AS a
  LEFT OUTER JOIN material_conn_latest AS b
    ON a~matnr EQ b~matnr
  WHERE a~connid NE b~connid.

In SQLscript it would be :

SELECT ( CASE  WHEN a.matnr IS NOT NULL THEN a.matnr
               WHEN b.matnr IS NOT NULL THEN b.matnr END ) as matnr,
       COALESCE (a.connid,'') AS old_id,
       COALESCE (b.connid,'') AS new_id
  INTO :lt_results
  FROM material_conn_view AS a
  FULL OUTER JOIN material_conn_latest AS b
    ON a.matnr EQ b~matnr
  WHERE a.connid NE b~connid;

[former_member509621]

Than you for your comment, by we need pure Hana-SQL query.

WHERE a~connid NE b~connid.

Isn't NE the same as != operator?

"NULL NE anything" will return NULL so in WHERE clause would mean FALSE