Re: 2 SQL questions

On Fri, 26 Jun 1998 17:57:30 -0500, Jason McKee <jmckee_at_ix.netcom.com> wrote:

>I have one solution that generates a SELECT statement which returns 1
>row of totals across the width of the screen like:
>
>column1 column2 column3 ..... column N
>----------------------------------------------------
> 20 33 9 X
>
>but this is not very readable for tables with many columns.
>
>Question #1: I would like to flip the result of the SELECT on its side
>to have:

You might take a look at the ANALYZE command. If you analyze the table, the number of nulls for each column will be placed in the NUM_NULLS column of the USER_TAB_COLUMNS view. Here's an example:

SQL> analyze table employee compute statistics;

Table analyzed.

SQL> select column_name, num_nulls
  2 from user_tab_columns
  3 where table_name ='EMPLOYEE';

COLUMN_NAME                    NUM_NULLS
------------------------------ ---------
EMPLOYEE_ID                            0
EMPLOYEE_NAME                          0
EMPLOYEE_HIRE_DATE                     0
EMPLOYEE_TERMINATION_DATE              5
EMPLOYEE_BILLING_RATE                  0

Updates to the table won't automatically update the NUM_NULLS field, so you have to ANALYZE each time you want to run your report. You also have to consider that the REAL number of nulls could actually change between the time the ANALYZE command is run and the time that the subsequent SELECT is run. That may or may not be critical to you. If it is, then you'll need to find a quiet time, when no one is accessing the database, to run your report.

regards,

Jonathan Received on Fri Jun 26 1998 - 23:06:24 CDT