Re: Several rows from Dual

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Fri, 13 Feb 2009 06:47:20 -0800 (PST)
Message-ID: <37d71fb8-f9d4-4514-bda1-3eb253618d85_at_m42g2000yqb.googlegroups.com>



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.

HTH -- Mark D Powell -- Received on Fri Feb 13 2009 - 08:47:20 CST

Original text of this message