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

Compare Row Counts

Hi all,

I have a need to compare the number of rows returned from table A when it is joined to table B to the number of rows returned when there are no joins involved. If the number of rows returned are the same, then I need to proceed to execute my next step else end.

So, If RowCount A = RowCount A when A joined to B
THEN Goto Next Step
Else End

I need to put the above logic in a sp that I want to execute using a job.

Help is appreciated.

VDECLARE @.countTotal INTEGER
SELECT @.countTotal = COUNT(*) FROM TableA

DECLARE @.countJoin INTEGER
SELECT @.countJoin = COUNT(DISTINCT TableA.ID) FROM TableA INNER JOIN TableB ON (TableA.Col = TableB.Col)

IF @.countTotal = @.countJoin BEGIN
-- Do something
ELSE
-- Do something else
END|||IF ( SELECT SUM(myCount)
FROM (
SELECT COUNT(*) AS myCount
FROM TableA
UNION ALL
SELECT COUNT(*)*-1 AS myCount
FROM TableA
INNER JOIN TableB
ON a.key = b.key)) AS XXX) = 0
BEGIN
-- Do something
END|||declare @.A table(Col int)
declare @.B table(Col int)

insert @.A values(1)
insert @.A values(2)
insert @.B values(1)

declare @.CountA int
,@.CountJoinB int

select @.CountA = count(A.Col)
,@.CountJoinB = count(B.Col)
from @.A A left join @.B B on A.Col = B.Col

select @.CountA,@.CountJoinB|||LEFT OUTER JOIN is definitely the right way to think about this problem

notice that you don't actually have to take any counts in order to determine whether to proceed

select 'stop' from A
where not exists (
select 937 from B
where B.FK = A.PK )

;)|||I agree with r937. You should not need to take an actual count to do this.

Compare data between two views on two different databases

How can I compare data returned by two views on two different
databases? I want to compare column by column or the whole dataset. I
need to do that in query.
Thanks"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1178058288.926031.204610@.h2g2000hsg.googlegroups.com...
> How can I compare data returned by two views on two different
> databases? I want to compare column by column or the whole dataset. I
> need to do that in query.
> Thanks
>
It's not difficult to compare them but you haven't specified exactly what
result you would like to see from the comparison. Maybe you just want the
exceptions - the rows that don't appear in both views. In that case:
(SELECT *
FROM DB01.dbo.view1
EXCEPT
SELECT *
FROM DB02.dbo.view2)
UNION
(SELECT *
FROM DB02.dbo.view2
EXCEPT
SELECT *
FROM DB01.dbo.view1);
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the quick response, but it doesn't work for me - hopefully
it is just me.
If I run this on sql query analyzer
Except({Canada, [British Columbia], Mexico, [British Columbia], USA,
Washington}, {Canada, Mexico, California})
I get [Microsoft][ODBC SQL Server Driver]Syntax error or access
violation
I think Except only works for enterprise version of sql server.
And yes, by the way, I just want the exceptions - the rows that don't
appear in both views.
On May 1, 6:37 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "Sehboo" <MasoodAd...@.gmail.com> wrote in message
> news:1178058288.926031.204610@.h2g2000hsg.googlegroups.com...
> > How can Icomparedatareturned by two views on two different
> > databases? I want tocomparecolumn by column or the whole dataset. I
> > need to do that in query.
> > Thanks
> It's not difficult tocomparethem but you haven't specified exactly what
> result you would like to see from the comparison. Maybe you just want the
> exceptions - the rows that don't appear in both views. In that case:
> (SELECT *
> FROM DB01.dbo.view1
> EXCEPT
> SELECT *
> FROM DB02.dbo.view2)
> UNION
> (SELECT *
> FROM DB02.dbo.view2
> EXCEPT
> SELECT *
> FROM DB01.dbo.view1);
> --
> David Portas,SQLServerMVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version ofSQLServeryou are using and specify the content
> of any error messages.
> SQLServerBooks Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --|||"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1178062348.162726.300260@.y80g2000hsf.googlegroups.com...
> Thanks for the quick response, but it doesn't work for me - hopefully
> it is just me.
> If I run this on sql query analyzer
> Except({Canada, [British Columbia], Mexico, [British Columbia], USA,
> Washington}, {Canada, Mexico, California})
> I get [Microsoft][ODBC SQL Server Driver]Syntax error or access
> violation
Correct. But that's not the query I gave you or anything like it! Why did
you add the extra parentheses and remove the SELECT statements? Please
consult Books Online if you need help with the syntax.
> I think Except only works for enterprise version of sql server.
>
It works in all editions but only in version 2005. Always state what version
you are using. Following is another alternative that should be compatible
with 2000 as well. If you need more assistance it would help if you could
post DDL and sample data to reproduce the problem.
SELECT key_col, col1, col2, col3
FROM
(SELECT
COALESCE(v1.key_col, v2.key_col),
COALESCE(v1.col1, v2.col1),
COALESCE(v1.col2, v2.col2),
COALESCE(v1.col3, v2.col3)
FROM DB01.dbo.view1 AS v1
FULL JOIN DB02.dbo.view2 AS v2
ON v1.key_col = v2.key_col
WHERE v1.key_col IS NULL OR v2.key_col IS NULL)
AS t(key_col, col1, col2, col3);
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Compare data between two views on two different databases

