SEO

#1 (**permalink**) 02-27-2008, 11:31 AM

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)

#2 (**permalink**) 02-27-2008, 11:31 AM

raysefo wrote:
> 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.
>
NULL keyword can't use as argument of COALESCE on DB2.

> 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)
How about this?
....
WHERE @id IS NULL

#3 (**permalink**) 02-27-2008, 11:31 AM

raysefo wrote:
> 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'm not sure I fully understand your requirements, but in you example
you probably need to cast null to whatever type eh.toblm is. Example:

[lelle@53dbd181 lelle]$ db2 "select * from sysibm.sysdummy1 where
IBMREQD = coalesce(null, IBMREQD)"
SQL0206N "NULL" is not valid in the context where it is used.
SQLSTATE=42703

[lelle@53dbd181 lelle]$ db2 "select * from sysibm.sysdummy1 where
IBMREQD = coalesce(cast(null as char), IBMREQD)"

IBMREQD
-------
Y

1 record(s) selected.

HTH
/Lennart

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

coalesce?