| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Help with sql-query
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
(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
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
The plan changes to:
| 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
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 - 06:18:49 CDT
 |
 |