Re: Date range view

From: Leland <dleland3_at_gmail.com>
Date: Fri, 14 Nov 2008 11:05:59 -0800 (PST)
Message-ID: <cc1e18ae-f725-40eb-a714-dc4499a7ba1c@o4g2000pra.googlegroups.com>

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

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

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 Received on Fri Nov 14 2008 - 13:05:59 CST