Re: Nested select....

From: Avi Abrami <aabrami_at_intersystemsww.com>
Date: Sun, 04 Aug 2002 13:45:32 +0200
Message-ID: <3D4D13DC.C7BFE92C@intersystemsww.com>

Daniel Morgan wrote:

> 
> Viswanath Vijayaragavan wrote:
> 
> > Folks,Problem description : Out of a list of 20 tables T1... T20, I
> > want to find out the count of rows in those Tables among the 20,
> > which have a column called C1 (NUMBER (22)) and the value in C1 is
> > 125. Any suggestionsVJ
> 
> Assuming you have been running statistics as your are supposed to for
> the optimizer ...
> 
> SELECT COUNT(*)
> FROM all_tables
> WHERE table_name IN (
>    SELECT table_name
>    FROM all_tab_columns
>    WHERE column_name = 'whatever');
> 
> Daniel Morgan

VJ,
If I understand your question correctly, then I don't think Daniel has given you a complete answer. Allow me to try and do so, now.

I am assuming that the twenty tables are all in the same schema, and that you are logged into the database as that schema's owner. (For example, if the tables are in SCOTT's schema, then you are logged in as user SCOTT.) As far as I know, for a given database table, each column name must be unique, therefore column C1 can only be defined once per table.

The following query will give you a list of database tables (out of the twenty you are interested in) that contain column "C1 NUMBER(22)": select

   TABLE_NAME
from

   USER_TAB_COLUMNS
where

   DATA_TYPE = 'NUMBER'
and

   DATA_PRECISION = 22
and

   DATA_SCALE = 0
and

   COLUMN_NAME = 'C1'
and

   TABLE_NAME    in ('T1',


                     'T2',

                     'T3',

                     'T4',

                     'T5',

                     'T6',

                     'T7',

                     'T8',

                     'T9',

                     'T10',

                     'T11',

                     'T12',

                     'T13',

                     'T14',

                     'T15',

                     'T16',

                     'T17',

                     'T18',

                     'T19',

                     'T20')


Now you need to go through the tables in the list returned by the
above query and find out which of them contain rows where the value
of column "C1" is 125. The following query is one way to do that:

select
   count(*)
from
   <table-returned-by-first-query>
where
   C1 = 125

If this query returns 0 (zero), then the given table does _not_
contain a row where column "C1" has the value 125. Therefore, you
are interested in the tables where the above query returns a number
greater than zero. Remember, you need to execute the above query
separately for each table returned by the first query.

There are several ways you could combine the above operations (like

"wrapping" them in a PL/SQL procedure, for example), but I have chosen to separate them in order to make my answer simpler (I hope that's alright with you :-)

By the way, I (partially) tested the above using Oracle 8.1.7.4 on (SUN) Solaris 7.

Hope this helps you.

Good Luck,
Avi. Received on Sun Aug 04 2002 - 06:45:32 CDT