Re: sql more intelligent

Paul wrote:
> hi all
>
> if i have a table with
>
> name char(30)
> group char(30)
>
> the name refers to 'schema' names, can i circulate through this table
> and union the results of other tables across more than one schema
>
> for example say we have the following data in this table
>
> name group
> fred high
> thomas low
> john high
> mark medium
>
> so i first to get the schema's i am interested in "select name from
> thistable where group = 'high'" - no i want to do this select
> firstschema.result+secondschema.result from commontable;
>
> now in this example firstschema would be fred, and secondschema would be
> john, bearing in mind that we dont always have a static number of names
> returned from the first select, how do a union the results together?

You can do this by writing a PL/SQL procedure. Do the first select ("select name from thistable where group = 'high'") and put the result into a cursor. Then iterate through this cursor while building the dynamic SQL statement for the second statement ("select firstschema.result+secondschema.result from commontable"). Then execute the dynamic SQL and return the result.

Cheers,
Dave

>
> thanks in advance for your assistance
> paul
Received on Wed Jul 02 2003 - 16:34:20 CDT