473,708 Members | 2,415 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Variables in SP do not compare as equal when both are NULL

DB2 8.2 for Linux, FP 10 (also performs the same on DB2 8.2 for Windoes, FP
11).

Using the SAMPLE database, tables EMP and EMLOYEE.

In the followng stored procedure, 2 NULL columns (COMM) are selected into 2
different SP variables and compared for equal. They are both NULL, but do
not compare as equal. When the Not NULL columns (SALARY) are compared, they
do compare as equal.

Is there a reason for this?

Statement terminator is @.

-- SET THE COMMISION TO NULL ON TWO SAMPLE TABLES FOR EMPNO '000010'

UPDATE EMP SET COMM = NULL WHERE EMPNO = '000010'@
UPDATE EMPLOYEE SET COMM = NULL WHERE EMPNO = '000010'@

--------------------------------------------------
DROP PROCEDURE TEST_SP@

CREATE PROCEDURE TEST_SP
(
OUT EMP_COMM DECIMAL(9,2),
OUT EMP_SALARY DECIMAL(9,2),
OUT EMPLOYEE_COMM DECIMAL(9,2),
OUT EMPLOYEE_SALARY DECIMAL(9,2),
OUT v_ERRMSG_1 VARCHAR(500),
OUT v_ERRMSG_2 VARCHAR(500)
)
SPECIFIC TEST_SP
RESULT SETS 0
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN

---------------------------------------------
-- Declare variables
---------------------------------------------

DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE at_end SMALLINT DEFAULT 0;

DECLARE A_EMPNO CHAR(6);
DECLARE A_EMP_COMM DECIMAL(9,2);
DECLARE A_EMP_SALARY DECIMAL(9,2);
DECLARE B_EMPNO CHAR(6);
DECLARE B_EMPLOYEE_COMM DECIMAL(9,2);
DECLARE B_EMPLOYEE_SALA RY DECIMAL(9,2);

---------------------------------------------
-- Declare exit handlers
---------------------------------------------

DECLARE EXIT HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1 v_ERRMSG_1 = MESSAGE_TEXT;

DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;

DECLARE EXIT HANDLER FOR SQLWARNING
GET DIAGNOSTICS EXCEPTION 1 v_ERRMSG_1 = MESSAGE_TEXT;

---------------------------------------------------------------
-- Begin processing logic
---------------------------------------------------------------

SELECT EMPNO, COMM, SALARY INTO A_EMPNO, A_EMP_COMM, A_EMP_SALARY
FROM DB2INST1.EMP
WHERE EMPNO = '000010';

SELECT EMPNO, COMM, SALARY INTO B_EMPNO, B_EMPLOYEE_COMM , B_EMPLOYEE_SALA RY
FROM DB2INST1.EMPLOY EE
WHERE EMPNO = A_EMPNO;

IF A_EMP_COMM = B_EMPLOYEE_COMM THEN
SET V_ERRMSG_1 = 'NULLS COMPARE AS EQUAL';
ELSE
SET V_ERRMSG_1 = 'NULLS DO NOT COMPARE AS EQUAL';
END IF;

IF A_EMP_SALARY = B_EMPLOYEE_SALA RY THEN
SET V_ERRMSG_2 = 'NON-NULLS COMPARE AS EQUAL';
ELSE
SET V_ERRMSG_2 = 'NON-NULLS DO NOT COMPARE AS EQUAL';
END IF;

SET EMP_COMM = A_EMP_COMM;
SET EMP_SALARY = A_EMP_SALARY;

SET EMPLOYEE_COMM = B_EMPLOYEE_COMM ;
SET EMPLOYEE_SALARY = B_EMPLOYEE_SALA RY;

END@

CALL TEST_SP (?,?,?,?,?,?)@

Value of output parameters
--------------------------
Parameter Name : EMP_COMM
Parameter Value : -

Parameter Name : EMP_SALARY
Parameter Value : 52750.00

Parameter Name : EMPLOYEE_COMM
Parameter Value : -

Parameter Name : EMPLOYEE_SALARY
Parameter Value : 52750.00

Parameter Name : V_ERRMSG_1
Parameter Value : NULLS DO NOT COMPARE AS EQUAL

Parameter Name : V_ERRMSG_2
Parameter Value : NON-NULLS COMPARE AS EQUAL

Return Status = 0


Jul 10 '06 #1
17 4528
In the followng stored procedure, 2 NULL columns (COMM) are selected into 2
different SP variables and compared for equal. They are both NULL, but do
not compare as equal. When the Not NULL columns (SALARY) are compared, they
do compare as equal.

Is there a reason for this?
Yep, that's the way it's supposed to be.

db2 "values (case when (nullif(0,0) = nullif(0,0)) then 1 else 0 end)"

1
-----------
0

1 record(s) selected.

db2 "values (case when (nullif(0,0) is null AND nullif(0,0) IS NULL)
then>

1
-----------
1

1 record(s) selected.

Jul 10 '06 #2
"mike" <_l*****@yahoo. comwrote in message
news:11******** *************@h 48g2000cwc.goog legroups.com...
Yep, that's the way it's supposed to be.

