Re: Is there anyway to use a tablename in subquery?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 27 May 2009 06:58:31 -0700 (PDT)
Message-ID: <46b86dd3-f218-4c1b-85da-7428fb9b79a5_at_r3g2000vbp.googlegroups.com>

On May 27, 1:51 am, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:
> Jia Lu (Roka..._at_gmail.com) wrote:
>
> : Hi all
>
> : Is there anyway to use a tablename in subquery like this:
>
> : SELECT * FROM ( SELECT tablebame FROM TABLE);
>
> Not directly, and not in plain sql.
>
> You can build the statement as a string using pl/sql and the run it using
> EXECUTE IMMEDIATE.  (google: Oracle Execute Immediate)
>
> You can do the equivalent using COLUMN NEW_VALUE within SQLPlus.
>         column TABLE_NAME new_value THE_TABLE_NAME
>         select table_name FROM TABLE;
>         select * from &THE_TABLE_NAME ;
>
> You can build a view that UNIONS a whole bunch of similar tables together
> and then select against that view.
>         create bunch_of_tables as
>         select 'TABLE1' table_name ,t1.* from TABLE1 t1
>         UNION ALL
>         select 'TABLE2' table_name ,t2.* from TABLE2 t2
>
>         (in real life you would explicily select the columns you needed).
>
> Probably other techniques, but none of them do what you want directly.
>
> $0.10

Jia, If Malcolm's post doesn't provide you with enough options to figure out how to tackle you problem post back with an explanation of the problem you are trying to solve and maybe someone will be able to offer additional suggestions.

HTH -- Mark D Powell -- Received on Wed May 27 2009 - 08:58:31 CDT