IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Comparing a NULL value in WHERE clause


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu Jun 03, 2010 5:39 pm
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu Jun 03, 2010 5:48 pm
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
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Null values are considered in Total c... DFSORT/ICETOOL 6
No new posts To search DB2 table based on Conditio... DB2 1
No new posts the system or user abend SF0F R=NULL COBOL Programming 0
No new posts Comparing Header and Trailer. DFSORT/ICETOOL 7
No new posts Uploading from desktop, a CSV file, s... DB2 2
Search our Forums:

Back to Top