Scott Hollows – One Line At A Time

This simple technique will show you how to write a SELECT statement that dynamically creates a number of rows.  It can be used to duplicate data or create a numbers on the fly, so the data looks like this.

DATA
1
2
3
4
5

Select a range of numbers

To dynamically create a range of numbers we can re-purpose Oracle’s CONNECT BY query that is normally usually used for selecting hierarchical data. Like this

SELECT  LEVEL  DATA
FROM    DUAL
CONNECT BY LEVEL <= 5

DATA
1
2
3
4
5

Duplicating rows

For another example, lets say you want to duplicate your source data so each row appears multiple times in the query results Lets make each row in the STOCK_CATEGORY table appear twice. This is how you do it

SELECT LEVEL, S.CODE, S.NAME
FROM   DUAL  D,
       STOCK_CATEGORY S
CONNECT BY LEVEL <= 2
ORDER BY S.CODE, LEVEL

LEVEL    CODE     NAME
1        HAMMER   Stop Hammer Time
2        HAMMER   Stop Hammer Time
1        NAIL     Yeah you nailed it
2        NAIL     Yeah you nailed it

Database Nerd Notes – This is called a cartesian join where there is no join condition between the two data sources

Scott Hollows

• Oracle & Delphi software developer based in Perth, Western Australia
• Australian Oracle User Group – National Committee Member
• Australian Delphi User Group – National Committee Member