2 SQL questions

From: Jason McKee <jmckee_at_ix.netcom.com>
Date: Fri, 26 Jun 1998 17:57:30 -0500
Message-ID: <3594275A.1B9D33DB@ix.netcom.com>

My 2 questions are related to a query I'm trying to write. I want to be able to count the number of NULL values in each column of a table specified on the command line.
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:

Column Name Total

Column1                        20
Column2                        33
Column3                         9
ColumnN                        X

Is there a simple way to do this?

Question #2: In an attempt to solve Question #1, I wrote some PL/SQL statements

                          That fetch the column names and store them in
a table. That went well.
                         Next, I used a cursor to select each row of the
given table and place in a
                         record and had in  mind being able to reference
each column in the
                        record indirectly as follows:

                             next_row := 1;
                             IF  temp_rec.col_table(next_row) IS NULL

                        However, PL/SQL does not like my attempt to
reference a
                       column name that I have stored in the above
mentioned table.
                       Is there a way to get the column name out of the
table and use it as
                       a reference to the column in the record?
                        I cannot explicitly supply the column name as
this query needs to be
                        non table specific. Also, the list of column

names could be quite long.

Any suggestions are greatly apreciated. Many thanks in advance.

Jason Received on Fri Jun 26 1998 - 17:57:30 CDT