Re: Create a view to return multiple rows based on the column values of a single row, How?

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/07/01
Message-ID: <8jkug9$3rj$1@nnrp1.deja.com>#1/1

In article <8jgtpl$9t3$1_at_nnrp1.deja.com>,   adamvt_at_my-deja.com wrote:
> OK! SQL Gurus, here is what I have:
>
> - Say I have a table, eg Period, whith two columns, eg
> First_Date_Of_Month (date) and Number_Of_Days (integer). For every
> month there is only on entry in it which are the date of the 1st day
 of
> the month and the number of days in that month. eg:
>
> First_Date_Of_Month Number_Of_Days
> 01-jan-2000 31
> 01-feb-2000 29
> 01-mar-2000 31
> ...
> ...
>
> And here is what I would like to get:
>
> - A view, eg Period_View, which will produce me the follwing result
> based on the row in the above table:
>
> SELECT day_of_the_month, date_of_the_day FROM Period_View WHERE
> First_Date_Of_Month = '01-feb-2000';
>
> day_of_the_month date_of_the_day
> 01 01-feb-2000
> 02 02-feb-2000
> .
> .
> 29 29-feb-2000
>
> Is this possible? If so how?
> Thanks for any help!
> Adam.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

sure, we just need a table with some number of rows in it... all_objects typically suffices. Here is an example:

ops$tkyte_at_8i> create table t ( first_date_of_month date, number_of_days int );

Table created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> insert into t values ( '01-jan-2000', 31 );

1 row created.

ops$tkyte_at_8i> insert into t values ( '01-feb-2000', 29 );

1 row created.

ops$tkyte_at_8i> insert into t values ( '01-mar-2000', 31 );

1 row created.

ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> select r, to_date('01-feb-2000')+r-1
  2    from ( select rownum r
  3             from all_objects
  4            where rownum <= ( select number_of_days
  5                                from t
  6                               where first_date_of_month = to_date
('01-feb-2000') )
  7         )

  8 /

         R TO_DATE('

---------- ---------
         1 01-FEB-00
         2 02-FEB-00
         3 03-FEB-00
         4 04-FEB-00
         5 05-FEB-00
         6 06-FEB-00
         7 07-FEB-00
         8 08-FEB-00
         9 09-FEB-00
        10 10-FEB-00
        11 11-FEB-00
        12 12-FEB-00
        13 13-FEB-00
        14 14-FEB-00
        15 15-FEB-00
        16 16-FEB-00
        17 17-FEB-00
        18 18-FEB-00
        19 19-FEB-00
        20 20-FEB-00
        21 21-FEB-00
        22 22-FEB-00
        23 23-FEB-00
        24 24-FEB-00
        25 25-FEB-00
        26 26-FEB-00
        27 27-FEB-00
        28 28-FEB-00
        29 29-FEB-00

29 rows selected.

I myself however would skip the error prone "number_of_days" column in your example and let the Oracle builtin date functions handle it. The only thing that could happen with your table is you put in the wrong number of days sometime and either get too few rows or an error due to trying to return "feb-29'th" on a non-leap year or something:

ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> select r, to_date('01-feb-2000')+r-1
  2    from ( select rownum r
  3             from all_objects
  4            where rownum <= to_number( to_char(last_day('01-feb-

2000'),'dd') ) )
  5 /

         R TO_DATE('

---------- ---------
         1 01-FEB-00
         2 02-FEB-00
         3 03-FEB-00
         4 04-FEB-00
         5 05-FEB-00
         6 06-FEB-00
         7 07-FEB-00
         8 08-FEB-00
         9 09-FEB-00
        10 10-FEB-00
        11 11-FEB-00
        12 12-FEB-00
        13 13-FEB-00
        14 14-FEB-00
        15 15-FEB-00
        16 16-FEB-00
        17 17-FEB-00
        18 18-FEB-00
        19 19-FEB-00
        20 20-FEB-00
        21 21-FEB-00
        22 22-FEB-00
        23 23-FEB-00
        24 24-FEB-00
        25 25-FEB-00
        26 26-FEB-00
        27 27-FEB-00
        28 28-FEB-00
        29 29-FEB-00

29 rows selected.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Sat Jul 01 2000 - 00:00:00 CDT