Re: Several rows from Dual

From: Robert Klemme <>
Date: Fri, 13 Feb 2009 22:21:14 +0100
Message-ID: <>

On 13.02.2009 15:47, Mark D Powell wrote:
> On Feb 13, 8:45 am, Hans Mayr <> 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?


        robert Received on Fri Feb 13 2009 - 15:21:14 CST

Original text of this message