At 09:38 AM 23/09/2004 -0300, you wrote:

>Somewhere in a system I'm developing I have a query like this:
>
>SELECT C FROM T WHERE (X = :A) AND (Y = :B)
>
>where both X and Y a declared as VARCHAR(10), and :A and :B are instanciated
>with values extracted from String variables in the software. X is never
>NULL,
>but Y could be. I'm (still) using Firebird 1.0.3.
>
>My first problem was that this query never returned Cs of any register where
>Y was NULL. So, I realized that NULL is different of '' (the empty string),
>that was the value assigned to :B. Right?

Yes. NULL is a state, not a value. Any comparison of NULL with anything
else (including NULL) returns false.

>Then I rewrote the query as:
>
>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.

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