Thanx for all the good advice, the answer from Helen Borrie did it!

My excuse for guessing for the sql syntax, i am a veterinarian, so
not a developper or what so ever, but willing to work with firebird

...

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...>
wrote:

>
> At 10:12 AM 9/12/2008, you wrote:
>
> >Hi folks,
> >
> >I am converting a database for the moment from mysql to firebird

2.0,

> >encountering following problem with subquery in case statement,
> >
> >case
> >
> >when
> >
> >select TABLE_VACCPROT.Vacc_1_EH from TABLE_VACC, TABLE_VACCPROT

where

> >(TABLE_VACCPROT.ProtocolID = TABLE_VACC.VaccProtocol = 'd'
> >
> > then
> >
> >DATEADD(
> >
> > day,
> >
> > (select VACC_1_INT from TABLE_VACCPROT where
> >TABLE_VACCPROT.ProtocolID = TABLE_VACC.VACCPROTOCOL),
> >
> > (select birthday from TABLE_COWS where
> >TABLE_COWS.cowregnumber = TABLE_VACC.CowRegNumber )
> >
> > )
> >
> >End
> >
> >Error: invalid token...
> >
> >i read that the subquery has to result in a boolean, right/wrong?
> >
> >Suggestions?
>
> Yup. Don't try to guess at the syntax of SQL - use the relevant

language guide (refer to the release notes and/or the Documentation
Index at the Firebird website).

>
> Try this for the above query:
>
> case
> when
> ( select p.Vacc_1_EH from TABLE_VACCPROT p
> join TABLE_VACC v on v.VaccProtocol = p.ProtocolID ) = 'd'
> then
>
> DATEADD( day,
> (select p1.VACC_1_INT from TABLE_VACCPROT p1
> where p1.ProtocolID = v.VACCPROTOCOL),
> (select birthday from TABLE_COWS c
> where c.cowregnumber = v.CowRegNumber ) )
>
> else
> CAST(some_timestamp as DATE) /* or whatever you want for

Else */

> End
> as NextVacc ;
>
> For the query that you asked about yesterday:
>
> select
> coalesce ( Cast (v.Vacc_1_D as DATE),
> DATEADD (DAY,
> (select p.VACC_1_INT from

TABLE_VACCPROT p

> where p.ProtocolID =

v.VACCPROTOCOL),

> (select c.birthday from

TABLE_COWS c

> where c.cowregnumber =

v.CowRegNumber ) )

> as NextVacc
> from TABLE_VACC v;
>
> Please, don't send private emails to list members unless invited.
>
> ./heLen
>