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>


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

Original text of this message