Re: Several rows from Dual

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 12 Feb 2009 19:33:07 +0100
Message-ID: <49946B63.3000609_at_gmail.com>

Hans Mayr schrieb:
> Hello,
>
> for some reason I need something like a "select 123 as abc from dual;"
> but with several result rows rather than one. The result should look
> like
>
> ABC
> 123
> 456
> 789
>
> So I came up with the following solution which seems akward to me:
>
> SELECT
> abc
> FROM
> (SELECT level abc FROM dual CONNECT BY level <= 999
> )
> WHERE
> abc IN(123, 456, 789) ;
>
> It was essential to me that I could easyly change the number of
> results like here through just writing them between the brackets,
> otherwise I could have used a UNION statement. But my solution looks
> ugly, especially the "<=999" because I will need it for bigger than 3
> digit numbers. Does anybody know a better solution?
>
> Thanks and best,
>
> Hans

It seems to be not very efficient to generate tousend rows if you know a priori, you'll need only 3 from this rowset, so i would probably do a union all.
If you have a possibility to create helper types, then ( i often seen it   in William Robertson's examples and like this approach), you can do something like

SQL> create or replace type number_t is table of number

   2 ;
   3 /

Type created.

SQL>
SQL> select *

   2 from table(number_t(123,456,789))
   3 ;

COLUMN_VALUE

---

          123
          456
          789

Best regards

Maxim Received on Thu Feb 12 2009 - 12:33:07 CST