Generate Values in Oracle SQL

livesql

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: