thanks Dave, so how do you use cursors? can you give me a quick example,
thanks in advance
Dave Hau wrote:
> 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
>
>
--
Your favorite stores, helpful shopping tools and great gift ideas.
Experience the convenience of buying online with Shop_at_Netscape!
http://shopnow.netscape.com/
Received on Wed Jul 02 2003 - 16:58:18 CDT
