13 Replies Latest reply: Dec 16, 2009 1:17 PM by 732425 RSS

    How to select columns with null values

    649638
      HI

      In my table ‘A’ I have 10 columns and 30,000 records. I need all those columns whose value is null for all the records.

      For example in the below table column 'suffix' is null for all the records. So I want column suffix to be selected.

      Name Suffix Street

      James 1100 Washington street
      Richard 273 GEORGIA ST
      Arnold 3018 OAKHILL AVE
      MICHAEL 834 E 161ST ST
      Joseph 410 PINE AVE


      Thanks in advance
        • 1. Re: How to select columns with null values
          tortureduck
          you can select null values by using IS NULL in oracle

          means: select * from testtab where testcolumn is null

          can be easyily found in http://tahiti.oracle.com/
          • 2. Re: How to select columns with null values
            649638
            thanks..

            But if I do like that I will get all the records. But I need only column name which is null for all the records.
            • 3. Re: How to select columns with null values
              SomeoneElse
              select count(name), count(suffix), count(street)
              from   your_table;
              The results with 0 are all nulls (or the table has no rows).
              • 4. Re: How to select columns with null values
                170207
                On 10g:
                SQL> desc a
                 Name                                      Null?    Type
                 ----------------------------------------- -------- ----------------------------
                 NAME                                               VARCHAR2(7)
                 SUFFIX                                             VARCHAR2(10)
                 STREET                                             VARCHAR2(22)
                 NUM                                                NUMBER
                 DT                                                 DATE
                
                SQL> select * from a;
                
                NAME    SUFFIX     STREET                        NUM DT
                ------- ---------- ---------------------- ---------- ----------
                James              1100 Washington street
                Richard            273 GEORGIA ST
                Arnold             3018 OAKHILL AVE
                MICHAEL            834 E 161ST ST
                Joseph             410 PINE AVE
                
                SQL> select *
                  2  from XMLTable(
                  3    '
                  4      for $NullCol in $ColNames/ROW/*
                  5      where fn:empty($SrcRows/ROW/*[fn:name() eq $NullCol/text()][text() ne ""])
                  6      return $NullCol
                  7    '
                  8    PASSING XMLType(DBMS_XMLQuery.GetXML('SELECT * FROM A')).Extract('/*/ROW') AS "SrcRows",
                  9            XMLType(DBMS_XMLQuery.GetXML('SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ''A''')).Extract('/*/ROW') AS "ColNames"
                 10    COLUMNS Column_Name VARCHAR2(30) PATH 'text()'
                 11  );
                
                COLUMN_NAME
                ------------------------------
                SUFFIX
                NUM
                DT
                Regards,
                Dima
                • 5. Re: How to select columns with null values
                  MichaelS
                  Or a 9i xml version:
                  SELECT table_name, column_name
                    FROM cols
                   WHERE data_type NOT IN ('BLOB', 'LONG', 'CLOB')
                         AND dbms_xmlgen.getxmltype('select count('
                                                    || CASE
                                                          WHEN data_type NOT IN
                                                                     ('BLOB', 'LONG', 'CLOB')
                                                          THEN
                                                             column_name
                                                          ELSE
                                                             '1'
                                                       END
                                                    || ') c from '
                                                    || table_name).EXTRACT (
                               '//text()'
                            ).getnumberval () = 0
                  /
                  (blobs, clobs, longs and other obscure datatypes must be handled seperatly)
                  • 6. Re: How to select columns with null values
                    Satyaki_De
                    Am i missing anything Michael?
                    satyaki>
                    satyaki>SELECT table_name, column_name
                      2    FROM all_tab_cols
                      3   WHERE data_type NOT IN ('BLOB', 'LONG', 'CLOB')
                      4   AND dbms_xmlgen.getxmltype('select count('
                      5                               || CASE
                      6                                    WHEN data_type NOT IN ('BLOB', 'LONG', 'CLOB') THEN
                      7                                          column_name
                      8                                    ELSE
                      9                                      '1'
                     10                                  END
                     11                               || ') c from '
                     12                               || table_name).EXTRACT (
                     13               '//text()'
                     14            ).getnumberval () = 0;
                     AND dbms_xmlgen.getxmltype('select count('
                         *
                    ERROR at line 4:
                    ORA-19202: Error occurred in XML processing
                    ORA-00942: table or view does not exist
                    ORA-06512: at "SYS.DBMS_XMLGEN", line 288
                    ORA-06512: at line 1
                    
                    
                    Elapsed: 00:00:02.08
                    satyaki>
                    Regards.

                    Satyaki De.
                    • 7. Re: How to select columns with null values
                      MichaelS
                      You can only use tables where you have select privileges. Therefore
                      SELECT owner, table_name, column_name
                        FROM all_tab_cols
                       WHERE data_type NOT IN ('BLOB', 'LONG', 'CLOB')
                             AND dbms_xmlgen.getxmltype('select count('
                                                        || CASE
                                                              WHEN data_type NOT IN
                                                                         ('BLOB', 'LONG', 'CLOB')
                                                              THEN
                                                                 column_name
                                                              ELSE
                                                                 '1'
                                                           END
                                                        || ') c from '
                                                        || owner
                                                        || '.'
                                                        || table_name).EXTRACT (
                                   '//text()'
                                ).getnumberval () = 0
                             AND table_name IN
                                      (SELECT table_name
                                         FROM all_tab_privs
                                        WHERE privilege = 'SELECT' AND USER IN (grantor, grantee)) 
                      should do it.
                      • 8. Re: How to select columns with null values
                        Satyaki_De
                        True...

                        But, i think here null needs to handle otherwise it will again throw some error like this ->
                        satyaki>
                        satyaki>select * from v$version;
                        
                        BANNER
                        ----------------------------------------------------------------
                        Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
                        PL/SQL Release 10.2.0.3.0 - Production
                        CORE    10.2.0.3.0      Production
                        TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
                        NLSRTL Version 10.2.0.3.0 - Production
                        
                        Elapsed: 00:00:00.20
                        satyaki>
                        satyaki>
                        satyaki>
                        satyaki>SELECT owner, table_name, column_name
                          2    FROM all_tab_cols
                          3   WHERE data_type NOT IN ('BLOB', 'LONG', 'CLOB')
                          4         AND dbms_xmlgen.getxmltype('select count('
                          5                                    || CASE
                          6                                          WHEN data_type NOT IN
                          7                                                     ('BLOB', 'LONG', 'CLOB')
                          8                                          THEN
                          9                                             column_name
                         10                                          ELSE
                         11                                             '1'
                         12                                       END
                         13                                    || ') c from '
                         14                                    || owner
                         15                                    || '.'
                         16                                    || table_name).EXTRACT (
                         17               '//text()'
                         18            ).getnumberval () = 0
                         19         AND table_name IN
                         20                  (SELECT table_name
                         21                     FROM all_tab_privs
                         22                    WHERE privilege = 'SELECT' AND USER IN (grantor, grantee));
                        ERROR:
                        ORA-19202: Error occurred in XML processing
                        ORA-24347: Warning of a NULL column in an aggregate function
                        ORA-06512: at "SYS.DBMS_XMLGEN", line 288
                        ORA-06512: at line 1
                        
                        
                        
                        no rows selected
                        
                        Elapsed: 00:00:04.03
                        satyaki>
                        Or,
                        satyaki>
                        satyaki>
                        satyaki>SELECT table_name, column_name
                          2    FROM user_tab_cols
                          3   WHERE data_type NOT IN ('BLOB', 'LONG', 'CLOB')
                          4   AND   dbms_xmlgen.getxmltype('select count('
                          5                               || CASE
                          6                                    WHEN data_type NOT IN ('BLOB', 'LONG', 'CLOB') THEN
                          7                                          column_name
                          8                                    ELSE
                          9                                      '1'
                         10                                  END
                         11                               || ') c from '||table_name).EXTRACT('//text()').getnumberval() = 0;
                         AND   dbms_xmlgen.getxmltype('select count('
                               *
                        ERROR at line 4:
                        ORA-19202: Error occurred in XML processing
                        ORA-24347: Warning of a NULL column in an aggregate function
                        ORA-06512: at "SYS.DBMS_XMLGEN", line 288
                        ORA-06512: at line 1
                        
                        
                        Elapsed: 00:00:02.66
                        satyaki>
                        satyaki>
                        Do you have any idea to resolve this issue in this context?

                        Regards.

                        Satyaki De.
                        • 9. Re: How to select columns with null values
                          MichaelS
                          Do you have any idea to resolve this issue in this context?
                          actually no ;)

                          can you try with a simple known table like emp?
                           ... FROM user_tab_cols WHERE table_name = 'EMP'
                          and then maybe the same thing after updating one column to NULL?

                          Need to find out if it is a general thing or related to specific table ....
                          • 10. Re: How to select columns with null values
                            Satyaki_De
                            Well, this happens when one of the column of your table contains null value.

                            You can see the following ->

                            This is working ->
                            satyaki>
                            satyaki>
                            satyaki>select xmltype( cursor( select sum( 1 ) s from dual ) ) from dual;
                            
                            XMLTYPE(CURSOR(SELECTSUM(1)SFROMDUAL))
                            -----------------------------------------------------------------------------
                            <?xml version="1.0"?>
                            <ROWSET>
                             <ROW>
                              <S>1</S>
                             </ROW>
                            </ROWSET>
                            
                            
                            Elapsed: 00:00:04.44
                            satyaki>
                            satyaki>
                            But, the given is not working ->
                            satyaki>
                            satyaki>
                            satyaki>select xmltype( cursor( select sum( null ) s from dual ) ) from dual;
                            ERROR:
                            ORA-19202: Error occurred in XML processing
                            ORA-24347: Warning of a NULL column in an aggregate function
                            ORA-06512: at "SYS.XMLTYPE", line 334
                            ORA-06512: at line 1
                            
                            
                            
                            no rows selected
                            
                            Elapsed: 00:00:00.29
                            satyaki>
                            And, my DB version is ->
                            satyaki>
                            satyaki>select * from v$version;
                            
                            BANNER
                            ----------------------------------------------------------------
                            Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
                            PL/SQL Release 10.2.0.3.0 - Production
                            CORE    10.2.0.3.0      Production
                            TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
                            NLSRTL Version 10.2.0.3.0 - Production
                            
                            Elapsed: 00:00:00.20
                            satyaki>
                            Now, still i'm looking for the solution. I don't have the access to the metalink. So, i don't know whether they have any solution for this problem or not?

                            Regards.

                            Satyaki De.
                            • 11. Re: How to select columns with null values
                              MichaelS
                              Ok, could reproduce on my 9.2.0.8 instance:

                              SQL>  select * from v$version where rownum=1
                              
                              BANNER                                                          
                              ----------------------------------------------------------------
                              Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
                              1 row selected.
                              
                              SQL>  select dbms_xmlgen.getxml('select sum( null ) s from dual') from dual
                              select dbms_xmlgen.getxml('select sum( null ) s from dual') from dual
                              *
                              Error at line 0
                              ORA-06502: PL/SQL: numeric or value error
                              ORA-24347: Warning of a NULL column in an aggregate function
                              but runs well on 11.1.0.7:
                              BANNER                                                                          
                              --------------------------------------------------------------------------------
                              Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production          
                              1 row selected.
                              
                              SQL>  select dbms_xmlgen.getxml('select sum( null ) s from dual') from dual
                              
                              DBMS_XMLGEN.GETXML('SELECTSUM(NULL)SFROMDUAL')                                  
                              --------------------------------------------------------------------------------
                              <?xml version="1.0"?>                                                           
                              <ROWSET>                                                                        
                               <ROW>                                                                          
                               </ROW>                                                                         
                              </ROWSET>                                                                       
                                                                                                              
                                                                                                              
                              1 row selected.
                              So obviously you have column_names named 'NULL' which you also have to exclude from the select:
                              ... FROM user_tab_cols WHERE column_name != 'NULL'
                              • 12. Re: How to select columns with null values
                                Satyaki_De
                                So, Looks like Op has to wait for a while to work this solution until 11g arrives for him/her. ;)

                                Regards.

                                Satyaki De.
                                • 13. Re: How to select columns with null values
                                  732425
                                  This thread helped me with this exact problem:
                                  http://kr.forums.oracle.com/forums/thread.jspa?threadID=856186

                                  Namely this select:
                                  SELECT t.table_name,
                                  t.column_name
                                  FROM user_tab_columns t
                                  WHERE t.nullable = 'Y'
                                  AND t.num_distinct = 0;