Re: Subqueries in FROM clause?

Hi

No loops per se in SQL, but PLSQL is a procedural language you may use to wrap your SQL, then using Native Dynamic SQL you can vary table names, etc. in your code to make writing SQL easier.

Good luck
Greg

Jürgen Reinecke <jgreinecke_at_web.de> wrote in message news:<3F914C9D.6D71438D_at_web.de>...
> Hi Folks,
>
>
> projects quite often require identical queries from several
> tables, a problem that I so far tackled by using positional
> parameters when calling an sql-script. The parameter was the name
> of the table to be queried, and I had to call the script several
> times.
>
> Alternatively I could write scripts that contain the same code for
> each queried table, which however is highly
> redundant code.
>
> Hence my request: does SQL provide some kind of 'loop' ?
> I tried the following workaround (see the code below)
> a) creating a table 'loop'
> - that contains the names of all tables that will be queried
> b) refer to that table in the from-clause of any query
> - by selecting it's contents instead of specifying a tablename
>
> Alas Oracle comes up with Error 904 (invalid identifier).
> Is that inevitable or am I missing something?
>
>
> Thanks in advance
>
>
> Jürgen
>
> P. S: Here comes my code
> ------------------------
> - rest assured that all tables mentioned here exist,
> - and that they contain a column named old_system_id.
>
> host clear
> host echo -e "creating table loop"
> drop table loop;
> create table loop (
> line_nr number(5),
> tab_name varchar2(20)
> );
> describe loop
>
> host echo -e "done\n- inserting values"
> insert into loop values (1,'src_ca');
> insert into loop values (2,'src_ma');
> insert into loop values (3,'src_mn');
> insert into loop values (4,'src_ny');
> insert into loop values (5,'src_tx');
>
> host echo -e "done\n- displaying values"
> select * from loop;
>
> host echo -e "done\n- AND HERE THE TEST..."
> select src.old_system_id
> from (select tab_name from loop) src;
Received on Mon Oct 20 2003 - 07:37:33 CDT