Reply from Pendleton on Apr 24 at 8:24 PM Hi Winkey,
Isn't SQL fun? (That is a sarcastic remark. SQL is normally pronounced 'SEQUEL', but could also be pronounced 'SQUEAL'.)
I do not have access to your example tables, so I cannot do the work you requested.
However, Steve's query can be used to your advantage. Steve is STRONG in SQL, and a real boon to those of us who struggle with SQL. He, and many others like http://asktom.oracle.com/pls/apex/f?p=100:1:0
recommend using SQL without PL/SQL whenever possible. This is good advice. If you cannot get the output format you need with SQL, then I recommend you always first work to get SQL producing the required output before using PL/SQL.
You might want to change Steve's query to get sequential rows using UNION, see below
SELECT
'sum(decode(' ||
COLUMN_NAME
|| ', NULL, 1, 0)) ' ||
COLUMN_NAME
|| '_NULLS UNION '
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'SCHEMA_1' AND TABLE_NAME = 'TABLE_1'
ORDER BY COLUMN_NAME;
Copy the output, add SELECT as a prefix, FROM your_tablename; as a suffix, and remove UNION from the last row of the query. If you need NOT NULL counts instead of NULL counts, reverse the 1 and 0 in the decode statement.
Please be courteous, once you have results, to mark Steve's answers as helpful, or as a solution.
If you do need to automate in PL/SQL, please work with the above until you get correct output. PL/SQL syntax is not always what one might expect.
Best Regards,
Bill
| | | ---------------Original Message---------------
From: winkeytech
Sent: Tuesday, April 24, 2012 5:26 PM
Subject: Get Counts of All the Columns Having Not Null Values
Hi Bill,
Thanks a lot for you response. Do you mind providing an example with some sample tables we get with oracle. I am little slow in understanding things and I could not get from that.
Coming to my script I got stuck in storing the count of null or not nulls. i.e below query
SELECT COUNT(*) INTO NOT_NULL_COUNT FROM EDXF.SWAP_DRVTV_RLTD_LEG_DTL_V09
WHERE TRIM(column_list) IS NOT NULL;
I am getting the same count for all the columns, which is not correct. Is there any other way that can come up to store respective counts of each column, so that instead of using INTO statement . I am also tried using arrays , but the results are same.
Please let me know if need more information or In case I am missing something.
Cheers
-Winkey |
| Reply to this email to post your response. __.____._ |   _.____.__ |
No comments:
Post a Comment