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
 |
 |
 |
Scott Hollows - one line at a time 