db2 "values (case when (nullif(0,0) = nullif(0,0)) then 1 else 0 end)"

1
-----------
0

1 record(s) selected.

db2 "values (case when (nullif(0,0) is null AND nullif(0,0) IS NULL)
then>

1
-----------
1

1 record(s) selected.
Yes, I figured out that is how it works. Is there is reason for this?
Jul 10 '06 #3

"Mark A" <no****@nowhere .comschreef in bericht
news:f_******** *************** *******@comcast .com...
"mike" <_l*****@yahoo. comwrote in message
news:11******** *************@h 48g2000cwc.goog legroups.com...
>Yep, that's the way it's supposed to be.

db2 "values (case when (nullif(0,0) = nullif(0,0)) then 1 else 0 end)"

1
-----------
0

1 record(s) selected.

db2 "values (case when (nullif(0,0) is null AND nullif(0,0) IS NULL)
then>

1
-----------
1

1 record(s) selected.

Yes, I figured out that is how it works. Is there is reason for this?
Yes, there is.
It is in the definition of NULL's.
A NULL means the specific value is unknown, NULL in itself isn't a value
that can be matched against any other value.

--
Jeroen
Jul 10 '06 #4
Yes, I figured out that is how it works. Is there is reason for this?

It is because in SQL, NULL is not a value, but the absence of one.
The equality predicate compares two values, not absence of them,
and if either of the operands to the equality predicate are NULL
then the result is unknown.

Jul 10 '06 #5
"mike" <_l*****@yahoo. comwrote in message
news:11******** **************@ 75g2000cwc.goog legroups.com...
>
It is because in SQL, NULL is not a value, but the absence of one.
The equality predicate compares two values, not absence of them,
and if either of the operands to the equality predicate are NULL
then the result is unknown.
That sounds like a circular argument to me (as does the other one posted by
Jeroene).

You claim that null is not a value, but the phrase "null value" turns up
frequently, including in DB2 documentation. And there are exceptions, such
as when using the DISTINCT predicate, where "null values are considered
equal. So (COL1 is NOT DISTINCT from COL2) will be true if both columns
contain an equal non-null value and also when both columns are the null
value." [quote from iSeries DB2 Infocenter].

Obviously, someone thought that relational theory dictated that two nulls
are not equal, but I don't quite understand under what conditions that it
would be useful. It just seems we have to do a lot of extra coding to get
around this "rule".
Jul 10 '06 #6
... It just seems we have to do a lot of extra coding to get
around this "rule".

Consider using COALESCE with predicates that involve nullable columns
to avoid explicitly coding the extra null tests.

Jul 10 '06 #7
Mark A wrote:
"mike" <_l*****@yahoo. comwrote in message
news:11******** **************@ 75g2000cwc.goog legroups.com...

It is because in SQL, NULL is not a value, but the absence of one.
The equality predicate compares two values, not absence of them,
and if either of the operands to the equality predicate are NULL
then the result is unknown.

That sounds like a circular argument to me (as does the other one posted by
Jeroene).

You claim that null is not a value, but the phrase "null value" turns up
frequently, including in DB2 documentation. And there are exceptions, such
as when using the DISTINCT predicate, where "null values are considered
equal. So (COL1 is NOT DISTINCT from COL2) will be true if both columns
contain an equal non-null value and also when both columns are the null
value." [quote from iSeries DB2 Infocenter].

Obviously, someone thought that relational theory dictated that two nulls
are not equal, but I don't quite understand under what conditions that it
would be useful. It just seems we have to do a lot of extra coding to get
around this "rule".
It is a misunderstandin g of what NULL is. NULL is on a different
"level" than other values, as it talks about the value. All values are
either known or unknown. That is, if it is known, the actual value can
be returned, if it is unknown, only a big question-mark can be
returned.

The second thing is, that a WHERE clause tests for a boolean value, and
so, a truth-table can be used to discern what it means. For example,
the AND truth-table:

(T)RUE/(F)ALSE

T+T=T
T+F=F
F+T=F
F+F=F

If NULL is added to any equation, we cannot answer any questions,
because since we don't know the value, its truth cannot be discerned.
Being neither TRUE nor FALSE, we must say we don't know, which is
database terminology is called NULL. Comparing NULL to NULL is
comparing to completely unknown values. There is no way anyone could
figure out how to compare them, so the result must be NULL as well.

Ultimately, to deal with NULLs, the special keyword IS is used.

As for why the documentation refers to it as a NULL value, it is
because it is differentiation between types of value, not of the values
themselves.

As for the INDEX, INDEXes are not part of the data, as much as they are
part of making the database work. So, implementation of NULL values on
an INDEX is up to the developers, and database implementations are not
consistent.

If you find NULL being more trouble than it is worth, perhaps NULL is
not appropriate for your scenario. Because if you use =, you are saying
you know what the value is. If there is no value, it should be a
zero-length string ('') not a NULL-value.

B.

