Tuesday, June 3, 2014

sql connect by level

Sometimes it's useful to generate in SQL a series of data responding to a certain criteria.

Like "the integers between 0 and N", or "the dates between today and 5 days ago"

You can do it with "connect by level":

SELECT level from DUAL connect by level <= 10 ;

SELECT TRUNC((sysdate-5 + (LEVEL))) AS DATES FROM DUAL connect by level <= ( sysdate-(sysdate-5) ) order by level;

No comments: