Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Help with sql-query
Wow, that is really complex but it works !!!! Thanks !!!
Regards, Maija-Leena
"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message
news:1182424729.231652.33710_at_k79g2000hse.googlegroups.com...
> On Jun 21, 5:47 am, "Maija-Leena" <kangasm..._at_jippii.fi> wrote:
> > Hi,
> >
> > I have rows like this (columns a b c):
> >
> > 1 20.06.2007 24.06.2007
> > 2 21.06.2007 23.06.2007
> >
> > and I need a SELECT-query that returns:
> >
> > 1 20.06.2007
> > 1 21.06.2007
> > 1 22.06.2007
> > 1 23.06.2007
> > 1 24.06.2007
> > 2 21.06.2007
> > 2 22.06.2007
> > 2 23.06.2007
> >
> > I managed to do this for rows 1 or 2 separately, but not all rows in the
> > same select. My query is:
> > CREATE TABLE MAIKKU (A NUMBER(1),B DATE,C DATE);
> >
> > INSERT INTO MAIKKU VALUES (1, TRUNC(SYSDATE), TRUNC(SYSDATE+5));
> >
> > INSERT INTO MAIKKU VALUES (2, TRUNC(SYSDATE+1), TRUNC(SYSDATE+2));
> >
> > SELECT * FROM maikku;
> >
> > SELECT a, b,c, b-1+ROWNUM FROM maikku WHERE a=1 CONNECT BY ROWNUM <=
c-b+1;
> >
> > Any ideas ?
> >
> > Thanks in advance !
> >
> > Maija-Leena
>
> Here is a quick run through (note: I renamed your table and column
> names)
> CREATE TABLE T1 (
> C1 NUMBER(10),
> C2 DATE,
> C3 DATE);
>
> INSERT INTO T1 VALUES(1, TRUNC(SYSDATE), TRUNC(SYSDATE+5));
> INSERT INTO T1 VALUES(2, TRUNC(SYSDATE+1), TRUNC(SYSDATE+2));
>
> COMMIT;
>
> In this case, we need a counter in a table/view to help us derive all
> of the dates between the start date and the end date, so we build the
> counter in an inline view:
> SELECT
> T1.C1,
> T1.C2,
> T1.C3,
> T1.C2+RC.RN ND
> FROM
> T1,
> (SELECT
> ROWNUM-1 RN
> FROM
> DUAL
> CONNECT BY
> LEVEL<=1000) RC
> WHERE
> (T1.C3-T1.C2)>=RC.RN
> ORDER BY
> C1,
> 4;
>
> (Note that the above only handles a date range of 1000 days)
> C1 C2 C3 ND
> ---------- --------- --------- ---------
> 1 21-JUN-07 26-JUN-07 21-JUN-07
> 1 21-JUN-07 26-JUN-07 22-JUN-07
> 1 21-JUN-07 26-JUN-07 23-JUN-07
> 1 21-JUN-07 26-JUN-07 24-JUN-07
> 1 21-JUN-07 26-JUN-07 25-JUN-07
> 1 21-JUN-07 26-JUN-07 26-JUN-07
> 2 22-JUN-07 23-JUN-07 22-JUN-07
> 2 22-JUN-07 23-JUN-07 23-JUN-07
>
> The above will be a bit inefficent with a large number of rows in the
> T1 table. This is the plan on Oracle 10.2.0.2:
> --------------------------------------------------------------------------
> --------------------------------------------------------------------------