Re: Date range view
From: ddf <oratune_at_msn.com>
Date: Fri, 14 Nov 2008 12:05:23 -0800 (PST)
Message-ID: <412cd2d7-4183-44b0-b4ba-f03b4396b8c8@v22g2000pro.googlegroups.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
7 );
11 select id, technician, mydate
12 from fsr_tech partition by (id, technician)
16 /
Date: Fri, 14 Nov 2008 12:05:23 -0800 (PST)
Message-ID: <412cd2d7-4183-44b0-b4ba-f03b4396b8c8@v22g2000pro.googlegroups.com>
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 Received on Fri Nov 14 2008 - 14:05:23 CST