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?

COALESCE or ISNULL was even easier to use in this case in MSSQL ;-)

You could also use an union (untested):

SELECT
Code
FROM
Supro,
Person
WHERE
person.Postal1 BETWEEN Supro.P1 AND Supro.P2
AND person.personid = 6789
AND person.Postal1 IS NOT NULL
UNION

SELECT
Code
FROM
Supro,
Person
WHERE
person.Postal2 BETWEEN Supro.P1 AND Supro.P2
AND person.personid = 6789
AND person.Postal1 IS NULL

Regards,
Arno