1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

pattern matching for multiple columns in where clause

Discussion in 'SQL PL/SQL' started by orafan, Mar 16, 2009.

  1. orafan

    orafan Active Member

    Messages:
    23
    Likes Received:
    3
    Trophy Points:
    90
    Hi guys, maybe this is not possible but just thought it worthwhile to ask :)

    So the thing is I just created a temporary table to upload some data in our application. Now I have 85 columns in the table! phew! Now i have to run sme basic data consistency checks on the data in the table. The problem is since the number of columns is huge i have to continuously keep writing tings like

    Code (Text):

    -----
    -----
    where colA is not null
    and colB is not null
    and colC is not null
    and
    ----
    ----
    and colZZ is not null
     
    phew :(

    SO is there any way for pattern matching in SQL query in the where clause something like
    Code (Text):

    ----
    ----
    where col[A-Z] is not null
    ----
     
    Now i have more or less reached a conclusion that it's not possible. Maybe someone can give a brilliant hint! :)

    Thanks again
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    hmhmhm.. It is not Possible by straight SQL.

    Need to use Dynamic query in PL/SQL as follows.

    Code (Text):

    SQL> DECLARE
      2  Null_count NUMBER(5);
      3  sqlquery   VARCHAR(4000);
      4  BEGIN
      5  sqlquery := 'SELECT COUNT(*) FROM EMP WHERE';
      6      for fld in ( Select COLUMN_NAME
      7                   from   user_tab_columns
      8                   where  TABLE_NAME ='EMP')
      9      loop
     10          sqlquery := sqlquery ||CHR(10)||' '||fld.COLUMN_NAME ||' IS NULL OR';
     11      end loop ;
     12          sqlquery := SUBSTR(sqlquery,1,LENGTH(sqlquery)-2);
     13  dbms_output.put_line(sqlquery) ;
     14     execute immediate  sqlquery into Null_count ;
     15  dbms_output.put_line('COUNT OF RECORDS WHERE THERE IS ATLEAST ONE NULL : '||Null_count) ;
     16  end ;
     17  /
    SELECT COUNT(*) FROM EMP WHERE
     EMPNO IS NULL OR
     ENAME IS NULL OR
     JOB IS NULL OR
     MGR IS NULL OR
     HIREDATE IS NULL OR
     SAL IS NULL OR
     COMM IS NULL OR
     DEPTNO IS NULL
    COUNT OF RECORDS WHERE THERE IS ATLEAST ONE NULL : 10

    PL/SQL procedure successfully completed.

    SQL>
     
     
  3. orafan

    orafan Active Member

    Messages:
    23
    Likes Received:
    3
    Trophy Points:
    90
    hmm hey of course i can loop through user_tab_columns... good good... i didn't think in that direction. I was actually trying to avoid writing a procedure but thanks for the code brother!

    ok i will modify your procedure for my tables... thanks again
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You can't do it in straight SQL As I said and you can't avoid writing a procedure or anonymous block as explained.

    If you wan to go for Procedure , you can make it more flexible by making table name and condition as Input parameter. Play around it :)