CODEWITHSUNDEEP
oracle-databaseplsql
mad_philly
mad_philly

Reputation: 13

Query table for null columns when column name like

I have an Oracle table which contains columns labelled attribute1 to attribute15. I need to list any of those columns that contain any values.

I need to do something like;

select ... 
from table 
where column_name like 'attribute%' 
  and column is not null

Can't find anything resembling this

Upvotes: 0

Views: 116

Answers (3)

Marmite Bomber
Marmite Bomber

Reputation: 21075

You may look at the table statistics to get this information.

  1. analyze the table without sampling (= full)

    exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'TAB',estimate_percent=>100);

  2. query the NUM_DISTINCT column in user_tab_columns 

    select COLUMN_NAME, NUM_DISTINCT, NUM_NULLS
from user_tab_columns 
where table_name = 'TAB';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS
------------------------------ ------------ ----------
ATT1                                      0          9
ATT2                                      3          3
ATT3                                      9          0

Note that NUM_DISTINCT = 0 means that all values are NULL.

On the contrary NUM_NULLS = 0 identifies not null columns.

Upvotes: 0

Matthew McPeak
Matthew McPeak

Reputation: 17934

Why get fancy?

Describe your table to find out how many ATTRIBUTE* columns there are and then write this:

SELECT *
FROM   table
WHERE  ATTRIBUTE1 IS NOT NULL
OR     ATTRIBUTE2 IS NOT NULL
OR     ATTRIBUTE3 IS NOT NULL
...

Or, if you need to know which columns in particular have values,

SELECT DECODE(COUNT(ATTRIBUTE1),0,'N','Y') ATTRIBUTE1_HAS_VALUES,
       DECODE(COUNT(ATTRIBUTE2),0,'N','Y') ATTRIBUTE2_HAS_VALUES,
       ...
FROM   TABLE;

Upvotes: 1

Popeye
Popeye

Reputation: 35910

I think you have null values in most of the columns and need any non null values.

If aforementioned is correct understanding then you can use coalesce function as following.

Select coalesce(attribute1, attribute2, .... , attribute15) as val 
-- or use select * , if you want all columns value
From your_table
Where coalesce(attribute1, attribute2, .... , attribute15) is not null;

Cheers!!

Upvotes: 0

Related Questions