Oracle: finding columns with null values

Question

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

Some columns always appear empty for a particular type.

I want to create a view for each type and show only the corresponding columns for each type. Working under the assumption that if a column has ONLY zero values ​​for a particular type, then these columns should not be part of the view, how can you find this with queries?

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

I know that I FULLY did everything above ... I'm just trying to understand this idea. 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 nonzero value (at least in Oracle).

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 only zeros.

Then this idea can be used to create the desired look.

The table now also requires * 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; 

What, 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 

Answer 3

After looking at the comments of @Gerrat and @BQ, I could get the necessary details as follows: I have an obsolete table that has N different types. All types share columns and have exclusive columns.

I can create a view for each type with all columns, and then use all_tab_columns to get all column names where "num_nulls" is less than the total number of rows for that particular type.

From there, it should be easy to assemble the columns that are used for each type and create views.

Thoughts?

Answer 4

I think you can solve this using metaprogramming. Use the cursor to scroll through each type and column and use "nonexistent" to check if the column is empty. For example:

 CREATE TABLE result_table (type VARCHAR(50), column VARCHAR(50)) CURSOR c IS SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME = &table_name; CURSOR ct IS SELECT DISTINCT type_name FROM &table_name; BEGIN FOR t in ct LOOP FOR r in c LOOP --If you're confused about how this works, replace 'EXECUTE IMMEDIATE' --with print or something and look at the output EXECUTE IMMEDIATE 'INSERT INTO result_table SELECT ''' || t.type_name || ''', ''' || r.COLUMN_NAME || ''' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ' || &table_name || ' WHERE t.type_name = ''' || t.type_name || ''' AND ' || r.COLUMN_NAME || ' IS NOT NULL);'; END LOOP END LOOP SELECT * FROM result_table 

Sorry if there is a syntax error somewhere, I have nothing to check for.

Answer 5

You can determine using the following query:

 select * from (select ascii(t.col2)+ascii(t.col4)+ascii(t.col1)+ascii(t.col3) col from test_null_col t) where col is null; 

And you want to remove the empty rows of the column here is the query:

 delete from (select ascii(t.col2)+ascii(t.col4)+ascii(t.col1)+ascii(t.col3) col from test_null_col t) where col is null; 

Answer 6

Something like that?

 SELECT column1, column2, column3 -- and so on FROM tableA WHERE columnX IS NULL AND columnY IS NULL AND columnZ IS NULL; 

Obviously, you can use this in your CREATE VIEW... statement if you want.

Answer 7

SELECT tablecolumn, tablecolumn2, ... FROM TABLENAME Column WHERE NOT NULL