At 12:03 AM 4/06/2004 +0100, you wrote:

>This is a current working SP which I have tried to convert to a CASE
>version as a test, with no success. I think its more elegant than the
>elseif constructs.
>System is W2K, Firebird 1.5 Release Candidate 7, database dialect 1,
>IBExpert 2004.04.01
>
>The CASE statement itself works in an Sql editor window as per:-
>select
>weigh_code,
>tonnes,
>case when (tonnes<20) then 'light' when (tonnes>20) then 'heavy' end
>from weigh
>345 19.78 light
>378 22.56 heavy
>...
>The error I get is this, no matter how I change the flow:-
>
>Invalid token.
>Dynamic SQL Error.
>SQL error code = -104.
>Token unknown - line 20, char 3.
>case.
>
>
>
>CREATE PROCEDURE MAINTAIN_PRODUCTS (
> PRODCODE INTEGER,
> PRODNAME CHAR(30) CHARACTER SET NONE,
> ZEROASH CHAR(1) CHARACTER SET NONE,
> ZEROWATER CHAR(1) CHARACTER SET NONE,
> ACTIONTYPE INTEGER)
>AS
>begin
> if (ActionType=1) then /* Update */
> begin
> update Product
> set Prod_Name=:ProdName,Zero_Ash=:ZeroAsh,Zero_Water=:ZeroWater
> where Prod_Code=:ProdCode;
> end
> else if (ActionType=2) then /* Insert */
> begin
> insert into Product(Prod_Code,Prod_Name,Zero_Ash,Zero_Water)
> values (:ProdCode,:ProdName,:ZeroAsh,:ZeroWater);
> end
> else if (ActionType=3) then /* Delete */
> begin
> delete from Product
> where Prod_Code=:ProdCode;
> end
> when SQLCode -803 Do
> Exception insertException;/* Already On File */
>end
>
>Any comments anybody? Should CASE statements work inside the FB stored procs?

In DSQL statements, yes, CASE expressions work just as usual.

But the PSQL language extensions do not support a CASE construct (which is
something different altogether).

Sorry to disappoint...

/heLen