a how can I do this in Informix question
Hello,
I have a problem in Informix that I need to solve.
I have table
Table ABC
{
Field1 integer,
Field2 char(5),
Insdate integer
}
What I want now is when an insert occurs on this table on the fields
(Field1 and Field2) that afterwards the fields Insdate is filled with
the date of today but stored as a number in the format YYYYMMDD.
Example:
Insert into ABC (Field1, Field2)
values (1, "Wim");
then should the query
Select * from ABC;
return:
Field1 Field2 Insdate
1 Wim 20051027
My collegue found how to do it in Oracle and now I want to prove it also
can be done in Informix. I hope. Else me and my big mouth :-)
So help me please.
Thanks,
Wim
The way to do this best in Informix is to redfine insdate as a DATE
datatype set a default for the column = TODAY
Set DBDATE = Y4MD
Quetzalcoatl wrote:
> Hello,
>
> I have a problem in Informix that I need to solve.
>
> I have table
>
> Table ABC
> {
> Field1 integer,
> Field2 char(5),
> Insdate integer
> }
>
> What I want now is when an insert occurs on this table on the fields
> (Field1 and Field2) that afterwards the fields Insdate is filled with
> the date of today but stored as a number in the format YYYYMMDD.
>
> Example:
>
> Insert into ABC (Field1, Field2)
> values (1, "Wim");>
> then should the query
>
> Select * from ABC;>
> return:
>
> Field1 Field2 Insdate
> 1 Wim 20051027
>
> My collegue found how to do it in Oracle and now I want to prove it also
> can be done in Informix. I hope. Else me and my big mouth :-)
>
> So help me please.
>
> Thanks,
>
> Wim
Quetzalcoatl wrote:
> Hello,
>
> I have a problem in Informix that I need to solve.
>
> I have table
>
> Table ABC
> {
> Field1 integer,
> Field2 char(5),
> Insdate integer
> };
> What I want now is when an insert occurs on this table on the fields
> (Field1 and Field2) that afterwards the fields Insdate is filled with
> the date of today but stored as a number in the format YYYYMMDD.
How about this as just ONE way to do it:
create procedure create_abc_default_date() returning int
define result int;
select year(current) * 10000 + month(current) * 100 + day(current)
into result
from systables
where tabid = 1;
return result;
end procedure;
create trigger abc_ins for insert on abc
referencing new as rec
for each row
update abc set insdate = create_abc_default_date()
where field1 = rec.field1 and field2 = rec.field2;
Art S. Kagel
> Example:
>
> Insert into ABC (Field1, Field2)
> values (1, "Wim");>
> then should the query
>
> Select * from ABC;>
> return:
>
> Field1 Field2 Insdate
> 1 Wim 20051027
>
> My collegue found how to do it in Oracle and now I want to prove it also
> can be done in Informix. I hope. Else me and my big mouth :-)
>
> So help me please.
>
> Thanks,
>
> Wim