At 08:09 AM 07-08-02 +0000, you wrote:

>Hi
>I have a query that should return a code depending on a postalcode is
>between 2 values. If the first postalcode is Null then use the other
>postalcode one instead. The 2 postalcodes are in the same table.
>
>SELECT Code FROM Supro, Person
>WHERE "if person.Postal1 isNull then use Person.postal2"
>BETWEEN Supro.P1 AND Supro.P2
>AND person.personid = 6789
>
>This is what I would use in SQL server:
>SELECT Code FROM Supro, Person
>WHERE
> CASE
> WHEN person.Postal1 IS NULL THEN Person.postal2
> ELSE person.Postal1
> END
>BETWEEN Supro.P1 AND Supro.P2
>AND person.personid = 6789
>
>But since case doesn´t exist in Interbase I don´t know what to
>use.
>Any idea on how to achieve this?
>

The CASE construct is in beta for Firebird, but InterBase doesn't have it
at all.

Have you tried using UDFs? The FBUDF.dll library provides the equivalent
of Oracle's NVL, which is a CASE construct exactly for returning a value
when SQL encounters null. These are Firebird UDFs but they should work
with InterBase 6.

SELECT Code,
sNVL(Person.Postal1, Person.Postal2) as Postal /*computed outputs need names */
from Person, Supro
WHERE person.personid = 6789
AND
sNVL(Person.Postal1, Person.Postal2) is not null
/* NVL functions return NULL if both arguments are null */
AND
sNVL(Person.Postal1, Person.Postal2) BETWEEN Supro.P1 AND Supro.P2

Use iNVL if Postal1 and Postal2 are integers or exact numerics.
There is no equivalent function for floating point types.

heLen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
______________________________________________________________________