Oracle: Finding Columns with only null values

Question

I have a table with a lot of columns and a type column.

Some columns seem to be always empty for a specific type.

I want to create a view for each type and only show the relevant columns for each type. Working under the assumption that if a column has ONLY null values for a specific type, then that columns should not be part of the view, how can you find that out with queries?

Is there a SELECT [columnName] FROM [table] WHERE [columnValues] ARE ALL [null]

I know I COMPLETELY made it all up above... I'm just trying to get the idea across. Thanks in advance!

Answer 1

SELECT  t.column_name
FROM    user_tab_columns t
WHERE   t.nullable = 'Y'
        AND t.table_name = 'YOUR_TABLE_NAME'
        AND t.num_distinct = 0

Answer 2

select
  count(col_1),
  count(col_2),
  count(col_3)
from
  <table>

returns how many records per column have a non-null value (at least in Oracle, that is.)

For example

drop table tq84_count_nulls;

create table tq84_count_nulls (
  col_1  varchar(50),
  col_2  number,
  col_3  date
);

insert into tq84_count_nulls values (null, null, null);
insert into tq84_count_nulls values ('xx', null, null);
insert into tq84_count_nulls values (null,   42, null);
insert into tq84_count_nulls values ('yy',   12, null);

select
  count(col_1),
  count(col_2),
  count(col_3)
from
  tq84_count_nulls;

returns

COUNT(COL_1) COUNT(COL_2) COUNT(COL_3)
------------ ------------ ------------
           2            2            0

indicating that col_3 consists of nulls only.

This idea can then be used to create the desired view.

The table now needs also the *group_id*:

drop table tq84_count_nulls;
create table tq84_count_nulls (
  col_1    varchar(50),
  col_2    number,
  col_3    date,
  group_id varchar(2)
);

insert into tq84_count_nulls values (null, null, null, 'a');
insert into tq84_count_nulls values ('xx', null, null, 'a');
insert into tq84_count_nulls values (null,   42, null, 'a');
insert into tq84_count_nulls values ('yy',   12, null, 'a');

insert into tq84_count_nulls values (null, null, null, 'b');
insert into tq84_count_nulls values (null, null, null, 'b');
insert into tq84_count_nulls values (null,   42, null, 'b');
insert into tq84_count_nulls values (null,   12, null, 'b');




create or replace view nulls_per_type as 
with n as (
  select
    count(col_1) col_1_count,
    count(col_2) col_2_count,
    count(col_3) col_3_count,
    group_id
  from
    tq84_count_nulls
  group by 
    group_id
),
o as (
select case col_1_count when 0 then 'COL_1 is always 0 for ' || group_id else null end u from n union all
select case col_2_count when 0 then 'COL_2 is always 0 for ' || group_id else null end u from n union all
select case col_3_count when 0 then 'COL_3 is always 0 for ' || group_id else null end u from n
)
select * from o where u is not null;

Which, when selected returns:

select * from nulls_per_type;

COL_1 is always 0 for b
COL_3 is always 0 for a
COL_3 is always 0 for b