Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Case statement in where clause

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vijigobi

New User


Joined: 13 Feb 2007
Posts: 5
Location: Bangalore

PostPosted: Wed Oct 17, 2007 7:11 pm    Post subject: Case statement in where clause
Reply with quote

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 ?
Back to top
View user's profile Send private message

gauravgupta2808
Warnings : 1

New User


Joined: 31 May 2007
Posts: 31
Location: Chennai, India

PostPosted: Wed Oct 17, 2007 8:15 pm    Post subject:
Reply with quote

Try this
Code:

select *
from table1
where
.
.
.
(CASE                                       
WHEN :host-date  IS NULL THEN   
tdate IS NULL
ELSE                                         
   tdate = :host-date
                               
END)       



i am not sure if 'tdate IS NULL' works...
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1228
Location: Israel

PostPosted: Wed Oct 17, 2007 8:39 pm    Post subject:
Reply with quote

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 ?
Back to top
View user's profile Send private message
vijigobi

New User


Joined: 13 Feb 2007
Posts: 5
Location: Bangalore

PostPosted: Tue Oct 23, 2007 8:14 pm    Post subject:
Reply with quote

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 ?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts ROUNDED Problem with COMPUTE statement shalem COBOL Programming 11 Thu Feb 09, 2017 8:16 pm
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm
No new posts Summing on Mixed Case Literals Roy Ware SYNCSORT 3 Tue Jan 03, 2017 10:18 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts COBOL DB2 - CALL statement - high CPU... TS70363 DB2 15 Sun Sep 11, 2016 6:07 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us