Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help with sql-query

Re: Help with sql-query

From: Maija-Leena <kangasmaki_at_jippii.fi>
Date: Thu, 21 Jun 2007 12:18:09 GMT
Message-ID: <50uei.110$e95.74@read3.inet.fi>


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



> | Id | Operation | Name | Starts | E-Rows | A-
> Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
> --------------------------------------------------------------------------


> | 1 | SORT ORDER BY | | 1 | 1
> | 8 |00:00:00.03 | 7000 | 2048 | 2048 | 2048 (0)|
> | 2 | NESTED LOOPS | | 1 | 1
> | 8 |00:00:00.03 | 7000 | | | |
> | 3 | VIEW | | 1 | 1 |
> 1000 |00:00:00.01 | 0 | | | |
> | 4 | COUNT | | 1 | |
> 1000 |00:00:00.01 | 0 | | | |
> | 5 | CONNECT BY WITHOUT FILTERING| | 1 | |
> 1000 |00:00:00.01 | 0 | | | |
> | 6 | FAST DUAL | | 1 | 1
> | 1 |00:00:00.01 | 0 | | | |
> |* 7 | TABLE ACCESS FULL | T1 | 1000 | 1
> | 8 |00:00:00.02 | 7000 | | | |
> --------------------------------------------------------------------------


>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 7 - filter("RC"."RN"<="T1"."C3"-"T1"."C2")
>
> Note
> -----
> - dynamic sampling used for this statement
> --------------------------------------------------------------------------
>
> It would be more efficient to create a table with a simple counter,
> and use that rather than the DUAL table:
> CREATE TABLE T2 (
> RN NUMBER(10),
> PRIMARY KEY(RN));
>
> INSERT INTO
> T2
> SELECT
> ROWNUM-1 RN
> FROM
> DUAL
> CONNECT BY
> LEVEL<=1000;
>
> COMMIT;
>
> The SQL statement then becomes:
> SELECT
> T1.C1,
> T1.C2,
> T1.C3,
> T1.C2+RC.RN ND
> FROM
> T1,
> T2 RC
> WHERE
> (T1.C3-T1.C2)>=RC.RN
> ORDER BY
> C1,
> 4;
>
> The plan changes to:
> --------------------------------------------------------------------------
> --------------------------------------------------------------------------


> | Id | Operation | Name | Starts | E-Rows | A-Rows
> | A-Time | Buffers | OMem | 1Mem | Used-Mem |
> --------------------------------------------------------------------------


> | 1 | SORT ORDER BY | | 1 | 100 | 8 |
> 00:00:00.01 | 11 | 2048 | 2048 | 2048 (0)|
> | 2 | NESTED LOOPS | | 1 | 100 | 8 |
> 00:00:00.01 | 11 | | | |
> | 3 | TABLE ACCESS FULL| T1 | 1 | 2 | 2 |
> 00:00:00.01 | 7 | | | |
> |* 4 | INDEX RANGE SCAN | SYS_C0036005 | 2 | 50 | 8 |
> 00:00:00.01 | 4 | | | |
> --------------------------------------------------------------------------


>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
> 4 - access("RC"."RN"<="T1"."C3"-"T1"."C2")
>
> Note
> -----
> - dynamic sampling used for this statement
> --------------------------------------------------------------------------
>
> It _might_ be more efficient to do the same with the T2 table, but
> without using the primary key index (use ROWNUM, rather than the value
> of the RN column).
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
>
Received on Thu Jun 21 2007 - 07:18:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US