Can somebody explain this sql to me.
How does it get the result how are the dates being manufactured .
It gets all the dates between given two dates from date and to date
When i run the sql containing only the subquery factoring clause (with statement) it just gets one record i.e the form date
with all_days as
connect by level<=to_date ('&to_date', 'mm/dd/yyyy')
- to_date ('&from_date', 'mm/dd/yyyy')
SELECT statement adds a pseudocolumn LEVEL and subtracts 1 from the FROM date. Simplified, it would look like
SELECT TO_DATE('15.08.2007', 'dd.mm.yyyy') + LEVEL - 1
CONNECT BY LEVEL <= 8 + 1
which would generate all dates between 15.08.2007 and 23.08.2007.
WITH is used to create an "imaginary" table; when it didn't exist, we had to create a "real" table, insert records into it and then select records in order to display the result. Now we can skip CREATE and INSERT.