Hello,
I have one simple query joining two tables with left outer join on 3
fields and using MIN on two fields. These two tables have lot of data
about 3 mil in total. I am trying to migrate db from MS Access to SQL
2005. I get about 689000 rows in SQL Server, vs 863000 rows in MS
Access.

SELECT T1.[MON], T1.[ANUM], T2.[ANUM], MIN ( T1.[OCD]), MIN(T1.
[STATE]), T1.COUNT

FROM T1 INNER JOIN T2 ON T1.MON = T2.MON AND T1.[OCD] = T2.[OCD] AND
T1.[STATE] = T2.[STATE]

WHERE T1.[REASON] <> 'SOMETHING' AND T2.[REASON] <> 'SOMETHING'

GROUP BY T1.[MON], T1.[ANUM], T2.[ANUM], T1.COUNT

HAVING T1.[MON] <> '-' AND T1.[ANUM] <> '-'

I have about 30 queries to migrate and I am sort of stuck. Does any
one have any idea ?

JB

On Wed, 20 Feb 2008 11:31:46 -0800 (PST), dsdevonsomer@gmail.com wrote:

>Hello,
>I have one simple query joining two tables with left outer join on 3
>fields and using MIN on two fields. These two tables have lot of data
>about 3 mil in total. I am trying to migrate db from MS Access to SQL
>2005. I get about 689000 rows in SQL Server, vs 863000 rows in MS
>Access.
>
>SELECT T1.[MON], T1.[ANUM], T2.[ANUM], MIN ( T1.[OCD]), MIN(T1.
>[STATE]), T1.COUNT
>
>FROM T1 INNER JOIN T2 ON T1.MON = T2.MON AND T1.[OCD] = T2.[OCD] AND
>T1.[STATE] = T2.[STATE]
>
>WHERE T1.[REASON] <> 'SOMETHING' AND T2.[REASON] <> 'SOMETHING'
>
>GROUP BY T1.[MON], T1.[ANUM], T2.[ANUM], T1.COUNT
>
>HAVING T1.[MON] <> '-' AND T1.[ANUM] <> '-'
>
>I have about 30 queries to migrate and I am sort of stuck. Does any
>one have any idea ?
>
>JB

Hi JB,

Are there any rows in the data with REASON or MON equal to Null? And how
does Access handle T2.[REASON] <> 'SOMETHING' for Null values of REASON?

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

(dsdevonsomer@gmail.com) writes:
> I have one simple query joining two tables with left outer join on 3
> fields and using MIN on two fields. These two tables have lot of data
> about 3 mil in total. I am trying to migrate db from MS Access to SQL
> 2005. I get about 689000 rows in SQL Server, vs 863000 rows in MS
> Access.
>
> SELECT T1.[MON], T1.[ANUM], T2.[ANUM], MIN ( T1.[OCD]), MIN(T1.
> [STATE]), T1.COUNT
>
> FROM T1 INNER JOIN T2 ON T1.MON = T2.MON AND T1.[OCD] = T2.[OCD] AND
> T1.[STATE] = T2.[STATE]
>
> WHERE T1.[REASON] <> 'SOMETHING' AND T2.[REASON] <> 'SOMETHING'
>
> GROUP BY T1.[MON], T1.[ANUM], T2.[ANUM], T1.COUNT
>
> HAVING T1.[MON] <> '-' AND T1.[ANUM] <> '-'
>
> I have about 30 queries to migrate and I am sort of stuck. Does any
> one have any idea ?

Unfortunately, it's impossible to tell what the reason might be from
that scant amount of information. But I could offer some ideas on how
you should proceed to narrow it down.

0) Move the conditions in the HAVING clause to the WHERE clause. At
least in this particular query, there is no reason for having those
conditions in the HAVING clause.

1) Remove the GROUP BY (and MIN), and compare the number of rows.
If the number of rows now are the same(A), there are some issues
with the grouping as such. If the number of rows still are different
(and with a bigger total difference), there is an issue in
the selection (B).

