Home » SQL & PL/SQL » SQL & PL/SQL » Help in SQL Query (Merged)
Help in SQL Query (Merged) [message #401743] Wed, 06 May 2009 05:49 Go to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

HI
Is there any way to find out which column contains NULL data.Column names are unknown.My table contains 100 columns of which manu columns are not in use.Then how to find out this?
Help in SQL query [message #401744 is a reply to message #401743] Wed, 06 May 2009 05:56 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi,
Is there any way to find out the column name containg NULL values.Column names are unknown

Re: Help in SQL query [message #401746 is a reply to message #401744] Wed, 06 May 2009 06:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Some explanation is required as to why you need to look through tables who's columns you can't find the names of to look for null values.

This feels like homework to me....
Re: Help in SQL Query (Merged) [message #401751 is a reply to message #401743] Wed, 06 May 2009 06:13 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Have you tried anything so far or did you just get the homework assignment and decide to post here immediately to try to get others to do your work for you?
Oh, and by the way, since this is a PL/SQL and SQL forum, it is a pretty safe bet that most threads will require help on either SQL or PL/SQL, rendering your thread topic pretty pointless. Please try to follow the guidelines (that you have been pointed to many, many times) and use a more descriptive title in future. And stop double posting.
Re: Help in SQL query [message #401771 is a reply to message #401744] Wed, 06 May 2009 07:13 Go to previous messageGo to next message
danish_fsd@yahoo.com
Messages: 38
Registered: February 2008
Location: Pakistan
Member
Hope following will help you.

DECLARE
     v_count NUMBER;
     v_count1 NUMBER;
     v_stmt VARCHAR2(2000);
     v_stmt1 VARCHAR2(2000);
BEGIN
     FOR i IN (SELECT DISTINCT Table_Name FROM User_Tab_Columns) LOOP
         v_stmt := 'SELECT COUNT(*) FROM '||i.Table_Name ;
         EXECUTE IMMEDIATE v_stmt INTO v_count;
         FOR j IN (SELECT Column_Name FROM User_Tab_Columns WHERE Table_Name = i.Table_Name) LOOP
             v_stmt1 := 'SELECT COUNT(*) FROM '||i.Table_Name||' WHERE '||j.column_name||' IS NULL';
             EXECUTE IMMEDIATE v_stmt1 INTO v_count1;
     
             IF NVL(v_count, 0) = NVL(v_count1, 0) THEN
                DBMS_OUTPUT.PUT_LINE(i.Table_Name ||'   '||j.Column_Name);
             END IF;
     
         END LOOP;
     END LOOP;     
END;


Regards.
Danish
Re: Help in SQL query [message #401781 is a reply to message #401771] Wed, 06 May 2009 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> DECLARE
  2       v_count NUMBER;
  3       v_count1 NUMBER;
  4       v_stmt VARCHAR2(2000);
  5       v_stmt1 VARCHAR2(2000);
  6  BEGIN
  7       FOR i IN (SELECT DISTINCT Table_Name FROM User_Tab_Columns) LOOP
  8           v_stmt := 'SELECT COUNT(*) FROM '||i.Table_Name ;
  9           EXECUTE IMMEDIATE v_stmt INTO v_count;
 10           FOR j IN (SELECT Column_Name FROM User_Tab_Columns WHERE Table_Name = i.Table_Name) LOOP
 11               v_stmt1 := 'SELECT COUNT(*) FROM '||i.Table_Name||' WHERE '||j.column_name||' IS NULL';
 12               EXECUTE IMMEDIATE v_stmt1 INTO v_count1;
 13       
 14               IF NVL(v_count, 0) = NVL(v_count1, 0) THEN
 15                  DBMS_OUTPUT.PUT_LINE(i.Table_Name ||'   '||j.Column_Name);
 16               END IF;
 17       
 18           END LOOP;
 19       END LOOP;     
 20  END;
 21  /
T1_ERR   ORA_ERR_TAG$
T2   COL
VALIDATE_STATS_RESULTS   INDEX_NAME
DECLARE
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at line 9


Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel

[Updated on: Wed, 06 May 2009 07:25]

Report message to a moderator

Re: Help in SQL query [message #401927 is a reply to message #401781] Thu, 07 May 2009 01:16 Go to previous messageGo to next message
danish_fsd@yahoo.com
Messages: 38
Registered: February 2008
Location: Pakistan
Member
Hi Michel,

Procedure is running fine without error (I have Oracle 10.2.0.1).
Sorry for put the solution. I will care it in future.

Regards.
Danish
Re: Help in SQL query [message #401934 is a reply to message #401927] Thu, 07 May 2009 01:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Instead of counting all the rows in all your tables, and then again counting all the nulls for all the columns in all your tables, you'd better check for one single non-null value. Use rownum as stopkey.
I bet it will be way faster.
Re: Help in SQL query [message #401944 is a reply to message #401927] Thu, 07 May 2009 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
danish_fsd@yahoo.com wrote on Thu, 07 May 2009 08:16
Procedure is running fine without error (I have Oracle 10.2.0.1).

Modifying your code to embed FRank's comment:
SQL> DECLARE
  2    v_count1 NUMBER;
  3    v_stmt1 VARCHAR2(2000);
  4  BEGIN
  5    FOR i IN (SELECT DISTINCT Table_Name FROM User_Tab_Columns) LOOP
  6      FOR j IN (SELECT Column_Name FROM User_Tab_Columns WHERE Table_Name = i.Table_Name) LOOP
  7        v_stmt1 := 
  8          'SELECT COUNT(*) FROM '||i.Table_Name||
  9          ' WHERE '||j.column_name||' IS NULL AND ROWNUM = 1';
 10        EXECUTE IMMEDIATE v_stmt1 INTO v_count1;
 11        IF v_count1 = 0 THEN
 12          DBMS_OUTPUT.PUT_LINE(rpad(i.Table_Name,30,'.')||' '||j.Column_Name);
 13        END IF;
 14      END LOOP;
 15    END LOOP;     
 16  END;
 17  /
T1_ERR........................ ORA_ERR_NUMBER$
T1_ERR........................ ORA_ERR_MESG$
T1_ERR........................ ORA_ERR_ROWID$
T1_ERR........................ ORA_ERR_OPTYP$
T1_ERR........................ ID
T1_ERR........................ VAL
T2............................ COL
VALIDATE_STATS_RESULTS........ TEST
VALIDATE_STATS_RESULTS........ TIMESTAMP
VALIDATE_STATS_RESULTS........ RESULT
EMP........................... HIREDATE
EMP........................... SAL
EMP........................... DEPTNO
EMP........................... EMPNO
EMP........................... ENAME
EMP........................... JOB
DECLARE
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at line 10

Now it should be nice to know why there is this error from time to time.
So I modify the code to remove normal display and add one to display the current query when the error comes:
SQL> DECLARE
  2    v_count1 NUMBER;
  3    v_stmt1 VARCHAR2(2000);
  4    x_sql exception ;
  5    pragma exception_init(x_sql, -933);
  6  BEGIN
  7    FOR i IN (SELECT DISTINCT Table_Name FROM User_Tab_Columns) LOOP
  8      FOR j IN (SELECT Column_Name FROM User_Tab_Columns WHERE Table_Name = i.Table_Name) LOOP
  9        v_stmt1 := 
 10          'SELECT COUNT(*) FROM '||i.Table_Name||
 11          ' WHERE '||j.column_name||' IS NULL AND ROWNUM = 1';
 12        EXECUTE IMMEDIATE v_stmt1 INTO v_count1;
 13  --      IF v_count1 = 0 THEN
 14  --        DBMS_OUTPUT.PUT_LINE(rpad(i.Table_Name,30,'.')||' '||j.Column_Name);
 15  --      END IF;
 16      END LOOP;
 17    END LOOP;     
 18  EXCEPTION WHEN x_sql THEN dbms_output.put_line(v_stmt1); RAISE;
 19  END;
 20  /
SELECT COUNT(*) FROM BIN$dZy54p+ZRXu3c0vTces42g==$0 WHERE TEST IS NULL AND ROWNUM = 1
DECLARE
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at line 18

Good, error comes from table in recyclebin, so remove them from the select and order the tables by name.
Also why querying user_tab_columns with distinct instead of user_tables?
SQL> DECLARE
  2    v_count1 NUMBER;
  3    v_stmt1 VARCHAR2(2000);
  4    x_sql exception ;
  5    pragma exception_init(x_sql, -933);
  6  BEGIN
  7    FOR i IN (
  8      SELECT Table_Name FROM User_Tables
  9      minus
 10      SELECT object_name FROM recyclebin WHERE type = 'TABLE'
 11      ORDER BY 1
 12    ) LOOP
 13      FOR j IN (
 14        SELECT Column_Name FROM User_Tab_Columns 
 15        WHERE Table_Name = i.Table_Name
 16        ORDER BY column_id
 17      ) LOOP
 18        v_stmt1 := 
 19          'SELECT COUNT(*) FROM '||i.Table_Name||
 20          ' WHERE '||j.column_name||' IS NULL AND ROWNUM = 1';
 21        EXECUTE IMMEDIATE v_stmt1 INTO v_count1;
 22        IF v_count1 = 0 THEN
 23          DBMS_OUTPUT.PUT_LINE(rpad(i.Table_Name,30,'.')||' '||j.Column_Name);
 24        END IF;
 25      END LOOP;
 26    END LOOP;     
 27  EXCEPTION WHEN x_sql THEN dbms_output.put_line(v_stmt1);
 28  END;
 29  /
EMP........................... EMPNO
EMP........................... ENAME
EMP........................... JOB
EMP........................... HIREDATE
EMP........................... SAL
EMP........................... DEPTNO
T............................. COL
T1............................ ID
T1............................ VAL
T1_ERR........................ ORA_ERR_NUMBER$
T1_ERR........................ ORA_ERR_MESG$
T1_ERR........................ ORA_ERR_ROWID$
T1_ERR........................ ORA_ERR_OPTYP$
T1_ERR........................ ID
T1_ERR........................ VAL
T2............................ COL
VALIDATE_STATS_RESULTS........ TEST
VALIDATE_STATS_RESULTS........ TIMESTAMP
VALIDATE_STATS_RESULTS........ RESULT

PL/SQL procedure successfully completed.

OK, now you can enhance the code to not repeat the table name, display the count of tables or columns and so on.
I also let you modify the code to handle the case where table or column name contains special or mixed case characters or reserved word (all these should not exist).

Regards
Michel


[Updated on: Thu, 07 May 2009 02:13]

Report message to a moderator

Re: Help in SQL query [message #401953 is a reply to message #401944] Thu, 07 May 2009 02:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think you did not get what I meant (and what I think the OP meant)
I think he wants to find columns that are not used, hence ALL values are null.
Your example is counting columns that DO have values.
Even though this might be only an example, it can be confusing to the OP

If you check for non-null values, it would be nice to check whether there are records at all in the table. Otherwise, empty tables will be reported having non-null columns Smile

Re: Help in SQL query [message #401964 is a reply to message #401953] Thu, 07 May 2009 02:54 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you thought right. Smile I thought OP searches for columns that contain some NULL values not that ALL values are NULL.
But my main goal was to do not let with an error pending and show how we can debug a code.

I let OP (or anyone else) fix my code to fit the requirements, it has no special difficulty (as one of my math teacher used to say us).

Regards
Michel

[Updated on: Thu, 07 May 2009 03:26]

Report message to a moderator

Previous Topic: package going invalid, without modifying dependency
Next Topic: 2d position array/varray in plsql
Goto Forum:
  


Current Time: Mon Dec 05 21:35:04 CST 2016

Total time taken to generate the page: 0.06060 seconds