Hi Martin,

> I want to convert a field in a select query that has the values Y or
> N to Yes and No.
>
> Unfortunately Interbase does not support case.
>
> That's why I have made a function:
>
> CREATE PROCEDURE CONVERTMULTIPACK (
> MULTIPACK VARCHAR(1) CHARACTER SET NONE)
> RETURNS (
> MULTIPACKYESNO VARCHAR(3) CHARACTER SET NONE)
> AS
> begin
> IF (multipack = 'Y') then
> multipackyesno = 'YES';
> ELSE
> multipackyesno = 'NO';
> end
>
>
> But when I apply this procedure in the next select query, I get an
> error:
>
> SELECT A.*, AV.VERPAKKINGOMSCHRIJVING, AE.EENHEIDOMSCHRIJVING,
> AM.MULTIPACKNAAM, AM.MULTIPACKINHOUD, AN.ARTIKELNUMMER
> FROM ARTIKELEN A
> LEFT OUTER JOIN ARTIKELVERPAKKING AV ON A.ARTIKELVERPAKKINGID
> = AV.VERPAKKINGID
> LEFT OUTER JOIN ARTIKELEENHEID AE ON A.ARTIKELEENHEIDID =
> AE.EENHEIDID
> LEFT OUTER JOIN ARTIKELMULTIPACK AM ON A.MULTIPACKID =
> AM.MULTIPACKID
> LEFT OUTER JOIN ARTIKELNUMMERS AN ON A.ARTIKELID =
> AN.ARTIKELNUMMERID
> WHERE upper(convertmultipack(A.multipack)) LIKE '%Y%';
>
> Does anybody know how I should solve this problem?

What makes you think you can use "convertmultipack" like a function in a
WHERE clause?
That's no where in the documentation. These are "stored procedures", not
"stored functions".

btw, for this particular case, why not do:

WHERE A.multipack = 'Y' ?

With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase & Firebird
Firebird Workbench - the developer tool for Firebird
Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."