Avatar of Wayne Burr
Wayne Burr
Flag for United States of America asked on

Best method to identify nulls in columns in all columns

I have a table with about 30+ columns.  Of all those columns I want to check every column to see if all the rows have nulls in them.
I want to display all the column names and if they were all nulls or not.

I started with a select statement with Case's but was not getting what I was hoping for so I figured there was a better way.

Any advice would be great!

Thanks in advance.

--Sample table
select AKAFirst,
AKAMiddle,
AKASurname,
AltAssess,
BirthCountry,
BirthPlace_City,
Birthplace_StateProvince,
CELDT_TestPurpose,
CMA_CSTTest,
CMA_ELA
from S_CA_STU_X;

--Display something like this:  (comments would not show ect)
AKAFirst    0               --this would be that all rows were null
AKAMiddle    8          --this would be 8 rows have data
AKASurname   15     --this wold be 15 rows have data
AltAssess   0              --this would be that all rows were null
...
Oracle Database

Avatar of undefined
Last Comment
johnsone

8/22/2022 - Mon
awking00

You might use a procedure like the following:
set serveroutput on
declare
v_sql varchar2(255);
v_cnt number;
begin
for c in
(select table_name, column_name
 from user_tab_columns
 where table_name = 'YOURTABLE'
 order by 1, 2)
loop
v_sql := 'select count('||c.column_name||') from '||c.table_name;
execute immediate v_sql into v_cnt;
dbms_output.put_line(c.column_name||'    '||v_cnt);
end loop;
end;
/
Wayne Burr

ASKER
Thanks awking!

I dont think I want to go that route with a procedure if I can help it.  I am doing some testing on different machines and multiple databases. I have to port my query to each sid quickly, test, get results and get out of there ect.

This is what I came up with but worry that it might be much with 30+ columns:
select case when (select count("BIRTHCOUNTRY") from "PS"."S_CA_STU_X" group by "BIRTHCOUNTRY") > 1 then 1 else 0 end as Birthcountry
  from "PS"."S_CA_STU_X"
  group by Birthcountry;
Wayne Burr

ASKER
So my other attempt did not work like I was hoping to either when I had values in the columns that were not null.   There was an error as I'm sure most of you would see why, so I changed it to this:  (But now I get multiple rows)
select case when (select count(akafirst) from "PS"."S_CA_STU_X" where akafirst is not null) > 1 then 1 else 0 end as AKAFirst,
  case when (select count(AKAMIDDLE) from "PS"."S_CA_STU_X" where akafirst is not null) > 1 then 1 else 0 end as AKAMIDDLE
  from "PS"."S_CA_STU_X"
  group by AKAFirst, AKAMIDDLE;

Data looks like this:
akafirst         akamiddle
1                     1
1                      1
1                      1
...
--for 300+ rows

I only want 1 row per column list to count the number of non nulls
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
johnsone

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Wayne Burr

ASKER
Thank you everybody!

Johnsone, Thank you  as this is exactly what I was looking for!

Laid it out and this is what it looks like:
select
sum(case when AKAFirst is Not null then 1 else 0 end) as AKAFirst,
 sum(case when AKAMIDDLE is Not null then 1 else 0 end) as AKAMIDDLE,
 sum(case when AKASURNAME is Not null then 1 else 0 end) as AKASURNAME,
 sum(case when BIRTHCOUNTRY is Not null then 1 else 0 end) as BIRTHCOUNTRY
 from "PS"."S_CA_STU_X" ;
awking00

The problem with trying to use a slelect is that your results will be horizontal like -
col1     col2      col3    .... col30+      
value   value   value   .... value
You might try using the procedure to load a table rather than printing out to the console.
Create table newtable(colname varchar2(30), notnulcount number);
Just change this
dbms_output.put_line(c.column_name||'    '||v_cnt);
to this
insert into newtable values(c.column_name,v_cnt);
Then you can query the table -
select * from newtable.
johnsone

While I think the procedure way would work well and would get the results, I see two things.

A good thing, it is dynamic and will get the column names and figure everything out.

A bad thing, it passes the table once for each column.

If you wanted to go that route, I would have the procedure build a SQL statement for the entire table and then execute it.  Getting all the counts in one pass of the table.

The single SQL statement requires quite a bit of editing to make if you need multiple tables, but is a quick way to pass the table only once to get the results.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.