Generate Values in Oracle SQL

I just came across an awesome SQL which I wanted to share with you. This is a SQL query which generates a sequence of values. I often need this when generating “0” (zero) values for graphs.

SQL> SELECT level FROM dual CONNECT BY level <= 7;

     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7

Here is another example:

SQL> SELECT TRUNC(SYSDATE-level+1) some_date FROM dual CONNECT BY level <= 7;

 SOME_DATE
----------
  06.02.17 (DD.MM.YY of today)
  05.02.17
  04.02.17
  03.02.17
  02.02.17
  01.02.17
  31.01.17

You can test these SQLs in Oracle’s “LiveSQL” webapp: https://livesql.oracle.com/apex/livesql/file/content_EX5HVU5FF1AOSJMCU9W0BZOHK.html

That’s IT

Advertisement