Re: Creating temp table for dates

Ruslan Kogan wrote:

> Hi,
>
> We have a bit of a problem and maybe someone knows a good
> solution.....we are thinking of using a temp table in oracle.
>
> We currently List a set of Data for any given week in a report. Say
> for instance the date parameter passed to the report is "7/10/04" the
> Output should look as follows:
>
> Date Qty
> *****************************
> Mon 04/10/2004 2
> Tue 05/10/2004
> Wed 06/10/2004 3
> Thu 07/10/2004 7
> Fri 08/10/2004
> Sat 09/10/2004
> sun 10/10/2004
>
> We need an entry for every day of that week (starting mon) even for
> days that have no entries in the database.
>
> WE ARE NOT ALLOWED TO CREATE ANOTHER TABLE IN THE DATABASE
>
> We are contemplating using an Oracle stored procedure and generating a
> temp table that has all the dates for that week and then performing a
> RIGHT join with it on the data.
>
> I see people have said do not create a temp table.....is there a
> better solution for our problem???
>
> Thanks a lot for your help!!!

You are correct in your reading about not using temporary tables in Oracle.

There is no reason to have a table at all as this can easily be done in memory with an array. Create an array with 7 records and just fill in the values.

You can find some code that might get you started by going to: http://www.psoug.org
Click on Morgan's Library
Click on Associative Array

The demo there doesn't do what you want but it demos the techniques necessary for doing it.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)