Re: Several rows from Dual
Date: Fri, 13 Feb 2009 22:43:28 +0100
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.
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
8 n[for r in (123,456,789,42)]=null
123 456 789 42
SQL> with t as (
2 select '123,456,789,42' s from dual
4 select regexp_substr(s,'\w+',1,level) 5 from t
6 connect by regexp_substr(s,'\w+',1,level) 7 is not null
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
Maxim Received on Fri Feb 13 2009 - 15:43:28 CST