sql server misc: sql novice is confused (again) -- sql server misc

sql novice is confused (again)

spam dump
11/16/2004 7:33:12 PM

sql server misc: Okay_ sql(2000) wizards_ here's a question for you...
I have two tables. One is used to update the other. I'm looking for
null or changed values in the existing route number field to replace
from table a to table b.
(actually the real query is looking at changed values for a bunch of other
fields as well as null values and a bunch of other stuff so don't bother to
'fix' my query these was just written to demonstrate my problem:)

Table A (new data user_contract_bcp)
contract_number Route_number
CBC15094 CBC25

Table B (existing data user_contract)
CBC15094 NULL

(actually there is much more data in my tables but I confined this to
cbc15094)

-- SET ANSI_NULLS to ON and test.
PRINT 'Testing ANSI_NULLS ON'
SET ANSI_NULLS ON
DECLARE @varname char
SELECT @varname = NULL
SELECT b.Contract_number as ExistC, b.route_number as ExistR,
a.Contract_number as NewC, a.route_number as NewR
FROM User_contract_BCP a
inner join user_contract b
on b.contract_number = a.contract_number
WHERE b.contract_number = 'CBC15094' and
a.route_number <> @varname
go
-- SET ANSI_NULLS to OFF and test.
PRINT 'Testing SET ANSI_NULLS OFF hey this one works'
SET ANSI_NULLS off
DECLARE @varname char
SELECT @varname = NULL
SELECT b.Contract_number as ExistC, b.route_number as ExistR,
a.Contract_number as NewC, a.route_number as NewR
FROM User_contract_BCP a
inner join user_contract b
on b.contract_number = a.contract_number
WHERE b.contract_number = 'CBC15094' and
a.route_number <> @varname
go
-- Okay setting ANSI_NULLS OFF worked comparing null variables why
-- doesn't it work for the next test.
-- where b.route_number is actually null just like @varname was_

PRINT 'Testing ANSI_NULLS ON'
SET ANSI_NULLS ON
SELECT b.Contract_number as ExistC, b.route_number as ExistR,
a.Contract_number as NewC, a.route_number as NewR
FROM User_contract_BCP a
inner join user_contract b
on b.contract_number = a.contract_number
WHERE b.contract_number = 'CBC15094' and
a.route_number <> b.route_number
go
-- SET ANSI_NULLS to OFF and test.
PRINT 'Testing SET ANSI_NULLS OFF ??'
SET ANSI_NULLS off
SELECT b.Contract_number as ExistC, b.route_number as ExistR,
a.Contract_number as NewC, a.route_number as NewR
FROM User_contract_BCP a
inner join user_contract b
on b.contract_number = a.contract_number
WHERE b.contract_number = 'CBC15094' and
a.route_number <> b.route_number

here's the output

Testing ANSI_NULLS ON
ExistC ExistR NewC NewR
--------- ------ --------- -----
(0 row(s) affected)

Testing SET ANSI_NULLS OFF hey this one works
ExistC ExistR NewC NewR
--------- ------ --------- -----
CBC15094 NULL CBC15094 CBC25
(1 row(s) affected)

Testing ANSI_NULLS ON
ExistC ExistR NewC NewR
--------- ------ --------- -----
(0 row(s) affected)

Testing SET ANSI_NULLS OFF ??
ExistC ExistR NewC NewR
--------- ------ --------- -----
(0 row(s) affected)

I was able to 'work around' this problem by basically changing the null to a
empty string but this really is a pain_

SELECT b.Contract_number as ExistC, b.route_number as ExistR ,
a.Contract_number as NewC, a.route_number as NewR FROM
User_contract_BCP a
inner join user_contract b
on b.contract_number = a.contract_number
WHERE b.contract_number = 'CBC15094' and
coalescel(a.route_number,'') <> coalescel(b.route_number,'')

SQL help says:
Null Values
A value of NULL indicates the value is unknown. A value of NULL is
different from an empty or zero value. No two null values are equal.
Comparisons between two null values, or between a NULL and any other
value, return unknown because the value of each NULL is unknown.
BUT it also says:
Transact-SQL also offers an extension for null processing. If the
option ANSI_NULLS is set off, then comparisons between nulls, such as
NULL = NULL, evaluate to TRUE. Comparisons between NULL and any data
value evaluate to FALSE.

Re: sql novice is confused (again)

Danny
11/22/2004 12:29:13 PM

Forget about setting ANSI_NULLS to off. It's rarely a good idea. Instead
TSQL provides isnull() and is null and coalesce to help with null
processing.

WHERE b.contract_number = 'CBC15094' and
(a.route_number <> b.route_number or b.route_number is null)

Danny

[quoted text, click to view]