Case statement in where clause

vijigobi · New User Joined: 13 Feb 2007 Posts: 5 Location: Bangalore

Hi,
I need a help.

How to handle NULL value for host variable in a Where clause

select *
from table1
where
.
.
tdate = :host-date
.
.

When I execute the program it gives -180
I have to select data from table1 even for host-date NULL

Hence I thot of using CASE statement
if the host-date is null, then I can use NULL value in predicate as below

select *
from table1
where
.
.
case when :host-date = null or spaces
then ( tdate is NULL )
else (tdate = :host-date)
end
.
.

But I am gettting syntax error.

Please assit..
is there any simple way ?

gauravgupta2808 · Posted: Wed Oct 17, 2007 8:15 pm

Try this

Marso · Posted: Wed Oct 17, 2007 8:39 pm

Are you sure the problem is caused by a null value ?
in SQLCODE -180 I haven't seen any reference to null.
can you display the contents of SQLERRMC together with the SQLCODE ?
what is the format of the "tdate" field, and how is variable "host-date" defined ?

vijigobi · New User Joined: 13 Feb 2007 Posts: 5 Location: Bangalore

Hi Marso,
:host-date is not actually NULL, its spaces.
I hope -180 will return if space is moved to a date field.

Hi gaurav,
'tdate IS NULL' is not working

Could anyone advise how to proceed.

I heard Case stmt can be used in where clause only for dynamic SQLs.
is that true ?