Thanx Paul for explanation




________________________________
From: Paul Vinkenoog <paul@...>
To: firebird-support@yahoogroups.com
Sent: Tue, May 11, 2010 1:42:40 PM
Subject: Re: [firebird-support] Question about computed field and datediff function


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






[Non-text portions of this message have been removed]