Print NULL Value column Name

Question

I have a table with the columns ColA, ... , ColE as below. I need to check each record and show the column name which has NULL value in another existing column called Comments

ColA   ColB    ColC    ColD   ColE   Comments
----   ----    -----   ----   ----   ------------------------------
1      7       3       4      NULL   NULL VALUE In ColE      
2      9       NULL    12     NULL   NULL VALUE in ColC, ColE   
3      NULL    NULL    NULL   10     NULL VALUE IN ColB, Colc, ColD 

The Output should be similar to the Comments column from the above result set.

Answer 1

This can be done using only plain SQL (no procedures or functions etc.), for example with a MERGE statement. A complete session, starting with setting up the table, inserting values, adding one more column for comments, then the MERGE statement, and the results "before" and "after" are shown below.

I added one more row of data, where there are no NULL values. You always need something like this to test the solution: does it work correctly when there are no NULL values in a row?

The real work is done in the subquery labeled x in the solution below. First I unpivot the data from the original table (adding the ROWID so I can identify each row later). UNPIVOT requires Oracle 11.1 or higher; I also use LISTAGG which requires Oracle 11.2 or higher. You tagged your post with both oracle11g and oracle10g so I don't really know what you have, and in any case 11g and such are marketing names; the proper version is 11.1 or 11.2 (or, really, 11.2.0.4.0 and similar). You can find out your version with select * from v$version.

If you don't have Oracle 11.2, the string aggregation can be done with other tools (hierarchical query, or XMLAGG, etc.) - Stack Overflow is full of questions about how that is done. Similarly, if you don't have Oracle 11.1, unpivoting can be done with a cross join; I didn't check, but I assume there are lots of questions about that on Stack Overflow also. Both operations are standard, and unrelated to your question; if you need help, start by searching this site, and write back if you run into difficulties.

So, back to my approach. I unpivot the data, keeping track of rowid. In the unpivot operation I include nulls (which is not the default, so include nulls is needed). Then from the result I keep only the rows where the value is NULL. Then I group by RID and aggregate with LISTAGG. This prepares the subquery x for the MERGE operation, which is standard beyond this point.

Note about efficiency I asked the OP in a comment whether fast execution or simplicity of the solution (easier maintenance) should be preferred. The OP said easy maintenance is more important. The solution below is clearer, but it is not efficient for the following reason. In the original table the values in the five columns are already "grouped" by the fact they appear in the same row. Unpivoting loses this information; then we must group by ROWID again, to create the LISTAGG strings and to prepare for MERGE. The GROUP BY operation is expensive, and it wouldn't be needed if we wrote code that didn't break up the input rows. But writing a solution that doesn't break up the input rows, while possible, is messier, especially as the number of columns increases.

SETUP

create table tbl(cola number, colb number, colc number, cold number,cole number);
insert into tbl(cola, colb, colc, cold, cole) values (1,    7,    3,    4, NULL);  
insert into tbl(cola, colb, colc, cold, cole) values (2,    9, NULL,   12, NULL);
insert into tbl(cola, colb, colc, cold, cole) values (3, NULL, NULL, NULL,   10);
insert into tbl(cola, colb, colc, cold, cole) values (0,    1,    2,    3,    5);
commit;

alter table tbl add (comment_about_nulls varchar2(1000));

select * from tbl;

COLA COLB COLC COLD COLE COMMENT_ABOUT_NULLS                    
---- ---- ---- ---- ---- ----------------------------------------
   1    7    3    4                                              
   2    9        12                                              
   3                  10
   0    1    2    3    4

SOLUTION

merge into tbl
using (
        select   rid, listagg(colname, ', ') within group (order by colname) str
        from     (select rowid as rid, cola, colb, colc, cold, cole from tbl)
        unpivot  include nulls (val for colname in 
                          (cola as 'ColA', colb as 'ColB', colc as 'ColC',
                                           cold as 'ColD', cole as 'ColE'))
        where    val is null
        group by rid
      ) x
  on (tbl.rowid = x.rid)
when matched then update
  set tbl.comment_about_nulls = 'NULL VALUE IN ' || x.str
;

select * from tbl;

COLA COLB COLC COLD COLE COMMENT_ABOUT_NULLS                    
---- ---- ---- ---- ---- ----------------------------------------
   1    7    3    4      NULL VALUE IN ColE                      
   2    9        12      NULL VALUE IN ColC, ColE                
   3                  10 NULL VALUE IN ColB, ColC, ColD          
   0    1    2    3    5  

Answer 2

is that the code you want?

declare 
  v_table_name  varchar2(31)  := 'MYTABLE';
  v_sql         varchar2(4000);
  v_columnName  varchar2(100);
  v_col         pls_integer; 
  cm            varchar2(1); 
  i             pls_integer := 0;   
begin
for b in ( select rownum rnum, t.rowid rid, t.* from mytable t order by rownum )
loop
 begin
 for c in ( select t.* from user_tab_columns t where t.table_name = v_table_name order by column_name ) 
 loop    
   v_sql := 'select ' || c.column_name  || ' from ' || v_table_name || ' t where t.rowid = '''||b.rid||'''';
   execute immediate v_sql into v_col; 
   if nvl(v_col,0)=0 then     
     if i>0 then cm:=','; end if;
     v_columnName := v_columnName||cm||c.column_name; 
     i:=+1;
   end if; 
 end loop;
    dbms_output.put_line(rpad(nvl(to_char(b.rnum),'NULL'),5,' ')||' '||rpad(nvl(to_char(b.cola),'NULL'),5,' ')||' '||rpad(nvl(to_char(b.colb),'NULL'),5,' ')||' '||rpad(nvl(to_char(b.colc),'NULL'),5,' ')||' '||rpad(nvl(to_char(b.cold),'NULL'),5,' ')||' '||rpad(nvl(to_char(b.cole),'NULL'),5,' ')||' '||'NULL VALUE In '||v_columnName); 
    v_columnName := null; i := 0; cm := null;
  exception when no_data_found then continue;
 end; 
end loop; 
end; 

by considering colA - E as number type and comments is non-existent in mytable.