Re: Several rows from Dual

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 13 Feb 2009 22:43:28 +0100
Message-ID: <4995E980.7030109_at_gmail.com>

Hans Mayr schrieb:
> 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

If you are simply looking for different techniques to solve that task, besides populating a global temporary table as suggested by Mark, here 2 another alternatives with plain sql. You could as well achieve that with built in xml functions ( i mean primarily xquery/xml table), but being probably most flexible, they would probably perform worst compared to other approaches.

SQL> select r

   2 from dual
   3 model return updated rows
   4 dimension by (1 r)
   5 measures (cast (null as number) n)    6 rules
   7 (
   8 n[for r in (123,456,789,42)]=null
   9 )
  10 ;

          R

---

        123
        456
        789
         42

SQL>
SQL> with t as (

   2 select '123,456,789,42' s from dual    3 )
   4 select regexp_substr(s,'\w+',1,level)    5 from t
   6 connect by regexp_substr(s,'\w+',1,level)    7 is not null
   8 ;

REGEXP_SUBSTR(S,'\W+',1,LEVEL)

---

123
456
789
42

If you are looking in general for good examples of sql/plsql, in my opinion good places are

http://www.oracle-developer.net/index.php
http://www.williamrobertson.net/
http://www.sqlsnippets.com/en/home.html

Best regards

Maxim Received on Fri Feb 13 2009 - 15:43:28 CST