A List of Values

How often do you need to generate a list of numbers. OK well perhaps not every day, but the technique shown here should be easily adaptable to a number of situations.

WITH tbl AS
    (SELECT     ROWNUM AS RN
           FROM DUAL
     CONNECT BY LEVEL < 10 )
SELECT *
 FROM tbl;

This will generate a result set of nine rows each row containing one of the numbers 1 - 9.

Notice I could have just written

SELECT     ROWNUM AS rn
     FROM DUAL
CONNECT BY LEVEL < 10 

This example also introduces the WITH keyword which I personally find extremely useful. Other colleagues find it annoying, though I expect that will change once they become accustomed to using it more.

Personally I like the structure using WITH enforces on my coding. I also find understanding the workings of SQL statements much easier. Maybe it's because the inner workings are broken up into more accesible chunks, with the inline view code moved out of the FROM clause, decluttering the SQL.