Re: 2 SQL questions

From: Rohrbacher, Boris <rohbo_at_sbox.tu-graz.ac.at>
Date: Sat, 27 Jun 1998 18:34:33 +0200
Message-ID: <35951F18.9A9C3BCE@sbox.tu-graz.ac.at>

Hi Jason

> Column Name Total
> ------------------------------
> Column1 20
> Column2 33
> Column3 9
> ColumnN X
>
> Is there a simple way to do this?

As I don't know how dynamically it should be, here's a statement thats poor performing and limitet to the cols you define

select 'Column 1' col_name , count( decode( column1,null,1,null) ) from the_table
union
select 'Column 2' col_name , count( decode( column2,null,1,null) ) from the_table
union
select 'Column 3' col_name , count( decode( column3,null,1,null) ) from the_table
union
...

So script building this statement

> 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.

The answer depends on Version of Oracle you are running :If you are running any version prior 7.3.x you cann't build table of records which is a pitty a leads to solutions like yours. Nevertheless you should be able to reference a table entry. I did this very often.

for instance :

type varchar_table_typ is table of varchar2(40) index by binary_integer;

type     record_of_tables_typ  is record (   current_row_count
number                 ,

tab1                                varchar2_table      ,

tab2                                varchar2_table       ) ;
tab_rec        record_of_tables_typ ;

Now to fill the table :

 for idx in 1..whatever loop

• code to get some data, for instance from a query tab_rec.tab1( idx ) := DATA_FROMANYWHERE -- for this row tab_rec.tab2( idx ) := DATA_FROMANYWHERE -- for this row tab_rec.current_row_count := idx ; -- to prevent lose of idx when any exception happens , This maintains rowcout currently in the table end loop;
  • Now to reference I always did :

tmp_data varchar2(40);

 begin
   for idx in 1...tab_rec.current_row_count loop     begin
      tmp_data := tab_rec.tab1( idx ) ;     execption

     when NO_DATA_FOUND then                 -- this means  row  #idx
doesn't exist anymore in the table for any reason
       tmp_data := null;
     end;

    if tmp_data .......
  end loop;
end ;

If you would happen to have Oracle 7.3.xxx or greater then you could make use of the new table features.
Really fine are table of records instead of records of tables. The first is what a programmer would naturally expect from database programming language.

type my_rec_type is record ( name varchar2(30) ,

                                                        null_count
number ) ;
type       my_tab_type        is table of my_rec_type ;

nll_tab        my_tab_type;

Features : Count of records in table is maintained by Oracle, You can use exists, delete .... Is really simple and easy going.

ex :

for idx in 1..nll_tab.count loop

  if nll_tab(idx).null_count > 0 then ......

end loop:

HTH
Regards Robo

Received on Sat Jun 27 1998 - 11:34:33 CDT