Sunday, March 25, 2012

Comparing Nulls

Hi All,
I am in the middle of checking data between SQL servers.
I am not getting any rows returned because in one server the amount is null
in the other is is a number. why aren't these unequal amounts coming in?
I have the same issue with date fileds.
my code is:
select a.accountkey,b.accountkey as BRacct,a.ContractID,a.ContractAmount,
b.ContractAmount as BRconAmt
from ACrepl..maincontracts a inner join BR11.AC.dbo.tblmaincontracts b
on a.contractId = b.contractID
where a.contractAmount <> b.contractAmount
TIA,
JoeThe only comparison allowed, when ansi_nulls is on, for null values are [not]
is null. Null can not be compared even with itself.
Try:
select
a.accountkey,
b.accountkey as BRacct,
a.ContractID,
a.ContractAmount,
b.ContractAmount as BRconAmt
from
ACrepl..maincontracts a
inner join
BR11.AC.dbo.tblmaincontracts b
on a.contractId = b.contractID
where
a.contractAmount <> b.contractAmount
or (contractAmount + b.contractAmount) is null
go
AMB
"jaylou" wrote:

> Hi All,
> I am in the middle of checking data between SQL servers.
> I am not getting any rows returned because in one server the amount is nul
l
> in the other is is a number. why aren't these unequal amounts coming in?
> I have the same issue with date fileds.
> my code is:
> select a.accountkey,b.accountkey as BRacct,a.ContractID,a.ContractAmount,
> b.ContractAmount as BRconAmt
> from ACrepl..maincontracts a inner join BR11.AC.dbo.tblmaincontracts b
> on a.contractId = b.contractID
> where a.contractAmount <> b.contractAmount
> TIA,
> Joesqlsql

No comments:

Post a Comment