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

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

Hi Maxim,

Thanks. This is an elegant solution. However I wonder if there is a
possibility which does not involve creating anything, just a plain
"one sql statement solution" ...

Best,

Hans

On Feb 13, 3:56*am, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:
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;

Hi Joel!

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

On Feb 13, 8:45*am, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:
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 --

On 13.02.2009 15:47, Mark D Powell wrote:
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?

Cheers

robert

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

Thanks Maxim! Another great answer.

Robert: The context is as follows: A user analyses a couple of items.
This analysis has to be done manually. For several items (ids
123,456,789) he identified one has to perform an insert. I was looking
for an easy way to perform this insert so I take the result of the
query above, do a join on it which returns the rows to be inserted.
The point was to have a single point in the insert sql which had to be
changed to do the operation.

Best,

Hans

On Feb 13, 8:56*am, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:
If you don't want to create a type, you could just use one that
already exists. Query all_coll_types where coll_type = 'TABLE'
and elem_type_name is 'INTEGER' or 'NUMBER' and take your pick. How
about ku$_objnumset? (It's used in DBMS_METADATA.)