At 12:56 PM 13/09/2012, you wrote:
>Hello I'm having a problems with a couple of columns, when I try to set a
>default value here is my code...
>
>
>alter table cboo_pedido
>add fec_uactu date default 'today' not null,
>add hor_uactu varchar(2) default extract(hour from cast('now' as date)) not
>null
>
>The first column (fec_uactu) goes OK, but on the second column I get this
>error:
> Invalid token.
> SQL error code = -104.
> Token unknown - line 3, char 34.
> extract.
>
>How can I add the second column with the required default?
Are you confusing a COMPUTED BY column with a defaulted column? The DEFAULT
attribute of a column can be a constant, a context variable or a predefined
date literal (like your column FEC_UACTU) but not an expression.
[FWIW, your expression for HOR_ACTU isn't valid, anyhow. You can't extract an
HOUR time-part from a DATE type. It seems bizarre to be trying to cast the
date literal 'NOW' as a date under any circumstances, since you have both
CURRENT_DATE and 'TODAY' available.]
One way to get this data on the records is to make HOR_ACTU nullable and use a
BEFORE INSERT OR UPDATE trigger to ensure you always get a default value when
HOR_ACTU is null:
create trigger some_name for cboo_pedido
active before insert or update as
begin
if (new.hor_actu is null) then
new.hor_actu = cast(extract (hour from current_timestamp) as varchar(2));
end
[I would also suggest reviewing the usefulness of storing HOR_ACTU as a
varchar. Do you really want '12' to be earlier than '2' when sorting by hour
of day?]
./heLen