How can I compare data returned by two views on two different
databases? I want to compare column by column or the whole dataset. I
need to do that in query.
Thanks
Thanks for the quick response, but it doesn't work for me - hopefully
it is just me.
If I run this on sql query analyzer
Except({Canada, [British Columbia], Mexico, [British Columbia], USA,
Washington}, {Canada, Mexico, California})
I get [Microsoft][ODBC SQL Server Driver]Syntax error or access
violation
I think Except only works for enterprise version of sql server.
And yes, by the way, I just want the exceptions - the rows that don't
appear in both views.
On May 1, 6:37 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "Sehboo" <MasoodAd...@.gmail.com> wrote in message
> news:1178058288.926031.204610@.h2g2000hsg.googlegro ups.com...
>
> It's not difficult tocomparethem but you haven't specified exactly what
> result you would like to see from the comparison. Maybe you just want the
> exceptions - the rows that don't appear in both views. In that case:
> (SELECT *
> FROM DB01.dbo.view1
> EXCEPT
> SELECT *
> FROM DB02.dbo.view2)
> UNION
> (SELECT *
> FROM DB02.dbo.view2
> EXCEPT
> SELECT *
> FROM DB01.dbo.view1);
> --
> David Portas,SQLServerMVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version ofSQLServeryou are using and specify the content
> of any error messages.
> SQLServerBooks Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --

Compare data between two views on two different databases

How can I compare data returned by two views on two different
databases? I want to compare column by column or the whole dataset. I
need to do that in query.
Thanks"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1178058288.926031.204610@.h2g2000hsg.googlegroups.com...
> How can I compare data returned by two views on two different
> databases? I want to compare column by column or the whole dataset. I
> need to do that in query.
> Thanks
>
It's not difficult to compare them but you haven't specified exactly what
result you would like to see from the comparison. Maybe you just want the
exceptions - the rows that don't appear in both views. In that case:
(SELECT *
FROM DB01.dbo.view1
EXCEPT
SELECT *
FROM DB02.dbo.view2)
UNION
(SELECT *
FROM DB02.dbo.view2
EXCEPT
SELECT *
FROM DB01.dbo.view1);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the quick response, but it doesn't work for me - hopefully
it is just me.
If I run this on sql query analyzer
Except({Canada, [British Columbia], Mexico, [British Columbia],
USA,
Washington}, {Canada, Mexico, California})
I get [Microsoft][ODBC SQL Server Driver]Syntax error or access
violation
I think Except only works for enterprise version of sql server.
And yes, by the way, I just want the exceptions - the rows that don't
appear in both views.
On May 1, 6:37 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "Sehboo" <MasoodAd...@.gmail.com> wrote in message
> news:1178058288.926031.204610@.h2g2000hsg.googlegroups.com...
>
>
> It's not difficult tocomparethem but you haven't specified exactly what
> result you would like to see from the comparison. Maybe you just want the
> exceptions - the rows that don't appear in both views. In that case:
> (SELECT *
> FROM DB01.dbo.view1
> EXCEPT
> SELECT *
> FROM DB02.dbo.view2)
> UNION
> (SELECT *
> FROM DB02.dbo.view2
> EXCEPT
> SELECT *
> FROM DB01.dbo.view1);
> --
> David Portas,SQLServerMVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version ofSQLServeryou are using and specify the content
> of any error messages.
> SQLServerBooks Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQ
L.90).aspx
> --|||"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1178062348.162726.300260@.y80g2000hsf.googlegroups.com...
> Thanks for the quick response, but it doesn't work for me - hopefully
> it is just me.
> If I run this on sql query analyzer
> Except({Canada, [British Columbia], Mexico, [British Columbia
], USA,
> Washington}, {Canada, Mexico, California})
> I get [Microsoft][ODBC SQL Server Driver]Syntax error or access
> violation
Correct. But that's not the query I gave you or anything like it! Why did
you add the extra parentheses and remove the SELECT statements? Please
consult Books Online if you need help with the syntax.

