View previous topic :: View next topic
|
Author |
Message |
saagu
New User
Joined: 15 May 2008 Posts: 41 Location: Chennai
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
what about adding something along the lines AND NAME IS NOT NULL ?? |
|
Back to top |
|
|
saagu
New User
Joined: 15 May 2008 Posts: 41 Location: Chennai
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
saagu
New User
Joined: 15 May 2008 Posts: 41 Location: Chennai
|
|
|
|
Thanks GuyC..
your suggestion is helpful |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
saagu wrote: |
Thanks GuyC..
your suggestion is helpful |
helpful? HELPFUL ? I did your bloody work for you
Just kidding, thx for the feedback. |
|
Back to top |
|
|
|