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 has been working with Oracle since 1991 using Oracle 6 through to 12.  He has never met a SQL statement that he cant tame

About The Author

The Usual Suspect
– Scott Hollows –

• Oracle and Delphi software developer.
• Australian Delphi User Group – Western Australia Chief Cat Herder
• Australian Delphi User Group – President