Hello Supertokkie,

> 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.

What do you mean by that? Interbase distinguishes between upper- and
lowercase characters.

> 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

This proc returns 'NO' if multipack is 'y'. Is that intentional?

> 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?

You don't specify what kind of error you get, but you're calling your
stored procedure here like it was a function. You can't do that; you
have to execute it like this:

execute procedure ConvertMultipack A.Multipack returning values :resultvar

...where resultvar is a variable (e.g. in another SP) or a field name.


I'm also puzzled by the WHERE clause as such.

First, you don't need to use UPPER() because ConvertMultipack returns
all-caps anyhow.

Second, since ConvertMultipack only tests against 'Y' you could change
the clause to a simple

WHERE A.MULTIPACK = 'Y'

or if you want do do it case-insensitively

WHERE UPPER( A.MULTIPACK ) = 'Y'


Greetings,
Paul Vinkenoog