Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Help with sql-query
On 21 jun, 11:47, "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
A little late, but here are two solutions that I think are less ugly:
Prior to 10g:
SQL> select a
2 , b + level - 1 3 from maikku 4 connect by level <= c - b + 1 5 and prior a = a 6 and prior dbms_random.value is not null7 /
A B+LEVEL-1
----- ------------------- 1 25-06-2007 00:00:00 1 26-06-2007 00:00:00 1 27-06-2007 00:00:00 1 28-06-2007 00:00:00 1 29-06-2007 00:00:00 1 30-06-2007 00:00:00
8 rijen zijn geselecteerd.
For 10g:
SQL> select a
2 , b
3 from maikku
4 model
5 partition by (a) 6 dimension by (0 i) 7 measures (b, c) 8 rules 9 ( b[for i from 1 to c[0]-b[0] increment 1] = b[0] + cv(i) 10 )
A B
----- ------------------- 1 25-06-2007 00:00:00 1 26-06-2007 00:00:00 1 27-06-2007 00:00:00 1 28-06-2007 00:00:00 1 29-06-2007 00:00:00 1 30-06-2007 00:00:00
8 rijen zijn geselecteerd.
Regards,
Rob.
Received on Mon Jun 25 2007 - 07:09:29 CDT