Resolving The Problem

A CASE statement within an IBM SQL/400 statement can prevent you from updating a field with a NULL value. In the following scenario, the initial statement (Statement #1) is trying to update FIELDB with the value of FIELDZ with a subselected join. When the statement is written like this, FIELDB in the initial file (FILE1) is updated even if the subselect produces a NULL value. You can see the results of this in the result set FILE1 - without CASE. As you can see, the NULL value is placed in the third record because the join failed to find a match.

Statement #1
UPDATE LIBRARY/FILE1 SET
FIELDB = (SELECT FIELDZ
FROM LIBRARY/FILE2 WHERE
FILE1.FIELDC = FILE2.FIELDX AND FILE1.FIELDD = FILE2.FIELDY)
WHERE FILE1.FIELDA = 'ST'


FILE1
FIELDA FIELDB FIELDC FIELDD
TS 1.99 1 1
ST 1.99 1 2
ST 1.99 1 3
ST 1.99 2 2
TS 1.99 2 3
ST 1.99 2 4
GG 1.99 1 1

FILE2
FIELDX FIELDY FIELDZ
1 1 2.00
1 2 2.00
1 4 2.00
2 2 2.00
2 3 2.00
2 4 2.00

Results - FILE1 - without CASE
FIELDA FIELDB FIELDC FIELDD
TS 1.99 1 1
ST 2.00 1 2
ST - 1 3
ST 2.00 2 2
TS 1.99 2 3
ST 2.00 2 4
GG 1.99 1 1


A CASE statement can be added to the subselect to prevent FIELDB from being updated with a NULL value. As you can see from the statement below (Statement #2) and the results FILE 1 - with CASE, this process updates FIELDB with itself if the join produces a NULL.


Statement #2
UPDATE LIBRARY/FILE1 SET
FIELDB = (SELECT
CASE
WHEN FIELDZ IS NULL THEN FILE1.FIELDB
ELSE FIELDZ
END
FROM LIBRARY/FILE2 WHERE
FILE1.FIELDC = FILE2.FIELDX AND FILE1.FIELDD = FILE2.FIELDY)
WHERE FILE1.FIELDA = 'ST'


Results - FILE1 - with CASE
FIELDA FIELDB FIELDC FIELDD
TS 1.99 1 1
ST 2.00 1 2
ST 1.99 1 3
ST 2.00 2 2
TS 1.99 2 3
ST 2.00 2 4
GG 1.99 1 1