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