scabaj wrote:

> I'd like to create a computed field, something like that
>
> CREATE TABLE TABLE1 (
> BORN DATE,
> AGE COMPUTED BY (DATEDIFF(YEAR FROM DATE BORN TO CURRENT_DATE))
> );
>
> or perhaps (?) better like this
>
> CREATE TABLE TABLE1 (
> BORN DATE,
> AGE COMPUTED BY ( CASE WHEN (BORN IS NULL) THEN 0 ELSE (DATEDIFF(YEAR FROM DATE BORN TO CURRENT_DATE)) END)
> );
>
> I get an error 'Invalid token... BORN'
> so the question is: is it possible in such situation to use a field name in datediff function ?

Yes, what's wrong is the use of DATE here. Try

... DATEDIFF(YEAR FROM BORN TO CURRENT_DATE) ...

The 'DATE' shortcut cast is
a) unnecessary here (since BORN is a DATE field)
b) only allowed before a string constant

The reason that the parser doesn't immediately choke on DATE is that at
that point, it might still be possible that a string constant follows.
When it encounters BORN (a field name), the syntax becomes invalid and
an error is raised.


HTH,
Paul Vinkenoog