Re: Date range view

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 14 Nov 2008 21:59:21 +0100
Message-ID: <491DE6A9.7080207@gmail.com>


ddf schrieb:
> On Nov 14, 1:52 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

>> "Maxim Demenko" <mdeme..._at_gmail.com> a écrit dans le message de news: gfkhes$qcf$0..._at_news.t-online.com...
>> | Michel Cadot schrieb:
>> | > <dlela..._at_gmail.com> a écrit dans le message de news: 6690ecf1-7f0d-4ca0-bb14-f445eee2c..._at_l33g2000pri.googlegroups.com...
>> | > |I am currently using Oracle 9i and have created a table:
>> | > |
>> | > | CREATE TABLE FSR_TECH
>> | > | (
>> | > |  ID  VARCHAR2(20),
>> | > |  TECHNICIAN VARCHAR2(20),
>> | > |  FROM_DATE DATE,
>> | > |  THRU_DATE DATE
>> | > | )
>> | > |
>> | > | I have inserted the following data into it:
>> | > |
>> | > | INSERT INTO FSR_TECH VALUES('FSR0000001','JIM.POULSEN','30-
>> | > | SEP-2008','01-OCT-2008');
>> | > | INSERT INTO FSR_TECH VALUES('FSR0000002','DAVE.ROSE','13-OCT-2008','13-
>> | > | OCT-2008');
>> | > | INSERT INTO FSR_TECH VALUES('FSR0000002', 'LLOYD.MEHANEY', '13-
>> | > | OCT-2008', '13-OCT-2008');
>> | > | INSERT INTO FSR_TECH VALUES('FSR0000003', 'DAVE.ROSE', '06-OCT-2008',
>> | > | '13-OCT-2008');
>> | > | COMMIT;
>> | > |
>> | > | ID         TECHNICIAN           FROM_DATE  THRU_DATE
>> | > | ---------- -------------------- ---------- ----------
>> | > | FSR0000001 JIM.POULSEN          2008-09-30 2008-10-01
>> | > | FSR0000002 DAVE.ROSE            2008-10-13 2008-10-13
>> | > | FSR0000002 LLOYD.MEHANEY        2008-10-13 2008-10-13
>> | > | FSR0000003 DAVE.ROSE            2008-10-06 2008-10-13
>> | > |
>> | > | I would like to create a view over it by including all dates between
>> | > | the from and thru dates
>> | > | (inclusive) and have it presented as follows:
>> | > |
>> | > | TECHNICIAN    DATE
>> | > | ------------- ----------
>> | > | JIM.POULSEN   2008-09-30
>> | > | JIM.POULSEN   2008-10-01
>> | > | DAVE.ROSE     2008-10-13
>> | > | LLOYD.MEHANEY 2008-10-13
>> | > | DAVE.ROSE     2008-10-06
>> | > | DAVE.ROSE     2008-10-07
>> | > | DAVE.ROSE     2008-10-08
>> | > | DAVE.ROSE     2008-10-09
>> | > | DAVE.ROSE     2008-10-10
>> | > | DAVE.ROSE     2008-10-11
>> | > | DAVE.ROSE     2008-10-12
>> | > | DAVE.ROSE     2008-10-13
>> | > |
>> | > | I have tried numerous things (CONNECT BY, LEVEL, PARTITION) but just
>> | > | cannot come up with a way of doing it.  Any ideas?
>> | > |
>> | > | Dave
>> | >
>> | >
>> | > SQL> with
>> | >   2    minmax as (
>> | >   3      select min(from_date) mindate, max(thru_date) maxdate
>> | >   4      from fsr_tech
>> | >   5    ),
>> | >   6    cal as (
>> | >   7      select mindate+level-1 mydate
>> | >   8      from minmax
>> | >   9      connect by level <= maxdate-mindate+1
>> | >  10    )
>> | >  11  select id, technician, mydate
>> | >  12  from fsr_tech partition by (id, technician)
>> | >  13       left outer join cal
>> | >  14       on (mydate between from_date and thru_date)
>> | >  15  order by 1,3
>> | >  16  /
>> | > ID                   TECHNICIAN           MYDATE
>> | > -------------------- -------------------- -----------
>> | > FSR0000001           JIM.POULSEN          30-SEP-2008
>> | > FSR0000001           JIM.POULSEN          01-OCT-2008
>> | > FSR0000002           DAVE.ROSE            13-OCT-2008
>> | > FSR0000002           LLOYD.MEHANEY        13-OCT-2008
>> | > FSR0000003           DAVE.ROSE            06-OCT-2008
>> | > FSR0000003           DAVE.ROSE            07-OCT-2008
>> | > FSR0000003           DAVE.ROSE            08-OCT-2008
>> | > FSR0000003           DAVE.ROSE            09-OCT-2008
>> | > FSR0000003           DAVE.ROSE            10-OCT-2008
>> | > FSR0000003           DAVE.ROSE            11-OCT-2008
>> | > FSR0000003           DAVE.ROSE            12-OCT-2008
>> | > FSR0000003           DAVE.ROSE            13-OCT-2008
>> | >
>> | > 12 rows selected.
>> | >
>> | > Regards
>> | > Michel
>> | >
>> | >
>> |
>> | Michel, iirc , partition join doesn't work on 9i, but it can be safely
>> | left out in this case as well.
>> | Alternatively, one hierarchical query should be sufficient as well
>> |
>> | SQL> select id,technician,from_date + level - 1 mydate
>> |   2  from fsr_tech t
>> |   3  connect by prior id=id
>> |   4  and prior technician=technician
>> |   5  and prior from_date + level -1 <= thru_date
>> |   6  and prior dbms_random.value is not null
>> |   7  ;
>> |
>> | ID                   TECHNICIAN           MYDATE
>> | -------------------- -------------------- ----------
>> | FSR0000001           JIM.POULSEN          2008-09-30
>> | FSR0000001           JIM.POULSEN          2008-10-01
>> | FSR0000002           DAVE.ROSE            2008-10-13
>> | FSR0000002           LLOYD.MEHANEY        2008-10-13
>> | FSR0000003           DAVE.ROSE            2008-10-06
>> | FSR0000003           DAVE.ROSE            2008-10-07
>> | FSR0000003           DAVE.ROSE            2008-10-08
>> | FSR0000003           DAVE.ROSE            2008-10-09
>> | FSR0000003           DAVE.ROSE            2008-10-10
>> | FSR0000003           DAVE.ROSE            2008-10-11
>> | FSR0000003           DAVE.ROSE            2008-10-12
>> | FSR0000003           DAVE.ROSE            2008-10-13
>> |
>> | 12 rows selected.
>> |
>> |
>> | Best regards
>> |
>> | Maxim
>>
>> Yes you are right Maxim I didn't notice OP's version, just the fact
>> he mentioned he tested PARTITION.
>>
>> Note that if your query works in 9.2 it no more works in 10.2.0.4
>> with new connect by algorithm:
>> SQL> select id,technician,from_date + level - 1 mydate
>>   2  from fsr_tech t
>>   3  connect by prior id=id
>>   4    and prior technician=technician
>>   5    and prior from_date + level -1 <= thru_date
>>   6    and prior dbms_random.value is not null
>>   7  /
>> ERROR:
>> ORA-01436: CONNECT BY loop in user data
>>
>> Regards
>> Michel

>
> But it can, if _old_connect_by_enabled is set to true:
>
> SQL> CREATE TABLE FSR_TECH
> 2 (
> 3 ID VARCHAR2(20),
> 4 TECHNICIAN VARCHAR2(20),
> 5 FROM_DATE DATE,
> 6 THRU_DATE DATE
> 7 );
>
> Table created.
>
> SQL>
> SQL>
> SQL> INSERT INTO FSR_TECH VALUES('FSR0000001','JIM.POULSEN','30-
>>         SEP-2008','01-OCT-2008');

>
> 1 row created.
>
> SQL> INSERT INTO FSR_TECH VALUES('FSR0000002','DAVE.ROSE','13-
> OCT-2008','13-
>>         OCT-2008');

>
> 1 row created.
>
> SQL> INSERT INTO FSR_TECH VALUES('FSR0000002', 'LLOYD.MEHANEY', '13-
>>         OCT-2008', '13-OCT-2008');

>
> 1 row created.
>
> SQL> INSERT INTO FSR_TECH VALUES('FSR0000003', 'DAVE.ROSE', '06-
> OCT-2008',
> 2 '13-OCT-2008');
>
> 1 row created.
>
> SQL> COMMIT;
>
> Commit complete.
>
> SQL>
> SQL>
> SQL> with
> 2 minmax as (
> 3 select min(from_date) mindate, max(thru_date) maxdate
> 4 from fsr_tech
> 5 ),
> 6 cal as (
> 7 select mindate+level-1 mydate
> 8 from minmax
> 9 connect by level <= maxdate-mindate+1
> 10 )
> 11 select id, technician, mydate
> 12 from fsr_tech partition by (id, technician)
> 13 left outer join cal
> 14 on (mydate between from_date and thru_date)
> 15 order by 1,3
> 16 /
>
> ID TECHNICIAN MYDATE
> -------------------- -------------------- ---------
> FSR0000001 JIM.POULSEN 30-SEP-08
> FSR0000001 JIM.POULSEN 01-OCT-08
> FSR0000002 DAVE.ROSE 13-OCT-08
> FSR0000002 LLOYD.MEHANEY 13-OCT-08
> FSR0000003 DAVE.ROSE 06-OCT-08
> FSR0000003 DAVE.ROSE 07-OCT-08
> FSR0000003 DAVE.ROSE 08-OCT-08
> FSR0000003 DAVE.ROSE 09-OCT-08
> FSR0000003 DAVE.ROSE 10-OCT-08
> FSR0000003 DAVE.ROSE 11-OCT-08
> FSR0000003 DAVE.ROSE 12-OCT-08
> FSR0000003 DAVE.ROSE 13-OCT-08
>
> 12 rows selected.
>
> SQL>
> SQL> select id,technician,from_date + level - 1 mydate
> 2 from fsr_tech t
> 3 connect by prior id=id
> 4 and prior technician=technician
> 5 and prior from_date + level -1 <= thru_date
> 6 and prior dbms_random.value is not null
> 7 ;
> ERROR:
> ORA-01436: CONNECT BY loop in user data
>
>
>
> no rows selected
>
> SQL>
> SQL> alter session set "_old_connect_by_enabled" = true;
>
> Session altered.
>
> SQL>
> SQL> select id,technician,from_date + level - 1 mydate
> 2 from fsr_tech t
> 3 connect by prior id=id
> 4 and prior technician=technician
> 5 and prior from_date + level -1 <= thru_date
> 6 and prior dbms_random.value is not null
> 7 ;
>
> ID TECHNICIAN MYDATE
> -------------------- -------------------- ---------
> FSR0000001 JIM.POULSEN 30-SEP-08
> FSR0000001 JIM.POULSEN 01-OCT-08
> FSR0000002 DAVE.ROSE 13-OCT-08
> FSR0000002 LLOYD.MEHANEY 13-OCT-08
> FSR0000003 DAVE.ROSE 06-OCT-08
> FSR0000003 DAVE.ROSE 07-OCT-08
> FSR0000003 DAVE.ROSE 08-OCT-08
> FSR0000003 DAVE.ROSE 09-OCT-08
> FSR0000003 DAVE.ROSE 10-OCT-08
> FSR0000003 DAVE.ROSE 11-OCT-08
> FSR0000003 DAVE.ROSE 12-OCT-08
> FSR0000003 DAVE.ROSE 13-OCT-08
>
> 12 rows selected.
>
> SQL>
> SQL> alter session set "_old_connect_by_enabled" = false;
>
> Session altered.
>
> SQL>
>
> This in 11.1.0.6.
>
>
> David Fitzjarrell

Michel, David, thanks, both are valid points. Probably, from 10g onwards, the safe way could be to use model

SQL> select id,

   2         technician,
   3         from_date

   4 from fsr_tech t
   5 model
   6     partition by (id,technician)
   7     dimension by (0 dim)
   8     measures (thru_date - from_date -1 as diff,from_date)
   9     rules iterate (1000) until(iteration_number>diff[0]) (
  10        from_date[iteration_number]=from_date[0] + iteration_number
  11 )
  12 ;
ID                   TECHNICIAN           FROM_DATE
-------------------- -------------------- ----------
FSR0000001           JIM.POULSEN          30-09-2008
FSR0000001           JIM.POULSEN          01-10-2008
FSR0000002           DAVE.ROSE            13-10-2008
FSR0000002           LLOYD.MEHANEY        13-10-2008
FSR0000003           DAVE.ROSE            06-10-2008
FSR0000003           DAVE.ROSE            07-10-2008
FSR0000003           DAVE.ROSE            08-10-2008
FSR0000003           DAVE.ROSE            09-10-2008
FSR0000003           DAVE.ROSE            10-10-2008
FSR0000003           DAVE.ROSE            11-10-2008
FSR0000003           DAVE.ROSE            12-10-2008
FSR0000003           DAVE.ROSE            13-10-2008

12 rows selected.

To OP
You most likely got the main idea for such kind of queries - common way is to use theta join with any row source generator. Implications with connect by ( besides the provided already in this topic) you can read on Tanel Poder blog
http://blog.tanelpoder.com/2008/06/08/generating-lots-of-rows-using-connect-by-safely/

Common methods for row source generators can be looked on the Adrian Billington site
http://www.oracle-developer.net/display.php?id=408

Best regards

Maxim Received on Fri Nov 14 2008 - 14:59:21 CST

Original text of this message