Subject Re: [ib-support] change condition i WHERE clause
Author Helen Borrie
At 08:09 AM 07-08-02 +0000, you wrote:
>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
> WHEN person.Postal1 IS NULL THEN Person.postal2
> ELSE person.Postal1
>BETWEEN Supro.P1 AND Supro.P2
>AND person.personid = 6789
>But since case doesn´t exist in Interbase I don´t know what to
>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.

sNVL(Person.Postal1, Person.Postal2) as Postal /*computed outputs need names */
from Person, Supro
WHERE person.personid = 6789
sNVL(Person.Postal1, Person.Postal2) is not null
/* NVL functions return NULL if both arguments are null */
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.


All for Open and Open for All
Firebird Open SQL Database · ·