Collation Conflict

Modify your query so that it converts your joins to
unicode data. This will make it collation independant.
PS Do you want to know why it went wrong or are you ok
with it ?
J

>--Original Message--
>Hi,
>I am running a query over 2 tables in 2 different
databases.
>I get the following error "Cannot resolve collation
conflict for equal to
>operation"
>for example
>SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
[dbo].[TB1] [TB1]
>INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=
[TB2].[ProductCode])
>WHERE [TB2].[ProdGroup]=@.PG
>@.PG is a string parameter.
>Regards
>Tim
>
>.
>
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])
|||Your [TB1].[Product] and [TB2].[ProductCode] columns have different
collations, so the result of the join expression is ambiguous. Take a
look at the BOL topic "Collation Precedence" -- it provides a good
explanation of the problem. You can avoid this fairly trivially by
providing a COLLATE clause that removes the ambiguity like this:
SELECT TOP 100 [TB1].[Product] AS Q0000000
FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2]
ON [TB1].[Product]=[TB2].[ProductCode]) COLLATE database_default
WHERE [TB2].[ProdGroup]=@.PG
but this will make it impossible for the QP to use an index seek on the
right side of the join. If this is a big problem it may be better to
change the collation of one of the two columns (using ALTER TABLE ALTER
COLUMN) so that the collations match. Note that to run ALTER COLUMN on a
column's collation you must first drop any indexes, stats, or constraints
that reference the column.
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Tim Marsden" <TM@.UK.COM>
References: <e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl>
<11c6101c4423b$568580b0$a301280a@.phx.gbl>
Subject: Re: Collation Conflict
Date: Tue, 25 May 2004 14:49:31 +0100
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <OC3oY8lQEHA.3988@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host213-122-182-242.in-addr.btopenworld.com
213.122.182.242
Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!tk2msftngp
13.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:342917
X-Tomcat-NG: microsoft.public.sqlserver.server
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])

Collation Conflict

Modify your query so that it converts your joins to
unicode data. This will make it collation independant.
PS Do you want to know why it went wrong or are you ok
with it ?
J

>--Original Message--
>Hi,
>I am running a query over 2 tables in 2 different
databases.
>I get the following error "Cannot resolve collation
conflict for equal to
>operation"
>for example
>SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
[dbo].[TB1] [TB1]
>INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[/vbc
ol]
[TB2].[ProductCode])[vbcol=seagreen]
>WHERE [TB2].[ProdGroup]=@.PG
>@.PG is a string parameter.
>Regards
>Tim
>
>.
>Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx
.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])|||Your [TB1].[Product] and [TB2].[ProductCode] columns have di
fferent
collations, so the result of the join expression is ambiguous. Take a
look at the BOL topic "Collation Precedence" -- it provides a good
explanation of the problem. You can avoid this fairly trivially by
providing a COLLATE clause that removes the ambiguity like this:
SELECT TOP 100 [TB1].[Product] AS Q0000000
FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2]
ON [TB1].[Product]=[TB2].[ProductCode]) COLLATE database_def
ault
WHERE [TB2].[ProdGroup]=@.PG
but this will make it impossible for the QP to use an index seek on the
right side of the join. If this is a big problem it may be better to
change the collation of one of the two columns (using ALTER TABLE ALTER
COLUMN) so that the collations match. Note that to run ALTER COLUMN on a
column's collation you must first drop any indexes, stats, or constraints
that reference the column.
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Tim Marsden" <TM@.UK.COM>
References: <e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl>
<11c6101c4423b$568580b0$a301280a@.phx.gbl>
Subject: Re: Collation Conflict
Date: Tue, 25 May 2004 14:49:31 +0100
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <OC3oY8lQEHA.3988@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host213-122-182-242.in-addr.btopenworld.com
213.122.182.242
Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp
13.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:342917
X-Tomcat-NG: microsoft.public.sqlserver.server
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx
.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])

Coalescing data from more than 2 tables

I have a query, below, -Can anyone tell me how I could modify this to
add data from third and fourth tables. Many Thanks
----
select
coalesce (x.NHPART, y.TPROD)
, isnull(x.NCQNTY,0) as "NCQNTY (NCM)"
, isnull(y.TQTY,0) as "TQTY (ITH)"
from
(
select
NHPART
, isnull(sum (NCQNTY),0) NCQNTY
from
NCM
WHERE NHSET ='INSP' and NHCDAT between 20060201 and 20060228 and NHVNDR
='5220'
group by
NHPART
)
x full join
(
select
TPROD
, isnull(sum (TQTY),0) TQTY
from
ITH
WHERE TTDTE between 20060201 and 20060228 and TVEND ='5220'
group by
TPROD
) y on x.NHPART = y.TPROD
ORDER BY coalesce (x.NHPART, y.TPROD)
----By the time I get to a three way (or worse, four way) full outer join
I find all the equality tests start to get overwhelming - to the point
I have found it hard to be sure my code is written properly.
Eventually I found an alternative way to code them that I, at least,
find much simpler.
I start by writing a query that UNIONs together just the keys - which
is to say the columns used for the IN clause comparisons - from all
the data sources.
select NHPART
from NCM
where NHSET ='INSP'
and NHCDAT between 20060201
and 20060228
and HVNDR >='5220'
UNION
select TPROD
from ITH
WHERE TTDTE between 20060201
and 20060228
and TVEND ='5220'
UNION
select OTHERPROD
from OTHERTBL
WHERE OTHERCOLUMN = 'test value'
Note that UNION performs a DISTINCT, which we need.
Now, with that working, I use that as a derived table, and make it the
root table in a LEFT outer join rather than a FULL outer join:
select K.EitherPart,
coalesce(X.NCQNTY,0) as "NCQNTY (NCM)",
coalesce(Y.TQTY,0) as "TQTY (ITH)",
coalesce(Z.SOMENUMBER) as OtherQty
from (<query from above> ) as K
LEFT OUTER
JOIN (<your first derived table> ) as X
ON K.EitherPart = X.NHPART
LEFT OUTER
JOIN (<your second derived table> ) as Y
ON K.EitherPart = Y.TPROD
LEFT OUTER
JOIN (<yet another derived table> ) as Z
ON K.EitherPart = Z.OTHERPROD
I hope this helps.
Roy Harvey
Beacon Falls, CT
On 7 Mar 2006 03:16:37 -0800, "philipbennett25" <pbennett@.xyratex.com>
wrote:

>I have a query, below, -Can anyone tell me how I could modify this to
>add data from third and fourth tables. Many Thanks
>----
>select
> coalesce (x.NHPART, y.TPROD)
>, isnull(x.NCQNTY,0) as "NCQNTY (NCM)"
>, isnull(y.TQTY,0) as "TQTY (ITH)"
>from
>(
>select
> NHPART
>, isnull(sum (NCQNTY),0) NCQNTY
>from
> NCM
>WHERE NHSET ='INSP' and NHCDAT between 20060201 and 20060228 and NHVNDR
>='5220'
>group by
> NHPART
> )
>x full join
>(
>select
> TPROD
>, isnull(sum (TQTY),0) TQTY
>from
> ITH
>WHERE TTDTE between 20060201 and 20060228 and TVEND ='5220'
>group by
> TPROD
> ) y on x.NHPART = y.TPROD
>ORDER BY coalesce (x.NHPART, y.TPROD)
>----