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: <rwijk72_at_gmail.com>
Date: Mon, 25 Jun 2007 12:09:29 -0000
Message-ID: <1182773369.315756.257420@n2g2000hse.googlegroups.com>


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 null
  7 /

    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

    2 26-06-2007 00:00:00
    2 27-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         )

 11 /

    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

    2 26-06-2007 00:00:00
    2 27-06-2007 00:00:00

8 rijen zijn geselecteerd.

Regards,
Rob. Received on Mon Jun 25 2007 - 07:09:29 CDT

Original text of this message

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