1

I am working on a CICS screen where the users can query, add, update or delete data in a table. In one of my tables, the unique key is made of 5 fields, 2 of which can be NULL. In my cursor I'm having to use an

AND   (COLUMN_1 = :V??-REC.COLUMN-1 OR COLUMN-1 IS NULL)

and then I am having to run an evaluation on the results and display the closest matching result.

How I am currently finding NULL entries:

EXEC SQL
    DECLARE V000001-CUR CURSOR FOR
    SELECT DOCM_N,
           DOCM_TYPE_T,
           REJ_RESN_C
    FROM   V000001
    WHERE  DOCM_N      = :V000001-REC.DOCM-N
    AND    DOCM_TYPE_T = :V000001-REC.DOCM-TYPE-T
    AND   (REJ_RESN_C  = :V000001-REC.REJ-RESN-C OR
           REJ_RESN_C IS NULL)
END-EXEC

I have read online that null indicators can be used to insert NULL into the table when using update or insert.

For example:

EXEC SQL
    UPDATE V000001
    SET    DOCM_N      = :V000001-REC.DOCM-N,
           DOCM_TYPE_T = :V000001-REC.DOCM-TYPE-T,
           REJ_RESN_C  = :V000001-REC.REJ-RESN-C
                           :WS-REJ-RESN-C-IND
    WHERE  DOCM_N      = :WW-DOCM-N
    AND    DOCM_TYPE_T = :WW-DOCM-TYPE-T
END-EXEC

My question. Can I use the null indicator in the WHERE clause?

Such as:

EXEC SQL
    DECLARE V000001-CUR CURSOR FOR
    SELECT DOCM_N,
           DOCM_TYPE_T,
           REJ_RESN_C
    FROM   V000001
    WHERE  DOCM_N      = :V000001-REC.DOCM-N
    AND    DOCM_TYPE_T = :V000001-REC.DOCM-TYPE-T
    AND    REJ_RESN_C  = :V000001-REC.REJ-RESN-C
                            :WS-REJ-RESN-C-IND
END-EXEC
Thraydor
  • 140
  • 1
  • 12

1 Answers1

2

!!! For DB2 Version 8 and onwards !!!

To use a NULL indicator in the WHERE clause for a SELECT, UPDATE or DELETE you need to use IS NOT DISTINCT FROM

Because NULL is an unknown it doesn't have a value, it can never equal anything - not even another NULL.

So, when you want to find an entry by matching on user input - after you have moved the user input to the required fields - you would normally use something like:

EXEC SQL
    SELECT COLUMN_1,
           COLUMN_2,
           COLUMN_3
    FROM   V000001
    WHERE  COLUMN_1 = :V000001-REC.COLUMN-1
    AND    COLUMN_2 = :V000001-REC.COLUMN-2
    AND    COLUMN_3 = :V000001-REC.COLUMN-3
END-EXEC

Or the equivalent UPDATE or DELETE SQL depending on the required function.

If COLUMN_1 has the potential to be NULL we would use a null indicator when fetching the data into a cursor and when trying to update/add a NULL to a table - but matching on a NULL in a WHERE clause isn't as simple.

Because NULL doesn't contain a value we CAN'T use the null indicator in the WHERE statement like so:

WHERE COLUMN_1 = :V000001-REC.COLUMN-1 :COLUMN-1-IND

and once you starting using brackets and OR xxxx IS NULL (seen below) you need to start programming in all sorts of other checks so you don't retrieve, update or delete the wrong row

WHERE (COLUMN_1 = :V000001-REC.COLUMN-1 OR COLUMN_1 IS NULL)

The correct way to use a null indicator in the WHERE clause is like so:

EXEC SQL
    SELECT COLUMN_1,
           COLUMN_2,
           COLUMN_3
    FROM   V000001
    WHERE  COLUMN_1 IS NOT DISTINCT FROM
                      :V000001-REC.COLUMN-1
                         :COLUMN-1-IND
    AND    COLUMN_2 = :V000001-REC.COLUMN-2
    AND    COLUMN_3 = :V000001-REC.COLUMN-3
END-EXEC

Since a working WHERE clause in a SELECT can be transferred to other statements, this also works for finding an exact match when doing an UPDATE or a DELETE

More reading: http://www.toadworld.com/platforms/ibmdb2/w/wiki/6842.nulls

Thraydor
  • 140
  • 1
  • 12