Oracle: Finden von Spalten mit nur Nullwerten

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

gibt an, wie viele Datensätze pro Spalte eine nicht-null-Wert (zumindest in Oracle, das ist.)

Beispielsweise

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;

gibt

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

darauf hinweist, dass col_3 besteht aus Nullen nur.

Diese Idee kann dann verwendet werden zum erstellen der gewünschten Ansicht.

Den Tisch muss nun auch die *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;

Welche, wenn Sie ausgewählt ist zurück:

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

InformationsquelleAutor René Nyffenegger

Ich denke, Sie können dies lösen, mit metaprogramming. Verwenden Sie einen cursor, um eine Schleife durch jedes Typ und Spalte, und verwenden Sie dann 'nicht vorhanden' zu überprüfen, ob die Spalte leer ist. Zum Beispiel:

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

Entschuldigt, wenn es ein Fehler in der syntax irgendwo, ich habe nichts, um dies zu überprüfen.

InformationsquelleAutor dataduck

Sowas?

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

Offensichtlich, die Sie verwenden können, in einem CREATE VIEW... - Anweisung, wenn Sie wie auch.

InformationsquelleAutor BQ.