Oracle Table NULL column validation. Update all error message at once

Requirement:

Consider table stg_table with columns c1 c2 c3 and c4(no index or not null constraints) and values as mentioned below

id    c1    c2    c3    c4
-----------------------------------
#1    1    2              5
#2    3            3
#3                         6

We wish to do NULL validation on all columns and update the error in error_msg column .For some reason NOT NULL constraint is not put on these columns.

Required output

id    c1    c2    c3    c4    error_msg
----------------------------------------------------
#1    1    2               5    C3 NULL
#2    3             3            C2, C4 NULL
#3                           6    C1, C2, C3 NULL

Solution

One simple method is to write as many number of sql UPDATE statement as number of columns that needs to be validated

UPDATE stg_table set error_message = error_msg||'C1, ' where c1 is null
UPDATE stg_table set error_message = error_msg||'C2, ' where c2 is null
.
.
and finally if error message was updated by any of above row then append ' NULL' to error_msg
UPDATE stg_table set error_message = decode(error_message,null,null,error_msg||' NULL')

Other method to perform same thing with single update statement is

[sourcecode language="sql"]
SELECT   id, DECODE (c1, NULL, 'C1,', NULL )
|| DECODE (c2, NULL, 'C2,', NULL)
|| DECODE (C3, NULL, 'C3,',NULL)
|| DECODE (c3, NULL, 'C3,', NULL) err_msg
FROM stg_table
[/sourcecode]

Above statement gives output like

id    err_msg
----------------------
1    C3,
2    C2,C4,
3    C1,C2,C3,

Now we need to remove the extra comma from end and append 'NULL' string

[sourcecode language="sql"]
SELECT id, DECODE (t2.err_msg,
NULL, NULL,
SUBSTR (t2.err_msg, 1, LENGTH (t2.err_msg) - 1)
|| ' NULL'
) error_msg
FROM (SELECT    DECODE (emp_no, NULL, 'EMP NO,', NULL )
|| DECODE (invoice_no, NULL, 'INVOICE NO,', NULL)
|| DECODE (invoice_date, NULL, 'INVOICE DATE,',NULL)
|| DECODE (vendor_no, NULL, 'VENDOR NO,', NULL) err_msg,
ID
FROM xx_invoices_stg) t2
[/sourcecode]

id    err_msg
----------------------
1    C3 NULL
2    C2,C4 NULL
3    C1,C2,C3 NULL

Now simply update stg table correlating above query id with table id

[sourcecode language="sql"]
UPDATE stg_table t1
SET        error_msg =
(SELECT DECODE (t2.err_msg,
NULL, NULL,
SUBSTR (t2.err_msg, 1, LENGTH (t2.err_msg) - 1)
|| ' should Not be NULL'
) error_msg
FROM (SELECT    DECODE (emp_no, NULL, 'EMP NO,', NULL )
|| DECODE (invoice_no, NULL, 'INVOICE NO,', NULL)
|| DECODE (invoice_date, NULL, 'INVOICE DATE,',NULL)
|| DECODE (vendor_no, NULL, 'VENDOR NO,', NULL) err_msg,
ID
FROM xx_invoices_stg) t2
WHERE t2.ID = t1.ID)
[/sourcecode]