2) (A) Remove columns from the GROUP BY list, and see if the difference
is due to a certain column.

3) (B) Narrow it down further by removing one of more of the conditions
from the WHERE clause (including those you moved in from HAVING).

4) (B) If that does not help play with the JOIN part.

At some point it's probably a good idea to narrow down also the dataset,
to see if there some certain data that is causing the difference. For
instance, different handling of lowercase/uppercase, accents etc.


And, oh, you said above that you were joining with outer join, but
above you have an inner join... If you use different join types in
Access and SQL Server, that could be an explanation.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Feb 20, 6:09*pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> *(dsdevonso...@gmail.com) writes:
> > I have one simple query joining two tables with left outer join on 3
> > fields and using MIN on two fields. These two tables have lot of data
> > about 3 mil in total. I am trying to migrate db from MS Access to SQL
> > 2005. I get about 689000 rows in SQL Server, vs 863000 rows in MS
> > Access.
>
> > SELECT T1.[MON], T1.[ANUM], T2.[ANUM], MIN ( T1.[OCD]), MIN(T1.
> > [STATE]), T1.COUNT
>
> > FROM T1 INNER JOIN T2 *ON T1.MON = T2.MON AND T1.[OCD] = T2.[OCD] AND
> > T1.[STATE] = T2.[STATE]
>
> > WHERE T1.[REASON] <> 'SOMETHING' AND T2.[REASON] <> 'SOMETHING'
>
> > GROUP BY T1.[MON], T1.[ANUM], T2.[ANUM], T1.COUNT
>
> > HAVING T1.[MON] <> '-' AND T1.[ANUM] <> '-'
>
> > I have about 30 queries to migrate and I am sort of stuck. Does any
> > one have any idea ?
>
> Unfortunately, it's impossible to tell what the reason might be from
> that scant amount of information. But I could offer some ideas on how
> you should proceed to narrow it down.
>
> 0) Move the conditions in the HAVING clause to the WHERE clause. At
> * *least in this particular query, there is no reason for having those
> * *conditions in the HAVING clause.
>
> 1) Remove the GROUP BY (and MIN), and compare the number of rows.
> * *If the number of rows now are the same(A), there are some issues
> * *with the grouping as such. If the number of rows still are different
> * *(and with a bigger total difference), there is an issue in
> * *the selection (B).
>
> 2) (A) Remove columns from the GROUP BY list, and see if the difference
> * *is due to a certain column.
>
> 3) (B) Narrow it down further by removing one of more of the conditions
> * * from the WHERE clause (including those you moved in from HAVING).
>
> 4) (B) If that does not help play with the JOIN part.
>
> At some point it's probably a good idea to narrow down also the dataset,
> to see if there some certain data that is causing the difference. For
> instance, different handling of lowercase/uppercase, accents etc.
>
> And, oh, you said above that you were joining with outer join, but
> above you have an inner join... If you use different join types in
> Access and SQL Server, that could be an explanation.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
> - Show quoted text -


Hello Erland/Hugo

First of, thank you much for replying. I have made error in typing. I
just have inner join. Not ANY Outer join.
Hugo, to your question, I have some null values in both tables for
MON and Reasons. Total of about 5000 rows between both tables are
having null values.

Is that what causing Access to return more results vs SQL Server ? I
tried including the clause " MON IS NOT NULL " and "Reason IS NOT
NULL" but Access still returns the same number of rows. ? Should I be
doing something different ?

Many thanks again,
JB

(dsdevonsomer@gmail.com) writes:
> First of, thank you much for replying. I have made error in typing. I
> just have inner join. Not ANY Outer join.
> Hugo, to your question, I have some null values in both tables for
> MON and Reasons. Total of about 5000 rows between both tables are
> having null values.
>
> Is that what causing Access to return more results vs SQL Server ? I
> tried including the clause " MON IS NOT NULL " and "Reason IS NOT
> NULL" but Access still returns the same number of rows. ? Should I be
> doing something different ?

