Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
VALIDATE NULL VALUE IN DB2 CASE STATEMENT

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

Active User


Joined: 05 Oct 2006
Posts: 145

PostPosted: Thu Feb 09, 2017 4:34 pm    Post subject: VALIDATE NULL VALUE IN DB2 CASE STATEMENT
Reply with quote

Hi,
can we do some kind of case statement within the where clause to validate the null value of the column.

please find the example below.

I have a indicator "y" or "n". based on this indicator I need to select the value (where clause using db2 case).

in the below example if the IND = 'Y' i should select only the columns with TERM_DATE IS NULL. else select all the value.

A.TERM_DATE =
CASE WHEN IND = 'Y'
THEN NULL
ELSE A.TERM_DATE
END

i know we cannot check for = NULL and it has to be IS NULL. but IS NUL inside the case statement doesn't work. is there any other way of handling this scenario?

Regards,
useit
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Thu Feb 09, 2017 7:20 pm    Post subject:
Reply with quote

Just curious, are you attempting something like this? Or maybe I am confused about the 'case' requirement and the '= NULL' issue icon_rolleyes.gif
Code:
WHERE (IND =  'Y' AND A.TERM_DATE IS NULL) OR
      (IND <> 'Y')
Back to top
View user's profile Send private message
useit

Active User


Joined: 05 Oct 2006
Posts: 145

PostPosted: Thu Feb 09, 2017 7:38 pm    Post subject: Reply to: VALIDATE NULL VALUE IN DB2 CASE STATEMENT
Reply with quote

no arun,

i have a Terminte indicator Y or N Which would help me to fetch only active accounts and active and terminated accounts both.

so if the Term-ind = Y. I Should fetch only active accounts. (i, e Terminate_date is null). And if the term-ind = 'N' i should fetch both active and non active accounts(I, e Term_dt is null and term_dt is not Null).

where terminate_dt =
case :host-term-ind = 'y'
then null
else null not nul both

end

something like above.

thanks,
useit
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Thu Feb 09, 2017 8:33 pm    Post subject:
Reply with quote

I was assuming the 'terminate indicator' to be a table column name based on your initial post. But from your recent post above, looks like the indicator is just an input variable and you are trying to adjust your WHERE clause on TERMINATE_DT to select only NULL if the input indicator is 'Y', and to ignore that filtering if the indicator is 'N' (or does not equal 'Y').

If that is the case, would changing the IND in my previous response to your indicator host variable, help?
Back to top
View user's profile Send private message
useit

Active User


Joined: 05 Oct 2006
Posts: 145

PostPosted: Thu Feb 09, 2017 9:24 pm    Post subject:
Reply with quote

thanks arun,
it worked for me.icon_smile.gif sorry i did not get this idea.... i was just trying with case statement.icon_smile.gif

thanks again


Regards,
Prahlad Shetty
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Thu Feb 09, 2017 9:57 pm    Post subject:
Reply with quote

No worries, You're welcome!
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 Validate the Date girishb2 DFSORT/ICETOOL 9 Tue Sep 19, 2017 1:12 am
No new posts DB2 NULL Values display V S Amarendra Reddy DB2 3 Thu Sep 07, 2017 6:59 pm
No new posts WER268A OUTREC STATEMENT : SYNTAX E... frozenblood87 SYNCSORT 12 Sat Aug 26, 2017 9:45 pm
No new posts Suppress value reported by BREAK in D... Learncoholic DFSORT/ICETOOL 3 Wed Aug 16, 2017 6:03 pm
No new posts ON 2 AND EVERY 1 - Statement ??? UmeySan COBOL Programming 2 Tue Jul 25, 2017 1:20 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us