on 2016 Apr 12 6:27 AM
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 *?
Request clarification before answering.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
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.
User | Count |
---|---|
50 | |
10 | |
8 | |
6 | |
5 | |
5 | |
5 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.