Date range view

#11

On Nov 14, 11:56*am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

Quote:

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:

When I run it, I receive the following error:
ERROR at line 12:
ORA-00933: SQL command not properly ended

I'm running Oracle 9i. Any ideas?

Dave

#12

On Nov 14, 11:56*am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

Quote:

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:

When I run it, I receive the following error:
ERROR at line 12:
ORA-00933: SQL command not properly ended

I'm running Oracle 9i. Any ideas?

Dave

#13

On Nov 14, 11:56*am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

Quote:

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:

When I run it, I receive the following error:
ERROR at line 12:
ORA-00933: SQL command not properly ended

I'm running Oracle 9i. Any ideas?

Dave

#14

"Maxim Demenko" <mdemenko (AT) gmail (DOT) com> a écrit dans le message de news: gfkhes$qcf$03$1 (AT) news (DOT) t-online.com...

Quote:

Michel Cadot schrieb:
dleland3 (AT) gmail (DOT) com> a écrit dans le message de news: 6690ecf1-7f0d-4ca0-bb14-f445eee2cb82...oglegroups.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

#15

"Maxim Demenko" <mdemenko (AT) gmail (DOT) com> a écrit dans le message de news: gfkhes$qcf$03$1 (AT) news (DOT) t-online.com...

Quote:

Michel Cadot schrieb:
dleland3 (AT) gmail (DOT) com> a écrit dans le message de news: 6690ecf1-7f0d-4ca0-bb14-f445eee2cb82...oglegroups.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

#16

"Maxim Demenko" <mdemenko (AT) gmail (DOT) com> a écrit dans le message de news: gfkhes$qcf$03$1 (AT) news (DOT) t-online.com...

Quote:

Michel Cadot schrieb:
dleland3 (AT) gmail (DOT) com> a écrit dans le message de news: 6690ecf1-7f0d-4ca0-bb14-f445eee2cb82...oglegroups.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

#17

"Maxim Demenko" <mdemenko (AT) gmail (DOT) com> a écrit dans le message de news: gfkhes$qcf$03$1 (AT) news (DOT) t-online.com...

Quote:

Michel Cadot schrieb:
dleland3 (AT) gmail (DOT) com> a écrit dans le message de news: 6690ecf1-7f0d-4ca0-bb14-f445eee2cb82...oglegroups.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

#18

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

Quote:

"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-

Quote:

SEP-2008','01-OCT-2008');

1 row created.

SQL> INSERT INTO FSR_TECH VALUES('FSR0000002','DAVE.ROSE','13-
OCT-2008','13-

Quote:

OCT-2008');

1 row created.

SQL> INSERT INTO FSR_TECH VALUES('FSR0000002', 'LLOYD.MEHANEY', '13-

Quote:

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

#19

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

Quote:

"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-

Quote:

SEP-2008','01-OCT-2008');

1 row created.

SQL> INSERT INTO FSR_TECH VALUES('FSR0000002','DAVE.ROSE','13-
OCT-2008','13-

Quote:

OCT-2008');

1 row created.

SQL> INSERT INTO FSR_TECH VALUES('FSR0000002', 'LLOYD.MEHANEY', '13-

Quote:

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

#20

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

Quote:

"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-

Quote:

SEP-2008','01-OCT-2008');

1 row created.

SQL> INSERT INTO FSR_TECH VALUES('FSR0000002','DAVE.ROSE','13-
OCT-2008','13-

Quote:

OCT-2008');

1 row created.

SQL> INSERT INTO FSR_TECH VALUES('FSR0000002', 'LLOYD.MEHANEY', '13-

Quote:

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