Re: Date range view
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_number11 )
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