Home -> Usenet -> c.d.o.misc -> Re: Date range view

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