Rene Nyffenegger 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???
>
>
> Don't know if you consider this approach better, but try:
>
> alter session set nls_date_format = 'dd/mm/yyyy';
>
> select start_day+offset from
> (select trunc(to_date('07/10/2004'), 'D') start_day
> from dual)
> cross join
> (select rownum offset from all_objects
> where rownum < 8);
>
>
> hth
> Rene
>
On Oracle Database 10g, you could use the new partitioned outer join
feature to fill the gaps in sparse values - see the OBE at
http://www.oracle.com/technology/obe/obe10gdb/bidw/outerjoin/outerjoin.htm
Received on Wed Oct 06 2004 - 21:09:30 CDT
