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!

kind of sequential query

MichaelSFeb 17 2007 — edited Jun 21 2007

Hello folks,

my requirement is: Given a number N (let's say 7 for this example) generate a query with following output:

SELECT A,B FROM some_query
/

A  B
-------
1  7
1  7
1  7
1  7
1  7
1  7
1  7
2  6
2  6
2  6
2  6
2  6
2  6
3  5
3  5
3  5
3  5
3  5
4  4
4  4
4  4
4  4
5  3
5  3
5  3
6  2
6  2
7  1

28 rows selected.

Any ideas?

Muthukumar S

simple workaround stricks my mind is that go for creating some functions.

or try hierarchy queries with connect by sys clause

Nicolas Gasparotto

Michaels,

Here a try :

SQL> select c1, c2
  2  from (select decode(mod(rownum,myvalue),0,myvalue,mod(rownum,myvalue)) c1,
  3               myvalue-decode(mod(rownum,myvalue),0,myvalue-1,mod(rownum,myvalue)-1) c2,
  4               row_number() over (partition by decode(mod(rownum,myvalue),0,myvalue,mod(rownum,myvalue)) order by null) rn
  5        from (select 7 myvalue from dual)
  6        connect by level <= myvalue*myvalue)
  7  where c2>=rn
  8  order by c1
  9  /

        C1         C2
---------- ----------
         1          7
         1          7
         1          7
         1          7
         1          7
         1          7
         1          7
         2          6
         2          6
         2          6
         2          6
         2          6
         2          6
         3          5
         3          5
         3          5
         3          5
         3          5
         4          4
         4          4
         4          4
         4          4
         5          3
         5          3
         5          3
         6          2
         6          2
         7          1

28 rows selected.

Nicolas.

MichaelS

Incredible! Thanks a lot Nicolas.

Regards,
Michael

Nicolas Gasparotto

You're welcome !

Nicolas.

mennan

One other alternative may be:

SQL> SELECT t3.i, t3.k
  2    FROM (SELECT t1.i, t1.k, trunc((rownum - 1) / 7) + 1 d, rownum r
  3            FROM (SELECT LEVEL i, 8 - LEVEL k FROM dual CONNECT BY LEVEL < 8) t1
  4                ,(SELECT dummy FROM dual CONNECT BY LEVEL < 8) t2) t3
  5   WHERE t3.i + t3.r <= ( 7 * t3.d) + 1;

         I          K
---------- ----------
         1          7
         1          7
         1          7
         1          7
         1          7
         1          7
         1          7
         2          6
         2          6
         2          6
         2          6
         2          6
         2          6
         3          5
         3          5
         3          5
         3          5
         3          5
         4          4
         4          4
         4          4
         4          4
         5          3
         5          3
         5          3
         6          2
         6          2
         7          1

28 rows selected

SQL>

More generic

SELECT t3.i, t3.k
  FROM (SELECT t1.i, t1.k, trunc((rownum - 1) / :base_number) + 1 d, rownum r
          FROM (SELECT LEVEL i, :base_number + 1 - LEVEL k FROM dual CONNECT BY LEVEL < :base_number + 1) t1
              ,(SELECT dummy FROM dual CONNECT BY LEVEL < :base_number + 1) t2) t3
 WHERE t3.i + t3.r <= ( :base_number * t3.d) + 1;

MichaelS

Thanks too mennan.
But ...arghh .... I have to modify my initial output again to read:

Any help is kindly appreciated.

Regards,
Michael

mennan

SQL> SELECT t3.i, t3.k, t3.j
  2    FROM (SELECT t1.i, t1.k, t2.j, trunc((rownum - 1) / 7) + 1 d, rownum r
  3            FROM (SELECT LEVEL i, 8 - LEVEL k FROM dual CONNECT BY LEVEL < 8) t1
  4                ,(SELECT LEVEL j FROM dual CONNECT BY LEVEL < 8) t2) t3
  5   WHERE t3.i + t3.r <= (7 * t3.d) + 1;

         I          K          J
---------- ---------- ----------
         1          7          1
         1          7          2
         1          7          3
         1          7          4
         1          7          5
         1          7          6
         1          7          7
         2          6          1
         2          6          2
         2          6          3
         2          6          4
         2          6          5
         2          6          6
         3          5          1
         3          5          2
         3          5          3
         3          5          4
         3          5          5
         4          4          1
         4          4          2
         4          4          3
         4          4          4
         5          3          1
         5          3          2
         5          3          3
         6          2          1
         6          2          2
         7          1          1

28 rows selected

SQL> 


SELECT t3.i, t3.k, t3.j
  FROM (SELECT t1.i, t1.k, t2.j, trunc((rownum - 1) / :base_number) + 1 d, rownum r
          FROM (SELECT LEVEL i, :base_number + 1 - LEVEL k FROM dual CONNECT BY LEVEL < :base_number + 1) t1
              ,(SELECT LEVEL j FROM dual CONNECT BY LEVEL < :base_number + 1) t2) t3
 WHERE t3.i + t3.r <= ( :base_number * t3.d) + 1;

Nicolas Gasparotto

Michael,

From my query above, use rn instead of c2 into the main select :

select c1, rn
from (select decode(mod(rownum,myvalue),0,myvalue,mod(rownum,myvalue)) c1,
             myvalue-decode(mod(rownum,myvalue),0,myvalue-1,mod(rownum,myvalue)-1) c2,
             row_number() over (partition by decode(mod(rownum,myvalue),0,myvalue,mod(rownum,myvalue)) order by null) rn
      from (select 7 myvalue from dual)
      connect by level <= myvalue*myvalue)
where c2>=rn
order by c1
/

That keep you two options with the mennan's query ;-)

HTH,

Nicolas.

443806

More terse, i.e. with fewer constructs?

WITH S AS (SELECT LEVEL N FROM DUAL CONNECT BY LEVEL <= :MYMAX),
T AS (SELECT A.N T1, B.N T2 FROM S A, S B WHERE A.N + B.N <= :MYMAX + 1),
U AS (SELECT A.N U1, B.N U2 FROM S A, S B WHERE A.N + B.N = :MYMAX + 1)
SELECT T.T1, U.U2 FROM T, U WHERE T.T1 = U.U1
ORDER BY T.T1, U.U2

T1 U2
1 7
1 7
1 7
1 7
1 7
1 7
1 7
2 6
2 6
2 6
2 6
2 6
2 6
3 5
3 5
3 5
3 5
3 5
4 4
4 4
4 4
4 4
5 3
5 3
5 3
6 2
6 2
7 1

ushitaki

select a,b from
(
select
t0.rn a
,max(t1.rn) over() - t0.rn + 1 b
from
(select rownum rn from dual connect by rownum <=7) t0
,(select rownum rn from dual connect by rownum <=7) t1
where t0.rn <= t1.rn
)
order by a,b
;

Aketi Jyuuzou

select a.A,max(B)
  from (select RowNum as A from all_catalog a where RowNum <= 7*(7+1)/2) a,
       (select RowNum as B,sum(RowNum) over (order by RowNum desc) as Counter
          from all_catalog
         where RowNum <=7) b
 where a.A <= b.Counter
group by A.a
order by A.a;

"where RowNum <= 7*(7+1)/2" is Gauss's Algorithm to derive sum.
http://www.uh.edu/engines/epi2087.htm

MichaelS

srinik001, ushitaki, Aketi Jyuuzou. Thanks very much for your efforts, but your queries don't give the desired output, as required in one of my later posts:

A B
------
1 1
1 2
1 3
1 4
1 5
1 6
1 7
2 1
2 2
2 3
2 4
2 5
2 6
3 1
3 2
3 3
3 4
3 5
4 1
4 2
4 3
4 4
5 1
5 2
5 3
6 1
6 2
7 1

Regards,
Michael

Aketi Jyuuzou

SQL> select a.A,row_number() over(partition by max(b.B) order by a.A) as B
  2    from (select RowNum as A from all_catalog a where RowNum <= 7*(7+1)/2) a,
  3         (select RowNum as B,sum(RowNum) over (order by RowNum desc) as Counter
  4            from all_catalog
  5           where RowNum <=7) b
  6   where a.A <= b.Counter
  7  group by a.A
  8  order by a.a;

        A          B
---------  ---------
        1          1
        2          2
        3          3
        4          4
        5          5
        6          6
        7          7
        8          1
        9          2
       10          3
       11          4
       12          5
       13          6
       14          1
       15          2
       16          3
       17          4
       18          5
       19          1
       20          2
       21          3
       22          4
       23          1
       24          2
       25          3
       26          1
       27          2
       28          1

121256

with t as (select level as i from dual connect by level <= 7)
select t1.i as a, t2.i - t1.i + 1 as b from t t1, t t2 where t1.i <= t2.i order by a, b

572471

SQL> var p number;
SQL> exec :p:=4;

PL/SQL procedure successfully completed
p
---------
4

SQL> 
SQL> select * from
  2  (select :p-trunc(dim/:p) a, s2 b from dual
  3   model
  4    dimension by (0 dim)
  5    measures(1 s, 0 s2)
  6     rules(s[for dim from 1 to power(:p,2)-1 increment 1] = mod(CV(dim),:p)+1,
  7           s2[any] = case when s[CV()]<=trunc((CV(dim))/:p)+1 then s[CV()] end))
  8   where b is not null
  9   order by a,b
 10  /

         A          B
---------- ----------
         1          1
         1          2
         1          3
         1          4
         2          1
         2          2
         2          3
         3          1
         3          2
         4          1

10 rows selected
p
---------
4

SQL>

Rob van Wijk

or:

SQL> var N number
SQL> exec :N := 7

PL/SQL-procedure is geslaagd.

SQL> select a
  2       , b
  3    from dual
  4   model
  5         dimension by (1 a, 1 b)
  6         measures (dummy)
  7         rules iterate (1000) until (:N - iteration_number = 0)
  8         ( dummy[iteration_number+1,for b from 1 to :N - iteration_number increment 1] = 0
  9         )
 10  /

    A     B
----- -----
    1     1
    1     2
    1     3
    1     4
    1     5
    1     6
    1     7
    2     1
    2     2
    2     3
    2     4
    2     5
    2     6
    3     1
    3     2
    3     3
    3     4
    3     5
    4     1
    4     2
    4     3
    4     4
    5     1
    5     2
    5     3
    6     1
    6     2
    7     1

28 rijen zijn geselecteerd.

SQL> exec :N := 4

PL/SQL-procedure is geslaagd.

SQL> select a
  2       , b
  3    from dual
  4   model
  5         dimension by (1 a, 1 b)
  6         measures (dummy)
  7         rules iterate (1000) until (:N - iteration_number = 0)
  8         ( dummy[iteration_number+1,for b from 1 to :N - iteration_number increment 1] = 0
  9         )
 10  /

    A     B
----- -----
    1     1
    1     2
    1     3
    1     4
    2     1
    2     2
    2     3
    3     1
    3     2
    4     1

10 rijen zijn geselecteerd.

Regards,
Rob.

Message was edited by:
Rob van Wijk

Oops, wrong output. Let me try again ...

Message was edited by:
Rob van Wijk

This one looks more like it:

SQL> var N number
SQL> exec :N := 7

PL/SQL-procedure is geslaagd.

SQL> select a
  2       , b
  3    from dual
  4   model
  5         dimension by (1 a, 1 z)
  6         measures (:N b)
  7         rules iterate (1000) until (:N - iteration_number = 0)
  8         ( b[iteration_number+1,for z from 1 to :N - iteration_number increment 1] = :N - iteration_number
  9         )
 10  /

    A     B
----- -----
    1     7
    1     7
    1     7
    1     7
    1     7
    1     7
    1     7
    2     6
    2     6
    2     6
    2     6
    2     6
    2     6
    3     5
    3     5
    3     5
    3     5
    3     5
    4     4
    4     4
    4     4
    4     4
    5     3
    5     3
    5     3
    6     2
    6     2
    7     1

28 rijen zijn geselecteerd.

SQL> exec :N := 4

PL/SQL-procedure is geslaagd.

SQL> select a
  2       , b
  3    from dual
  4   model
  5         dimension by (1 a, 1 z)
  6         measures (:N b)
  7         rules iterate (1000) until (:N - iteration_number = 0)
  8         ( b[iteration_number+1,for z from 1 to :N - iteration_number increment 1] = :N - iteration_number
  9         )
 10  /

    A     B
----- -----
    1     4
    1     4
    1     4
    1     4
    2     3
    2     3
    2     3
    3     2
    3     2
    4     1

10 rijen zijn geselecteerd.

1 - 16

Locked Post

New comments cannot be posted to this locked post.

Locked on Jul 18 2007

Added on Feb 17 2007

16 comments

3,504 views

SQL & PL/SQL

kind of sequential query

Comments

Post Details