> >SELECT C FROM T WHERE (X = :A) AND (((Y IS NULL) AND (:B = '')) OR (Y =

:B))

> >
> >What a kludge! But if it works... he, of course it didn't. Whenever :B

was

> >not empty the query failed with an error: "arithmetic exception, numeric
> >overflow, or string truncation."
>
> Empty string is not the same as NULL. And a predicate that is passed as
>
> where aValue = NULL
>
> is not syntactically valid.
>

Excuse me, but I didn't understand your answer. By rewriting the query, I
was trying to avoid exactly that construct. But... perhaps you are referring
to the case where Y is NULL but :B is not empty, so in that case I will
again be comparing :B to NULL, when the first part of the or subexpression
fails. Lets see... I rewrote the query as:

SELECT C FROM T WHERE (X = :A) AND (((Y IS NULL) AND (:B = '')) OR ((NOT Y
IS NULL) AND (Y = :B)))

But, this one also fires the "arithmetic exception, numeric overflow, or
string truncation" error, in the same cases as the previous one (when :B is
not empty).

> So, assuming that you want to treat nulls in Y as though they were empty
> strings, alter your query to this:
>
> SELECT C FROM T
> WHERE X = :A
> AND COALESCE(Y, '') = :B
>

> ./heLen
>

Mmm, I guess that's a new function in FB 1.5 (I'm still using FB 1.0.3). I
get a "Funcion unknown COALESCE" error when trying to execute the query.

EB.