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
 

 

Comparing a NULL value in WHERE clause

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

New User


Joined: 15 May 2008
Posts: 41
Location: Chennai

PostPosted: Thu Jun 03, 2010 5:35 pm    Post subject: Comparing a NULL value in WHERE clause
Reply with quote

Hi,

I have a query as below:

Code:
EXEC SQL

SELECT * FROM EMPLOYEE
WHERE EMPNO = :WS-EMPNO                       AND
           NAME   = :WS-NAME:WS-NAME-IND

END-EXEC

Here in my EMPLOYEE table, EMPNO is not nullable and NAME is nullable

Suppose, I have NULL value for NAME in database for EMPNO 12345.

To retrive the above data, I am moving -1 to WS-NAME-IND, 12345 to WS-EMPNO. But no data is selected.

Please let me know the problem in the above query.
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10256
Location: italy

PostPosted: Thu Jun 03, 2010 5:39 pm    Post subject: Reply to: Comparing a NULL value in WHERE clause
Reply with quote

what about adding something along the lines AND NAME IS NOT NULL ??
Back to top
View user's profile Send private message
saagu

New User


Joined: 15 May 2008
Posts: 41
Location: Chennai

PostPosted: Thu Jun 03, 2010 5:43 pm    Post subject: Reply to: Comparing a NULL value in WHERE clause
Reply with quote

But my requirement is not just checking whether its NULL or not.
If I have a valid value for NAME, then I have to check that name aginst database
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10256
Location: italy

PostPosted: Thu Jun 03, 2010 5:48 pm    Post subject: Reply to: Comparing a NULL value in WHERE clause
Reply with quote

if You had read and understood my post I said ADD and don' t take away anything

where <somecolumn_name> IS NOT NULL AND <somecolumn_name> <isequalto> <some_value>
is a well built logic constraint

IIRC db2 is smart enough to check for nullness before checking for a value expression
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Jun 03, 2010 6:51 pm    Post subject:
Reply with quote

the old way :
Code:
where ((:hv = column) or (:hvind = -1 and column is null))
=> Stage 2 and not indexable

the decent way since DB2 V8
Code:
where column is not distinct from :hv:hvind
==> Stage 1 and indexable

NULL = NULL is FALSE
NULL <> NULL is FALSE
like in math : infinity
that's why IBM invented IS DISTINCT FROM and IS NOT DISTINCT FROM

so this should work :
Code:
EXEC SQL
SELECT * FROM EMPLOYEE
WHERE EMPNO = :WS-EMPNO           
  AND NAME   is not distinct from :WS-NAME:WS-NAME-IND
END-EXEC
Back to top
View user's profile Send private message
saagu

New User


Joined: 15 May 2008
Posts: 41
Location: Chennai

PostPosted: Thu Jun 03, 2010 7:29 pm    Post subject: Reply to: Comparing a NULL value in WHERE clause
Reply with quote

Thanks GuyC..
your suggestion is helpful
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Jun 03, 2010 7:32 pm    Post subject: Re: Reply to: Comparing a NULL value in WHERE clause
Reply with quote

saagu wrote:
Thanks GuyC..
your suggestion is helpful

helpful? HELPFUL ? I did your bloody work for you icon_evil.gif

Just kidding, thx for the feedback.
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 Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm


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