SEO

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_SALARY 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_SALARY
FROM DB2INST1.EMPLOYEE
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_SALARY 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_SALARY;

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

> 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.

"mike" <_link98@yahoo.com> wrote in message
news:1152510153.657801.41110@h48g2000cwc.googlegro ups.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?

"Mark A" <nobody@nowhere.com> schreef in bericht
news:f_6dnZGABreldyzZnZ2dnUVZ_oSdnZ2d@comcast.com. ..
> "mike" <_link98@yahoo.com> wrote in message
> news:1152510153.657801.41110@h48g2000cwc.googlegro ups.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

> 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.

"mike" <_link98@yahoo.com> wrote in message
news:1152514099.822155.301690@75g2000cwc.googlegro ups.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 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.

Mark A wrote:
> "mike" <_link98@yahoo.com> wrote in message
> news:1152514099.822155.301690@75g2000cwc.googlegro ups.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 misunderstanding 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.

Mark A wrote:
> "mike" <_link98@yahoo.com> wrote in message
> news:1152514099.822155.301690@75g2000cwc.googlegro ups.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/

"Brian Tkatch" <Maxwell_Smart@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.COMM,0) = COALESCE(EMPLOYEE.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.