Hi,

I m trying to use coalesce but i m getting this error below, btw i m using db2 8.2.
what i m doing is;
...
where eh.toblm = coalesce(NULL,eh.toblm)

SQL0206N "NULL " is not valid in the context where it is used.

Actually what i want to do is, get the result of this select statement either @id is NOT NULL or NULL! if @id is NULL then all results should be displayed!
...
where eh.toblm = coalesce(@id,eh.toblm)

I suggest you post the entire query along with the table definition. And what is "@id"?

The keyword NULL can only be used in a context where its data type is known.

So, it's e.g. valid to use it with INSERT, as inYou may use it in any other context where a constant value is valid, but in that case only after an explicit type cast, e.g.or(or of course alternatively with the "old" casting methods INT(NULL) or CHAR(NULL,20)).

Your condition makes sense, and will indeed be a "no-op", i.e., match everything, when expr has the NULL value. But it should be of a datatype which is compatible with eh.toblm for that expression to be accepted by DB2, so plugging in NULL for expr won't work while e.g. CAST(NULL as INT) will work (provided eh.toblm is of a numeric type).

Peter,

While you are right about the use of NULL, the problem of the original poster lies elsewhere. "where eh.toblm = coalesce(cast(NULL as whatever),eh.toblm)" is equivalent to "where eh.toblm = eh.toblm", which is equivalent to not having the WHERE clause at all.

Apart from using the tool (COALESCE) improperly, he (or she) seems to be using the wrong tool for the task.

My understanding of the question was rather whether the conditionwould indeed be "always true" in case expr is NULL. And to test this, raysefo tried to replace expr by NULL, which didn't work, hence his question.

" WHERE eh.toblm = coalesce(expr, eh.toblm)
would indeed be "always true" in case expr is NULL "

I disagree. If eh.toblm is <NULL> the result would be "unknown"

You're right. My mistake. Thanks!
The following would include that case:
where you must replace 'XYXYX' by some constant expression of the same datatype as eh.toblm and expr, but which never equals one of the possible values of those.