Here's my try. Note that I am lousy at sql, so I expect the gurus to
point out any logic flaws (for my benefit too).

SELECT Code FROM Supro, Person
WHERE
person.personid = 6789
and
(
person.Posta1 is not null
and
person.Postal1 BETWEEN Supro.P1 AND Supro.P2
)
or
(
person.Postal1 is null
and
person.Postal2 BETWEEN Supro.P1 AND Supro.P2
)


Regards,
Andrew Ferguson
-- Open 7 days a week and weekends.



--- In ib-support@y..., "jennysigvardsdotter"
<jennysigvardsdotter@y...> 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?
>
> Thanks
> //Jenny