Forum Stats

  • 3,836,734 Users
  • 2,262,175 Discussions
  • 7,900,088 Comments

Discussions

Hi Everybody Null Column Filter

First of all I want to say My english is poor. I hope I can tell what I want to do

How can I do Column filter is that possible

for example

I have Personel table which have 20 columns and I dont know which columns are emty and full

I want to select only full columns. Is there special keyword or like someting that .

I dont want to use .... where xxx is null or xxx=null like all columns

because I dont know which columns is emty or full

How can I find full columns

select * from Personel where Id=3 and xxxx

result columns are only full columns not empty columns

Thanks in advance

Answers

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,106 Silver Trophy

    If I understand correctly, there is no short-cut for what you are trying to accomplish. If you only want results were all 20 columns have data (i.e. full), then you have to state that in the WHERE clause for each column.

    SELECT *
    FROM Personel
    WHERE id is not null
    and col2 is not null
    and col3 is not null
    ...
    and col19 is not null;
    


    Christyxo
  • Christyxo
    Christyxo Member Posts: 151 Silver Badge
    edited Jul 7, 2021 8:55AM

    As @Jason_(A_Non) said; you have to do the work to build the conditions up as there's no special function that you are looking for. But you could emulate your conditions by either a view or a generated column:

    VIEW:

    Create a view that selects everything from your table with the conditions mentioned above.

    CREATE OR REPLACE VIEW V_PERSONEL AS
    SELECT
      ID
    , COL2
    , COL3
    FROM
      PERSONEL
    WHERE
      ID IS NULL 
    OR COL2 IS NULL 
    OR COL3 IS NULL ;
    

    And then your subsequent select statements will always be

    SELECT * FROM V_PERSONEL
    


    VIRTUAL COLUMN

    You could also create a generated column with those conditions. The expression is stored by Oracle not the values so the database will still be running the conditions in real time, but you can select the rows that you want simply by filtering out the generated column:

    CREATE TABLE PERSONEL
    (
      CL_ID     NUMBER ( 10 , 0 )
    , CL_COL1   VARCHAR2 ( 20 BYTE )
    , CL_COL2   VARCHAR2 ( 20 BYTE )
    , CL_GEN    NUMBER ( 2 , 0 ) GENERATED ALWAYS AS 
    ( 
     CASE 
      WHEN CL_ID IS NULL THEN 0
      WHEN CL_COL1 IS NULL THEN 0
      WHEN CL_COL2 IS NULL THEN 0
      ELSE 1
     END )
    ) ;
    

    To retrieve rows where every column is NOT NULL

    SELECT * FROM PERSONEL WHERE CL_GEN = 1 ;