Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    37

    Unanswered: Calendar month table with CTE

    Hi,
    I have a simple calendar table generated in SqlServer that I need to create in oracle, just a list of months between two dates. The MSS is
    Code:
    -- SQL Server version
    -- Generate a calendar table containing the months from start to end date
    WITH cte_months 
    AS 
    ( 
        SELECT cast('2009-10-01' as datetime) AS aMonth     -- parameter
        UNION ALL 
        SELECT DATEADD(mm, 1, aMonth) 
        FROM cte_months
        WHERE DATEADD(mm, 1, aMonth) < '2010-10-01'        -- parameter
    ) select * from cte_months
    which gives output
    Code:
    2009-10-01 00:00:00.0
    2009-11-01 00:00:00.0
    2009-12-01 00:00:00.0
    2010-01-01 00:00:00.0
    2010-02-01 00:00:00.0
    2010-03-01 00:00:00.0
    2010-04-01 00:00:00.0
    2010-05-01 00:00:00.0
    2010-06-01 00:00:00.0
    2010-07-01 00:00:00.0
    2010-08-01 00:00:00.0
    2010-09-01 00:00:00.0
    Can anyone help changing to oracle? This is what I have so far, but its complaining about WITH clause:

    ORA-32031: illegal reference of a query name in WITH clause

    Code:
    -- Generate a calendar table containing the months from start to end date
    WITH cte_months
    AS 
    ( 
        SELECT to_date('2009-10-01', 'YYYY-MM-DD') AS aMonth from dual    
        UNION ALL 
        SELECT add_months(aMonth, 1) FROM cte_months
        WHERE add_months(aMonth, 1) < to_date('2010-10-01', 'YYYY-MM-DD')
    ) select * from cte_months
    thanks
    Last edited by FLANDERS; 09-13-10 at 09:29.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Try this:
    Code:
    WITH cte_months
            AS (    SELECT ADD_MONTHS (TO_DATE ('2009-10-01', 'YYYY-MM-DD'),
                                       (LEVEL - 1))
                              AS aMonth
                      FROM DUAL
                CONNECT BY LEVEL <= 12)
    SELECT *
      FROM cte_months;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Nov 2009
    Posts
    37
    Damn, I literally just figured that exact approach following much googling and was about to triumphantly answer my own question!!

    Thanks a million, much simpler than DB2/MSS it appears

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Yeah, it didn't work because Oracle doesn't support recursive CTE's.
    Another approach on Oracle is to use rownum from the all_objects (or any table with sufficient number of rows) table to generate a series of numbers.
    Code:
    SELECT add_months(date '2001-01-01', rownum - 1) mon
    FROM all_objects ao
    WHERE rownum <= 12
    But I like the CONNECT BY better.

Posting Permissions

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