CODEWITHSUNDEEP
sqloracle-database
Ashok Kumar
Ashok Kumar

Reputation: 145

Find out what are all the columns having NULL values in the table for a given row in Oracle

For Example if the table is like below

ID  FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY      DESCRIPTION
---- ---------- ---------- --------- --------- ---------- -------------------
01   Jason      Martin     25-JUL-96 NULL         1234.56 NULL       Programmer
02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester

For the record ID "1", END_DATE and CITY are having null values. How to get these values in a single query?

Upvotes: 0

Views: 1243

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

You can use this function:

create or replace function list_null_cols(i_table in varchar2, 
  i_filter in varchar2 default '') return varchar2 is

  v_sql varchar2(32000);
  cursor cols is 
    select column_name cn from user_tab_cols 
      where table_name = upper(i_table) and nullable='Y';
  ret varchar2(32000);
begin
  for c in cols loop
    v_sql := v_sql || 'max(nvl2('||c.cn||',null,'''||c.cn||',''))||';
  end loop;
  if length(v_sql) = 0 then 
    return 'no columns found'; 
  end if;
  v_sql := 'select '||rtrim(v_sql, '|| ') ||' from '||i_table||' '||i_filter;
  begin 
    execute immediate v_sql into ret;
  exception when others then
    return 'error: '||sqlerrm;
  end;
  return rtrim(ret, ',');
end;

First parameter is table name, second, optional is where... filter. If you omit second parameter all rows in table will be analyzed, like in examples:

create table test_table (ID varchar2(2), FIRST_NAME number, LAST_NAME number,  
  START_DATE number, END_DATE number, SALARY number, CITY number, DESCRIPTION number);

insert into test_table values ('01', 1, 1, 1, null, 1, 1, 1);
insert into test_table values ('02', 2, 2, 2, 2, 2, null, 2);

select list_null_cols('test_table') list from dual;

LIST               
-------------------
END_DATE,CITY      

select list_null_cols('test_table', ' where id=''01''') list from dual;

LIST               
-------------------
END_DATE      

select list_null_cols('test_table', ' where salary=2') list from dual;

LIST               
-------------------
CITY

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can use case to figure out what has a NULL value and concatenate the results together:

select ((case when id is null then 'id;' end) ||
        (case when first_name is null then 'firstname;' end) ||
        . . .
        (case when description is null then 'description;' end)
       ) as NullColumns
from table;

Note: Oracle treats NULL values as empty strings for the concatenation operator.

Upvotes: 2

Related Questions