dbTalk Databases Forums  

Date range view

Go Back dbTalk Databases Forums Databases comp.databases.oracle.misc Date range view

comp.databases.oracle.misc comp.databases.oracle.misc


Discuss Date range view in the comp.databases.oracle.misc forum.



Reply
Page 4 of 4 < 1 2 3 4
 
Thread Tools Display Modes
  #31  
Old   
Maxim Demenko
 
Posts: n/a

Default Re: Date range view - 11-14-2008 , 03:59 PM





ddf schrieb:
Quote:
On Nov 14, 1:52 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
"Maxim Demenko" <mdeme... (AT) gmail (DOT) com> a écrit dans le message de news: gfkhes$qcf$0... (AT) news (DOT) t-online.com...
| Michel Cadot schrieb:
| > <dlela... (AT) gmail (DOT) com> a écrit dans le message de news: 6690ecf1-7f0d-4ca0-bb14-f445eee2c... (AT) l33g2000pri (DOT) 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/0...ect-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


Reply With Quote
  #32  
Old   
Maxim Demenko
 
Posts: n/a

Default Re: Date range view - 11-14-2008 , 03:59 PM





ddf schrieb:
Quote:
On Nov 14, 1:52 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
"Maxim Demenko" <mdeme... (AT) gmail (DOT) com> a écrit dans le message de news: gfkhes$qcf$0... (AT) news (DOT) t-online.com...
| Michel Cadot schrieb:
| > <dlela... (AT) gmail (DOT) com> a écrit dans le message de news: 6690ecf1-7f0d-4ca0-bb14-f445eee2c... (AT) l33g2000pri (DOT) 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/0...ect-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


Reply With Quote
  #33  
Old   
Maxim Demenko
 
Posts: n/a

Default Re: Date range view - 11-14-2008 , 03:59 PM


ddf schrieb:
Quote:
On Nov 14, 1:52 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
"Maxim Demenko" <mdeme... (AT) gmail (DOT) com> a écrit dans le message de news: gfkhes$qcf$0... (AT) news (DOT) t-online.com...
| Michel Cadot schrieb:
| > <dlela... (AT) gmail (DOT) com> a écrit dans le message de news: 6690ecf1-7f0d-4ca0-bb14-f445eee2c... (AT) l33g2000pri (DOT) 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/0...ect-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


Reply With Quote
Reply
Page 4 of 4 < 1 2 3 4

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top