If I select * from a table which for example returns 10 fields with 100 rows, how can I exclude all fields that return null on all 100 rows with select *?

asked 12 Apr '16, 05:27

Rolle's gravatar image

Rolle
379213142
accept rate: 0%


Here's an answer attempt for the real question, based on Marks's suggestion.

First, generate some test data, i.e. a table T_Test with 5 columns of which Col1 is NOT NULL by design, Col2-Col4 may have NULLs, and Col5 is always NULL. You may filter the table to include more or less all-NULL columns, say by using "where Col4 is null".

with
   S1 as (select row_num as Nr, case when mod(row_num, 4) = 0 then null else row_num end as NrOrNull from sa_rowgenerator(1,100)),
   S2 as (select row_num as Nr, case when mod(row_num, 3) = 0 then null else row_num end as NrOrNull from sa_rowgenerator(1,100)),
   S3 as (select row_num as Nr, case when mod(row_num, 2) = 0 then null else row_num end as NrOrNull from sa_rowgenerator(1,100))
select S1.Nr as Col1, S1.NrOrNull as Col2, S2.NrOrNull as Col3, S3.NrOrNull as Col4, null as Col5
into T_Test
from S1 inner join S2 on S1.Nr = S2.Nr inner join S3 on S1.Nr = S3.Nr
order by 1, 2, 3, 4, 5

Then use a FOR statement based on the system catalog to enumerate all column names, and then use a count query for each to count the NOT NULL values. Note, if your original query uses a WHERE condition, the count query needs to do so, too.

begin
   declare strQuery long varchar;
   declare strCntQuery long varchar;
   declare nCnt int;
   for forCrs as crs cursor for
      select SC.column_name
      from systabcol SC key join systab ST where table_name = 'T_Test' order by column_id
   do
      -- build the "count query"
      set strCntQuery = 'select count(' || column_name || ') into nCnt from T_Test';
      execute immediate strCntQuery;
      message 'Column/Count : ' || column_name ||  '/' || nCnt to client;
      if nCnt > 0 then 
         set strQuery = strQuery || ', ' || column_name;
      end if;
   end for;
   -- finally complete the query and use it with EXECUTE IMMEDIATE
   if strQuery is not null then
      set strQuery = substr(strQuery, 3); -- drop the initial comma
      set strQuery = 'select ' || strQuery || ' from T_Test order by 1';
      message 'Null-free statement: ' || strQuery to client;
      execute immediate with result set on strQuery;
   end if;
end;

The sample will return a result set that omits Col5.

Variation: If you add a WHERE clause within the two queries on T_Test, say by replacing "from T_Test" with "from T_Test where Col2 Is null", the result set will omit Col2 and Col4, as well.


Apparently, that could easily be enhanced to

  • use an arbitrary dynamic query with joins/filters instead of the hard-coded sample table
  • use sa_describe_query() instead of the system catalog to get the column names
permanent link

answered 12 Apr '16, 10:18

Volker%20Barth's gravatar image

Volker Barth
30.3k301454660
accept rate: 32%

edited 12 Apr '16, 10:23

Well, one obvious way would be to add according filters in the WHERE clause for all columns, i.e. that at least one column must be NOT NULL, such as

...
WHERE col1 is not null or col2 is not null or col4 is not null or ... or col10 is not null

However, a possibly easier way would be to use the EXCEPT set operator - note that EXCEPT treats NULL as equal, contrary to normal comparisons.

Here's a sample to generate some data with null values - using three local views (S1 to S3) which return a number (col Nr) from 1 to 100 and each forth/third/second element as NULL (col NrOrNull), then joining them on Nr:

with
   S1 as (select row_num as Nr, case when mod(row_num, 4) = 0 then null else row_num end as NrOrNull from sa_rowgenerator(1,100)),
   S2 as (select row_num as Nr, case when mod(row_num, 3) = 0 then null else row_num end as NrOrNull from sa_rowgenerator(1,100)),
   S3 as (select row_num as Nr, case when mod(row_num, 2) = 0 then null else row_num end as NrOrNull from sa_rowgenerator(1,100))