Did you try to use the procedures I outlined to narrow down what the
problem might be?

I don't know Access, and I don't know your tables and data, so I cannot
really compete in any guessing game I'm afraid.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Thu, 21 Feb 2008 10:10:22 -0800 (PST), dsdevonsomer@gmail.com wrote:

(snip)
>Hugo, to your question, I have some null values in both tables for
>MON and Reasons. Total of about 5000 rows between both tables are
>having null values.
>
>Is that what causing Access to return more results vs SQL Server ?

Hi JB,

I do not *know* that, but it was my suspicion. That's why I asked.

In standard SQL, a row with Reason equal to NULL should be excluded with
"WHERE Reason = 'Something'" (this is quote intuitive), but it should
also be excluded with "WHERE Reason <> 'Something'" (which a lot of
people happen to find very unintuitive). SQL Server works like that,
unless you choose to use some compatibility settings to mimic behaviour
of a much older version (which you shouldn't).

I know little of Jet SQL (what Access uses), but I do know that this
implementation of SQL is a lot further removed from the standard than
SQL Server. So maybe, just maybe, the Access team decided to go for what
many people consider to be intuitive rather than do it right - and if
that's the case, it might explain the different row counts.

> I
>tried including the clause " MON IS NOT NULL " and "Reason IS NOT
>NULL" but Access still returns the same number of rows. ? Should I be
>doing something different ?

Hmmm, that suggests that Access was already filtering these rows out (as
it should). So unless you are actually using that backward compatibility
setting in SQL Server, the problem has to lie somewhere else.

I'm afraid it's time for you to start the old job of adding filters in
order to narrow down the result set bit by bit until you get an amount
of rows small enough for manual inspection that still displays the
inconsistency. Once you know which rows are causing the problem, it's
much easier to find the reason!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

On Feb 21, 5:55 pm, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALID> wrote:
> On Thu, 21 Feb 2008 10:10:22 -0800 (PST), dsdevonso...@gmail.com wrote:
>
> (snip)
>
> >Hugo, to your question, I have some null values in both tables for
> >MON and Reasons. Total of about 5000 rows between both tables are
> >having null values.
>
> >Is that what causing Access to return more results vs SQL Server ?
>
> Hi JB,
>
> I do not *know* that, but it was my suspicion. That's why I asked.
>
> In standard SQL, a row with Reason equal to NULL should be excluded with
> "WHERE Reason = 'Something'" (this is quote intuitive), but it should
> also be excluded with "WHERE Reason <> 'Something'" (which a lot of
> people happen to find very unintuitive). SQL Server works like that,
> unless you choose to use some compatibility settings to mimic behaviour
> of a much older version (which you shouldn't).
>
> I know little of Jet SQL (what Access uses), but I do know that this
> implementation of SQL is a lot further removed from the standard than
> SQL Server. So maybe, just maybe, the Access team decided to go for what
> many people consider to be intuitive rather than do it right - and if
> that's the case, it might explain the different row counts.
>
> > I
> >tried including the clause " MON IS NOT NULL " and "Reason IS NOT
> >NULL" but Access still returns the same number of rows. ? Should I be
> >doing something different ?
>
> Hmmm, that suggests that Access was already filtering these rows out (as
> it should). So unless you are actually using that backward compatibility
> setting in SQL Server, the problem has to lie somewhere else.
>
> I'm afraid it's time for you to start the old job of adding filters in
> order to narrow down the result set bit by bit until you get an amount
> of rows small enough for manual inspection that still displays the
> inconsistency. Once you know which rows are causing the problem, it's
> much easier to find the reason!
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

Hello everyone,
I tried all the options and after no success, I started from scratch
with one by one column in both sql and ms access and I am now getting
similar results. This was a weird exercise as still I am not sure,
what could have been wrong.

But I want to thank everyone who helped me understand little better
sql concepts.
thanks,
JB