Query table for null columns when column name like

Question

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

@a_horse_with_no_name He is probably using the Oracle e-Business Suite. Almost every table in that database has at least 15 ATTRIBUTE* columns. They're called "descriptive flexfields" and are actually very sensible and useful, especially for a database design that is 25+ years old. — Matthew McPeak
– Matthew McPeak, Commented Oct 7, 2019 at 14:22

Matthew McPeak · Accepted Answer · 2019-10-07 14:37:05Z

1

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;

edited Oct 7, 2019 at 14:37

answered Oct 7, 2019 at 14:21

Matthew McPeak

18.1k3 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mad_philly Over a year ago

I know there are 15 attribute columns, I just wondered if there was a way to wildcard the attribute column rather than write it out 15 times. It's lazy I know, but if I have to do this discovery on multiple tables I'd like to make it as simple as possible.

Matthew McPeak Over a year ago

There is no simple syntax for that. You can use dynamic SQL to write the queries and execute them. Depending on how well you know how to use dynamic SQL and how many tables you are analyzing, that may or may not be less work.

Popeye · Accepted Answer · 2019-10-07 14:23:28Z

0

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!!

answered Oct 7, 2019 at 14:23

Popeye

36k4 gold badges12 silver badges31 bronze badges

1 Comment

mad_philly Over a year ago

It's more than likely that all the columns will be null, so yes it would be great to list any of them that have a value. To avoid listing all 15 columns is my main goal here

marc_s · Accepted Answer · 2019-11-13 21:42:17Z

0

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

analyze the table without sampling (= full)

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

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.

edited Nov 13, 2019 at 21:42

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Oct 7, 2019 at 18:48

Marmite Bomber

21.3k4 gold badges32 silver badges59 bronze badges

Collectives™ on Stack Overflow

Query table for null columns when column name like

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related