Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2004
    Posts
    115

    Unanswered: Somebody explain this sql to me

    Hi

    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

    Code:
    with all_days as
    (select to_date('&from_date','mm/dd/yyyy')
    +level
    -1 all_sundays
    from dual
    connect by level<=to_date ('&to_date', 'mm/dd/yyyy')
    		- to_date ('&from_date', 'mm/dd/yyyy')
    + 1)
    select all_sundays
    from all_days

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It is one of the row generator techniques. The simplest version of the same is
    Code:
    SQL> SELECT LEVEL
      2  FROM dual
      3  CONNECT BY LEVEL <=5;
    
         LEVEL
    ----------
             1
             2
             3
             4
             5
    Your query does the same, only with dates. As you've seen, LEVEL is a number; that's exactly what result of
    Code:
    to_date ('&to_date', 'mm/dd/yyyy') - to_date ('&from_date', 'mm/dd/yyyy')
    is - a number. So, if we presume that FROM_DATE = 15.08.2007 and TO_DATE = 23.08.2007, the result would be 8 (eight days):
    Code:
    SQL> select
      2  to_date('23.08.2007', 'dd.mm.yyyy') - to_date('15.08.2007', 'dd.mm.yyyy')
      3  from dual;
    
    TO_DATE('23.08.2007','DD.MM.YYYY')-TO_DATE('15.08.2007','DD.MM.YYYY')
    ---------------------------------------------------------------------
                                                                        8
    SELECT statement adds a pseudocolumn LEVEL and subtracts 1 from the FROM date. Simplified, it would look like
    Code:
    SELECT TO_DATE('15.08.2007', 'dd.mm.yyyy') + LEVEL - 1
    FROM dual
    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.

  3. #3
    Join Date
    Jun 2004
    Posts
    115
    Hi

    Thanks for a clear and a crisp explanation

    But the place where i get confused is

    Code:
    SELECT TO_DATE('15.08.2007', 'dd.mm.yyyy') + LEVEL - 1
    FROM dual
    CONNECT BY LEVEL <= 8 + 1
    that query gives only one row as output then how come the dates for 8 datys are generated

  4. #4
    Join Date
    Jun 2004
    Posts
    115
    Hi

    Also when i run this ia m getting only one row as opposed to 5 by you

    Code:
    SQL> select version from v$instance;
    
    VERSION
    -----------------
    9.2.0.7.0
    
    SQL> select level
      2  from dual
      3  CONNECT BY LEVEL<=5
      4
    SQL> /
    
         LEVEL
    ----------
             1

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Ah! Why didn't you say which version you are on? Algorithm has changed so what works in 10g doesn't in 9i. Try something like this:
    Code:
    SQL> select lvl
      2  from (select level lvl from dual
      3        connect by level <= 5
      4       );
    
           LVL
    ----------
             1
             2
             3
             4
             5
    
    SQL>

  6. #6
    Join Date
    Jun 2004
    Posts
    115
    Hi

    My apologies

    Thanks for the wonderful explanation that was crystal clear :-)

    Thanks for spending your precious time too

    regards
    Hrishy

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You're welcome! I'm glad you are satisfied with the answer.

  8. #8
    Join Date
    Jun 2004
    Posts
    115
    Hi

    I dont think so anybody would have explained that concept so clearly.

    I was actually grappling with it wundering and then you came in and made it look so easy.

    Thank you very much i appreciate

    regards
    Hrishy

  9. #9
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Just to add up to this thread.

    The technique with the "subquery factoring" (with clause) seems to work on all versions starting 9i. I tested this on 9iR1, 9iR2, 10gR1 and 10gR2. I have not done this test on 11g though.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •