Temp Calendar Table
How can I build a temporary table that contains a record for each date of the month for december? I already have the first and last day of the month stored in variables. I'm using SPL for Informix 7.3 Thanks __________________________________________________ This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank You. ___________________________________________________
There are a couple of ways. If you are in a stored procedure, try this:
define min date;
define max date;
define x date;
create temp table tmp_date (date_col date not null) with no log;
let x=min;
while x <=max
insert into tmp_date (x); let x=x+1;
end while;
Another approach we use is to have a permanent table with a single integer
column
called col1 (we call our table cc_100_row). Then insert 100 rows with
values
incrementing from 1 to 100. This allows temp table to be created as you
mentioned
without looping:
create temp table tmp_date (date_col date not null) with no log;
insert into tmp_date
select :min + (col1 - 1)
from cc_100_row
where col1 between 1 and 31;
Good luck!
Jay Buckler
<nanson@paulweiss.com> wrote in message
news:83lmhc$sj9$1@news.xmission.com...
>
> How can I build a temporary table that contains a record for each date of
the
> month for december? I already have the first and last day of the month
stored in
> variables. I'm using SPL for Informix 7.3
>
>
> Thanks
>
>
>
>
> __________________________________________________
>
> This message is intended only for the use of the Addressee and may
> contain information that is PRIVILEGED and CONFIDENTIAL.
>
> If you are not the intended recipient, you are hereby notified that any
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please erase all copies of the
> message and its attachments and notify us immediately.
>
> Thank You.
> ___________________________________________________
>
>
>