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
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