OraQA

• Categories

  • Administration
  • APEX (HTMLDB)
  • Concepts
    • Architecture
  • E-Business Suite
  • General
  • Jdeveloper
  • PL/SQL
  • Question
  • Security
  • SQL
  • Tuning
  • XML

• Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

• Links

  • AskTom.oracle.com
  • Oracle doc search
  • Oracle Technology Network
  • OraNA - Oracle News Aggregator

Oracle News

SQL> CREATE TABLE t2 AS
  2  SELECT 1 AS ID,
  3         TO_DATE ('01/01/2000', 'MM/DD/YYYY') AS start_date,
  4         TO_DATE ('11/18/2003', 'MM/DD/YYYY') AS end_date
  5    FROM DUAL
  6  UNION ALL
  7  SELECT 2 AS ID,
  8         TO_DATE ('01/01/2005', 'MM/DD/YYYY') AS start_date,
  9         TO_DATE ('11/18/2007', 'MM/DD/YYYY') AS end_date
 10    FROM DUAL
 11  /

Table created.

SQL> SELECT *
  2    FROM t2
  3  /

        ID START_DAT END_DATE
---------- --------- ---------
         1 01-JAN-00 18-NOV-03
         2 01-JAN-05 18-NOV-07

SQL> SELECT     ID,
  2             TO_CHAR (ADD_MONTHS (start_date, (LEVEL - 1) * 12), 'yyyy') YEAR
  3        FROM t2
  4  CONNECT BY PRIOR ID = ID
  5         AND LEVEL <=
  6                CEIL (  MONTHS_BETWEEN (GREATEST (end_date, start_date),
  7                                        LEAST (end_date, start_date)
  8                                       )
  9                      / 12
 10                     )
 11         AND PRIOR DBMS_RANDOM.STRING ('p', 10) IS NOT NULL
 12    ORDER BY ID, YEAR
 13  /

        ID YEAR
---------- ----
         1 2000
         1 2001
         1 2002
         1 2003
         2 2005
         2 2006
         2 2007

7 rows selected.

8 Responses to “How to generate multiple rows from a single row based on a date range”

1. mkapadia Says:
   March 14th, 2007 at 6:38 am

   SELECT TO_CHAR(ADD_MONTHS (start_year, (12 * r) - 12), ‘YYYY’) my
   FROM (
   SELECT TRUNC (start_date, ‘YEAR’) AS start_year, TRUNC
   (end_date, ‘YEAR’) AS end_year,
   TO_CHAR (TRUNC (end_date, ‘YEAR’), ‘YYYY’) -
   TO_CHAR (TRUNC (start_date, ‘YEAR’), ‘YYYY’) + 1
   AS yr
   FROM t2
   ORDER BY start_date, end_date
   ) a,
   (SELECT LEVEL r
   FROM DUAL
   CONNECT BY LEVEL

2. mkapadia Says:
   March 14th, 2007 at 6:41 am

   ..continuation from previous query:

   CONNECT BY LEVEL

3. Laurent Schneider Says:
   March 21st, 2007 at 4:44 am

   well, connect by without prior is not supported. But using connect by prior dbms_random.value is not null is not fair. First it may fail anytime, like the following query works 50% of the time

   select rownum from dual connect by level 
   
   (of course yours works 99.99% )
   
   what's more, it is such a hack that I would never rely on it. The optimizer may change the plan and it may suddenly fail in a next patchset. 
   
   Actually, I would prefer to have ORA-1436 in any connect by without prior, but we will have to wait 12gR2 to see it ;-)

4. Laurent Schneider Says:
   March 21st, 2007 at 4:46 am

   the < did not work, here again with &lt;

   select rownum from dual connect by level < 3 and prior trunc(dbms_random.value(1,3)) is not null;
   

5. stewstryker Says:
   January 3rd, 2008 at 1:25 pm

   Hmm, when I try Laurent’s version:

   select rownum from dual connect by level < 3
      and prior trunc(dbms_random.value(1,3)) is not null;

   I get the error:

   ORA-01436: CONNECT BY loop in user data

   Strangely, I can’t find that error in the manual… We’re running 10.2.0.3.0

   [sigh]

6. Laurent Schneider Says:
   January 3rd, 2008 at 1:57 pm

   try to run in more than once and you will see it works sometimes.

   about the error manual, I have reported that to the doc owner, good catch!

7. stewstryker Says:
   January 3rd, 2008 at 5:01 pm

   Thanks for the reply. I tried about 8 times using various interfaces and always got the same error. Oh well!

8. Laurent Schneider Says:
   March 5th, 2008 at 11:32 pm

   about the ORA-1436 please monitor enhancement request 6801807 on metalink

Leave a Reply

You must be logged in to post a comment.