Analytics Software

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

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.

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

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