Analysing null / not null data - 01-31-2006 , 04:05 AM
I want to analyse a 9i table in a way that will provide me with summary
information about the contents of each column, like this:
col_1 : this column contains a null in 1000 rows
col_2 : this column contains a null in 500 rows
col_3 : this column contains a null in 0 rows
That's all I need - either the total number of rows where the column is
null, or the total number of rows where it is not null. I'm hoping
there's a portable method that will work for any table, without me
having to name each table / column individually. Does anyone know of a
way to achieve it?
Re: Analysing null / not null data - 01-31-2006 , 10:49 AM
tarbster (AT) yahoo (DOT) com wrote:
native dynamic SQL.
Daniel A. Morgan
(replace x with u to respond)
Re: Analysing null / not null data - 01-31-2006 , 07:21 PM
The dba_tab_columns dictionary view contains an estimated count of
nulls in each column based on the optimizer statistics gathering
process. The view also identifies how many unique values exist for
each column, if is is nullable, and a few other interesting facts.
Depending on why you want to create your summary this view may be
Otherwise as Daniel said select count(*) from x where col1 is null.
You will only need dynamic SQL if you are trying to write a generic
routine that you can use on any of your tables.
HTH -- Mark D Powell --