Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Creating temp table for dates
> 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
-- Rene Nyffenegger http://www.adp-gmbh.ch/Received on Wed Oct 06 2004 - 18:23:49 CDT