select S1.Nr, S1.NrOrNull, S2.NrOrNull, S3.NrOrNull
from S1 inner join S2 on S1.Nr = S2.Nr inner join S3 on S1.Nr = S3.Nr

Apparently, each 12th row will contain all NULLs (except the Nr here):

Nr,NrOrNull,NrOrNull,NrOrNull
1,1,1,1
2,2,2,(NULL)
3,3,(NULL),3
4,(NULL),4,(NULL)
5,5,5,5
6,6,(NULL),(NULL)
7,7,7,7
8,(NULL),8,(NULL)
9,9,(NULL),9
10,10,10,(NULL)
11,11,11,11
12,(NULL),(NULL),(NULL)  <-- all NULLs
13,13,13,13
14,14,14,(NULL)
...
22,22,22,(NULL)
23,23,23,23
24,(NULL),(NULL),(NULL)  <-- all NULLs
25,25,25,25
26,26,26,(NULL)
...

When you omit the Nr column and use except with a SELECT statement that justs returns NULL for each column, these rows are omitted:

with
   S1 as (select row_num as Nr, case when mod(row_num, 4) = 0 then null else row_num end as NrOrNull from sa_rowgenerator(1,100)),
   S2 as (select row_num as Nr, case when mod(row_num, 3) = 0 then null else row_num end as NrOrNull from sa_rowgenerator(1,100)),
   S3 as (select row_num as Nr, case when mod(row_num, 2) = 0 then null else row_num end as NrOrNull from sa_rowgenerator(1,100))
select S1.NrOrNull, S2.NrOrNull, S3.NrOrNull
from S1 inner join S2 on S1.Nr = S2.Nr inner join S3 on S1.Nr = S3.Nr
except
select null, null, null

In that simple sample, it might as well do to use a WHERE clause such as

where S1.NrOrNull is not null or S2.NrOrNull is not null or S3.NrOrNull is not null
permanent link

answered 12 Apr '16, 06:39

Volker%20Barth's gravatar image

Volker Barth
30.3k301454660
accept rate: 32%

edited 12 Apr '16, 06:41

1

Volker: Your answer is for excluding rows that have all NULL columns, but the question asks to excluded columns that have all NULL rows. There is no easy way to do this - one would have to do two passes: first pass would be to determine which columns are null for all rows (something like count number of non-null values in each column - so if there were 10 columns you would compute 10 counts), and then you would have to construct a statement based on the counts, excluding columns that had associated counts equal to zero, and EXECUTE IMMEDIATE the constructed statement.

(12 Apr '16, 07:25) Mark Culp
Replies hidden

That's right.

I wanna exclude the columns there all rows is null, how do I do then? That's what I want.

Ex: I use select * from tbltest which return 10 columns and 100 rows. On two columns all rows is null. So when I use select * from tbltest I wanna exclude the columns where all rows is null, in this case only 8 columns will be returned. This is dynamic, so maybe next time I select * from that table 4 columns have null on all rows and should be excluded. It should only exclude the columns there All rows is null.

(12 Apr '16, 08:07) Rolle
Replies hidden

You're so right - "Read first, then answer", as they say.)

(12 Apr '16, 08:18) Volker Barth

how do I do then?

Just as Mark has explained: You will need to do an auxiliary query first, such as

select count(Col1) as Cnt1, count(Col2) as Cnt2, ..., count(Col10) as Cnt10
from tbltest;

Because you need to build the query with varying number of columns, you will have to use a dynamic query, i.e. to build the query as a string and then use EXECUTE IMMEDIATE to run the query based on the string.

While building the query string, you will add the according column to the SELECT list in case the according CntX is > 0.


Based on your requirements, you may do the counting for all columns in one go (as I my sample query) and then use the result set to build the query, or you could alternatively enumerate the columns one-by-one, count the values for that current column, and if > 0, add it to the SELECT list, and repeat that for the next column.

(12 Apr '16, 08:33) Volker Barth

I think I need more help with EXECUTE IMMEDIATE. Could you show med an example? After I have count all columns, I'm not clear how to exclude all with 0 with EXECUTE IMMEDIATE.

(12 Apr '16, 09:06) Rolle
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×49

question asked: 12 Apr '16, 05:27

question was seen: 417 times

last updated: 12 Apr '16, 10:23