Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

sql iteration

alinuxJul 16 2008 — edited Jul 16 2008
Hi guys,

It is possible to iterate some how in one select command?

Example:

I have one dates table: date_table

and select a_data from date_table.

I want to build a new select that will execute my date_table select for a specific number of times. I will input the number of times, using a parameter:


the resulting select will be similar to:
 
for i in 1..:n loop 
select a_data + from date_table; 
end loop; 
Can I do this in one select?

joining with
(select a from (select level a  from dual connect by 1 = 1) where rownum < 3) a
will be an idea?
Thanks

Comments

isotope
Well, test it:
test@ora>
test@ora> --
test@ora> with date_table as (
  2    select sysdate as dt from dual union all
  3    select sysdate - 1   from dual union all
  4    select sysdate - 2   from dual)
  5  --
  6  select dt, a.a
  7  from date_table d,
  8  (select a from (select level a  from dual connect by 1 = 1) where rownum < 3) a;

DT                 A
--------- ----------
16-JUL-08          1
15-JUL-08          1
14-JUL-08          1
16-JUL-08          2
15-JUL-08          2
14-JUL-08          2

6 rows selected.

test@ora>
test@ora> --
test@ora> -- or
test@ora> --
test@ora> with date_table as (
  2    select sysdate as dt from dual union all
  3    select sysdate - 1   from dual union all
  4    select sysdate - 2   from dual)
  5  --
  6  select dt, a.a
  7  from date_table d,
  8  (select level a from dual connect by level < 3) a;

DT                 A
--------- ----------
16-JUL-08          1
15-JUL-08          1
14-JUL-08          1
16-JUL-08          2
15-JUL-08          2
14-JUL-08          2

6 rows selected.

test@ora>
test@ora>
isotope
Frank Kulash

Hi,

Cross-joining with a counter-table, that is, your

(select a from (select level a  from dual connect by 1 = 1) where rownum < 3) a

for example, or its simpler and more common equivalent

(   SELECT      LEVEL AS a
    FROM        dual
    CONNECT BY  LEVEL <= 3
) a

can give you some of the benefits of iteration. There are differences, which can be important. For example, in a procedural loop, like

for i in 1..:n loop 
    select a_data + from date_table; 
end loop; 

you can be sure that the iteration for i=1 is done before the iteration for i=2, and variables that you set in one iteration can be referenced in the next.
In Oracle 10 (and up) the MODEL feature allows true iteration.

alinux
Thanks for your answers.
this will work for me in the first phase:
select res.level_no + a - 1, res.rp_id
  from (select level_no, rp_id from cont_cust where h = a and rownum < 2) res,
        (select level a from dual connect by level <= 5)
Res table will always return one row.

But if I want to use the level(a) in some condition in res table.

the result of res table to be modified by 1,2,3 or 4(use it in where clause)?

see in bold-that is not possible...but something similar I want to obtain.

thanks
Frank Kulash

Hi,

It looks like you now know how to create a sub-query a that contains the integers 1, 2, ... n. It's as if you had a table with n rows, containing the numbers 1, 2, ..., n. You can use that result set the same way you use any other result set. Usually, that means joining it to some other result set. You can do inner joins, outer joins, or cross joins: each are appropriate for different tasks.
The reason your bolded text:

  from (select level_no, rp_id from cont_cust where h = a and rownum < 2) res,

doesn't work is that the query is based entirely on cont_cust, which (I suppose) doesn't have a column called a. If you want to reference column a from table a, you have to join with table a. "h = a" would be a perfectly valid join condition.

I'm not sure what you're trying to do. Concrete questions are always easier to answer. Can you post a specific example?
For instance:
"I want to display five copies of a given row from a table (say, the row from scott.emp that has the employee named King). I want to number those rows 1 through 5, and flag a given row (say, #3)".

If that were your question, the answer might be:

WITH a AS 
(
    SELECT     LEVEL AS a
    FROM       dual
    CONNECT BY LEVEL <= 5
)
SELECT      a.a
,           emp.ename
,           CASE
                WHEN a.a = 3  THEN 'This is special' 
            END    AS flag
FROM        a
CROSS JOIN  scott.emp
WHERE       ename = 'KING';

Output:

         A ENAME      FLAG
---------- ---------- ---------------
         1 KING
         2 KING
         3 KING       This is special
         4 KING
         5 KING
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 13 2008
Added on Jul 16 2008
4 comments
2,414 views