How to Query / Generate Multiple Rows from Oracle DUAL Table?


Method 1: Using Connect By Clause

Number Series from DUAL
SQL> SELECT rownum
FROM DUAL
CONNECT BY rownum <= 10;


    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

Generate Series of dates from DUAL
SQL> SELECT rownum+sysdate
FROM DUAL
CONNECT BY rownum <= 10;

ROWNUM+SYSDATE
------------------
21-DEC-12
22-DEC-12
23-DEC-12
24-DEC-12
25-DEC-12
26-DEC-12
27-DEC-12
28-DEC-12
29-DEC-12
30-DEC-12

10 rows selected.

Generate Multiplication Table from DUAL
SQL> SELECT rownum||' x 2 = '||rownum*2 MultiTable
FROM DUAL
CONNECT BY rownum <= 10 ;

MULTITABLE
-------------------
1 x 2 = 2
2 x 2 = 4
3 x 2 = 6
4 x 2 = 8
5 x 2 = 10
6 x 2 = 12
7 x 2 = 14
8 x 2 = 16
9 x 2 = 18
10 x 2 = 20

10 rows selected.
Method 2: Using Cube Function:
SQL> SELECT rownum||' x '||rownum||' = '||rownum*rownum Square_table FROM (
SELECT NULL FROM DUAL GROUP BY CUBE(1,2,3,4))  ;

SQUARE_TABLE
----------------
1 x 1 = 1
2 x 2 = 4
3 x 3 = 9
4 x 4 = 16
5 x 5 = 25
6 x 6 = 36
7 x 7 = 49
8 x 8 = 64
9 x 9 = 81
10 x 10 = 100
11 x 11 = 121
12 x 12 = 144
13 x 13 = 169
14 x 14 = 196
15 x 15 = 225
16 x 16 = 256

16 rows selected.

SQL> SELECT rownum||' x '||rownum||' = '||rownum*rownum Square_table
FROM (SELECT NULL FROM DUAL GROUP BY CUBE(1,2,3)) ;

SQUARE_TABLE
-----------------------
1 x 1 = 1
2 x 2 = 4
3 x 3 = 9
4 x 4 = 16
5 x 5 = 25
6 x 6 = 36
7 x 7 = 49
8 x 8 = 64

8 rows selected.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google