> I think Except only works for enterprise version of sql server.
>
It works in all editions but only in version 2005. Always state what version
you are using. Following is another alternative that should be compatible
with 2000 as well. If you need more assistance it would help if you could
post DDL and sample data to reproduce the problem.
SELECT key_col, col1, col2, col3
FROM
(SELECT
COALESCE(v1.key_col, v2.key_col),
COALESCE(v1.col1, v2.col1),
COALESCE(v1.col2, v2.col2),
COALESCE(v1.col3, v2.col3)
FROM DB01.dbo.view1 AS v1
FULL JOIN DB02.dbo.view2 AS v2
ON v1.key_col = v2.key_col
WHERE v1.key_col IS NULL OR v2.key_col IS NULL)
AS t(key_col, col1, col2, col3);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Common Paging Practice

Is there a method used to limit the returned number of records from a
query, useful for paging, that does not involve the process of creating
a temp table with an identity field and limiting results by that.See if this helps:
How do I page through a recordset?
http://www.aspfaq.com/show.asp?id=2120
AMB
"jw56578@.gmail.com" wrote:

> Is there a method used to limit the returned number of records from a
> query, useful for paging, that does not involve the process of creating
> a temp table with an identity field and limiting results by that.
>|||Depending on what application you are using to display the resultset
(ASP.NET, MS Access, Crystal, etc), you will have different options for
paging at the client side. For a pure SQL solution, let's assume you have a
table (mytable) that contains a record for each zip code for each state, and
you want to page 10 zip codes at a time for the state of Arizona.
Page zip codes 31 - 40:
select bottom 10 zip_code from (select top 40 zip_code from mytable where
state_code='AZ' order by zip_code) as x
Page zip codes 41 - 50:
select bottom 10 zip_code from (select top 50 zip_code from mytable where
state_code='AZ' order by zip_code) as x
<jw56578@.gmail.com> wrote in message
news:1124732360.078016.240550@.f14g2000cwb.googlegroups.com...
> Is there a method used to limit the returned number of records from a
> query, useful for paging, that does not involve the process of creating
> a temp table with an identity field and limiting results by that.
>|||The subquery solution is fine for 50 rows, but not 50,000...
(Also, there is no "bottom" in T-SQL. You would use TOP with an opposite
ORDER BY.)

> Page zip codes 31 - 40:
> select bottom 10 zip_code from (select top 40 zip_code from mytable where
> state_code='AZ' order by zip_code) as x
> Page zip codes 41 - 50:
> select bottom 10 zip_code from (select top 50 zip_code from mytable where
> state_code='AZ' order by zip_code) as x|||It should prove quite scalable, assuming that state_code and zip_code are
both indexed. Besides, any application that requires the user to page
through 50,000 records needs some serious re-engineering from a GUI
usability perspective. For example, when a user is browsing available
properties at a realestate website, then they are typically confined to
searching a specific zip code and price range. If they want a listing of all
properties on a regional basis, then that should be delivered in report form
or cached on the client side.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eCCdOo0pFHA.2916@.TK2MSFTNGP14.phx.gbl...
> The subquery solution is fine for 50 rows, but not 50,000...
> (Also, there is no "bottom" in T-SQL. You would use TOP with an opposite
> ORDER BY.)
>
>
>
>