Re: Several rows from Dual
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Fri, 13 Feb 2009 22:21:14 +0100
Message-ID: <6vm6iqFkkf3pU1_at_mid.individual.net>
On 13.02.2009 15:47, Mark D Powell wrote:
> On Feb 13, 8:45 am, Hans Mayr <mayr1..._at_gmx.de> wrote:
>
> A global temporary table might be a solution for you. You would
> define the definition once then each program that referenced the table
> could execute a procedure or chunk of code to insert the values to be
> used in this run.
>
> Then subsequent SQL statements could reference the GTT to get the
> values. Because each session gets its own copy of the GTT multiple
> processes could run concurrently with different sets of values.
>
> If the application is web based then you might have to use a permanent
> table of tables structure where you add an identifier that the end
> user session uses to identify its list of values.
>
> The best solution depends on exactly why you need the list, how the
> list of values is determined, and how it is used. Maxim's solution
> sounded from your posts that it will do. I never remember to consider
> solutions like that but there are often multiple approaches and the
> best needs to be determined based on the requirements on hand.
Date: Fri, 13 Feb 2009 22:21:14 +0100
Message-ID: <6vm6iqFkkf3pU1_at_mid.individual.net>
On 13.02.2009 15:47, Mark D Powell wrote:
> On Feb 13, 8:45 am, Hans Mayr <mayr1..._at_gmx.de> wrote:
>> Hi Joel! >> >>> does this count as a one sql statement solution? >>> select 123 as abc from dual >>> union >>> select 456 from dual >>> union >>> select 789 from dual; >> I explicitly stated that I know that union could have been used and >> that I was looking for a solution that is easy to adapt from 1 to n >> rows. Don't misunderstand me, at this point I am not looking for "a" >> solution anymore, both my method and Maxim's solution work fine and >> yours would work, too, though it is less flexible. I just would like >> to learn more about sophisticated solutions regarding my problem to >> let me understand (Oracle) SQL better. >> >> Best, >> >> Hans
>
> A global temporary table might be a solution for you. You would
> define the definition once then each program that referenced the table
> could execute a procedure or chunk of code to insert the values to be
> used in this run.
>
> Then subsequent SQL statements could reference the GTT to get the
> values. Because each session gets its own copy of the GTT multiple
> processes could run concurrently with different sets of values.
>
> If the application is web based then you might have to use a permanent
> table of tables structure where you add an identifier that the end
> user session uses to identify its list of values.
>
> The best solution depends on exactly why you need the list, how the
> list of values is determined, and how it is used. Maxim's solution
> sounded from your posts that it will do. I never remember to consider
> solutions like that but there are often multiple approaches and the
> best needs to be determined based on the requirements on hand.
IMHO this is the most important question. A query returning three distinct numeric values is worthless in itself. Hans, what do you need that for?
Cheers
robert Received on Fri Feb 13 2009 - 15:21:14 CST