Jul 10 '06 #8
Mark A wrote:
"mike" <_l*****@yahoo. comwrote in message
news:11******** **************@ 75g2000cwc.goog legroups.com...
>It is because in SQL, NULL is not a value, but the absence of one.
The equality predicate compares two values, not absence of them,
and if either of the operands to the equality predicate are NULL
then the result is unknown.

That sounds like a circular argument to me (as does the other one posted by
Jeroene).
To make a long story short: ANSI SQL
There are ton's of papers on how NULL should or shouldn't behave.
You claim that null is not a value, but the phrase "null value" turns up
frequently, including in DB2 documentation. And there are exceptions, such
as when using the DISTINCT predicate, where "null values are considered
equal. So (COL1 is NOT DISTINCT from COL2) will be true if both columns
contain an equal non-null value and also when both columns are the null
value." [quote from iSeries DB2 Infocenter].
Correct. It's so special they had to talk about it. :-)
There is an alternative predicate in the works for the SQL Standard
which will treat NULL as equal because there are valid arguments for
both sides.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 10 '06 #9
"Brian Tkatch" <Ma***********@ ThePentagon.com wrote in message at
conditions that it
>
If NULL is added to any equation, we cannot answer any questions,
because since we don't know the value, its truth cannot be discerned.
Being neither TRUE nor FALSE, we must say we don't know, which is
database terminology is called NULL. Comparing NULL to NULL is
comparing to completely unknown values. There is no way anyone could
figure out how to compare them, so the result must be NULL as well.
If COMM on table EMP is null, and COMM on table EMPLOYEE is null (both for
the same EMPNO= '000010', which is the PK), if I say:

IF EMP.COMM = EMPLOYEE.COMM

then I "CAN" compare them and IMO, they should be equal even if both values
are null. I understand that you disagree, but that is your opinion.
Ultimately, to deal with NULLs, the special keyword IS is used.
Not exactly. What I can do is:

IF COALESCE(EMP.CO MM,0) = COALESCE(EMPLOY EE.COMM,0)

or

IF ((EMP.COMM = EMPLOYEE.COMM) OR (EMP.COMM IS NULL AND EMPLOYEE.COMM IS
NULL))

It seems to me that those who actually have a need for IF EMP.COMM =
EMPLOYEE.COMM to test false (assuming that both columns are null) are in the
small minority (it would be hard for me to imagine what that would be used
for) and that any special syntax for it test true (as Serge mentioned may be
coming) should have been reserved for the rare exceptions and not the
majority case where most people would expect it to test true.
As for why the documentation refers to it as a NULL value, it is
because it is differentiation between types of value, not of the values
themselves.
This sounds like gobblygook to me. If the column is null, then it contains
null values. The value is null.
Jul 10 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

28
2271
by: JKop | last post by:
Haven't been able to find such a thing. Can anyone please inform me of a Standard C++ function for comparing two strings without regard to case. Both for working with "char*", and with "std::string". Thanks, -JKop
5
2242
by: pattanawadee | last post by:
Dear all, I have a problem about compare the 2 variables by use operator 'AND' while one variable is the int constant 0x00000002 and the other one is string '0x202' for the constant 0x00000002 is the flag of systemcall OPEN following fcntl.h #define O_RDWR 0x00000002 /* open for reading and
122
5302
by: Einar | last post by:
Hi, I wonder if there is a nice bit twiddling hack to compare a large number of variables? If you first store them in an array, you can do: for (i = 0; i < n; i++) { if (array != value) { /* array differs from value, do something*/
17
8232
by: bengamin | last post by:
Hi, I have a C# class and two instance of the class; the class have some property. I want to compare the property value of the two instance How should i do? override == ? use delegate ?
3
1362
by: John C Kirk | last post by:
One odd thing I've come across - if you declare a private variable in a class, it is exposed to other instances of that same class. To replicate this behaviour, create a class like this: Public Class Class1 Private mintID As Integer = 0 Public Sub New(ByVal pintID As Integer)
10
14482
by: lchian | last post by:
Hi, For two stl strings s1 and s2, I got different results from strcmp(s1.c_str(), s2.c_str()) and s1.compare(s2) can someone explain what these functions do? It seems that strcmp gives the "right" (i.e.wysiwyg) answer while compare() does not.
12
29463
by: Assimalyst | last post by:
Hi, I have a working script that converts a dd/mm/yyyy text box date entry to yyyy/mm/dd and compares it to the current date, giving an error through an asp.net custom validator, it is as follows: function doDateCheckNow(source, args) { var oDate = document.getElementById(source.controltovalidate); // dd/mm/yyyy
1
4560
by: The Rookie | last post by:
Hi everybody, The rookie is back... Ok I'm working actually on a lot of differents financial excel tables (some with calculation'macro) and most of the time I need to check manually if every financial deal match, and obviously it takes ages. Can someone tell or explain me if I could use a Query or Macro for be able to compare each table with the rest of them and extract just the data which doesn't match with what I'm looking for.
50
20312
by: titan nyquist | last post by:
I wish to compare two structs via == but it does not compile. I can overload and create my own == but am I missing something that c# already has implemented? ~titan
0
8788
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9159
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9061
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9001
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7925
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6615
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4713
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3151
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2097
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.