p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   Using COLLATE in SQL SELECT Statements (http://p2p.wrox.com/showthread.php?t=20002)

sudhakaratp October 18th, 2004 07:01 AM

Using COLLATE in SQL SELECT Statements
 
Hi,
I have two databases
Db1 is in collation "SQL_Latin1_General_CP1_CI_AS" and
Db2 is in collation "Compatibility_52_409_30003".

Now I need to get records based on the values that i have in these two databases. I have used the following query for that
Select db1..table1.field1,db2..table2.field3 from db1..table1 inner join db2..table2 on db1..table1.field4 = db2..table2.field5 COLLATE SQL_Latin1_General_CP1_CI_AS.

When I run this query, it is returning Incorrect syntax near COLLATE.
Can anyone help me on this. I need to solve this issue @ the earliest.

Expecting a positive reply from my dear forum members.

Thanks in advance
Regards
Sudhakara.T.P.

Anantsharma October 19th, 2004 03:20 AM

HI,

Use COLLATE at Left side of comparison.

B. Anant

sudhakaratp October 19th, 2004 03:25 AM

Hi Anant,

Its the same result. I tried using that both on left and right side of the = symbol, but it gives the same result saying "invalid syntax near collate"

Thanks & Regards
Sudhakara.T.P.

Anantsharma October 19th, 2004 03:43 AM

HI,

I m in hurry. Here is a query I am using in my produxtion code and its working.

Select ..............From......
Left OUTER join RCHSFIL RCH on pof.po COLLATE SQL_Latin1_General_CP1253_CS_AS = RCH.PO"

I feel u r doing a liitle mistake somewhere, I will be comming back and see. Till then seethis query. Hope u can fix it.

B. Anant

Anantsharma October 19th, 2004 04:31 AM

HI again,

Select db1.dbo.table1.field1,db2.dbo.table2.field3 from db1.dbo.table1 inner join db2.dbo.table2 on db2.dbo.table2.field5 COLLATE SQL_Latin1_General_CP1_CI_AS = db1.dbo.table1.field4

Try this.

B. Anant

sudhakaratp October 19th, 2004 04:35 AM

Hi Ananth,
This is the query that I am using. Here, I have two databases. Intranet..Events is in collation Compatibility_52_409_30003
Pewcentral..staff is in collation SQL_Latin1_General_CP1_CI_AS
So, I need to get the count of records and here is the query that I am using.
Can you make out any difference in the query that you had sent me and the query that is here. When I run this query, i am getting the error invalid syntax near collate.

Select Count(*) as TotRec
FROM Intranet..Events E LEFT OUTER JOIN PewCentral..staff P on E.EditedBy collate SQL_Latin1_General_CP1_CI_AS = P.Initials

Thanks & Regards
Sudhakara.T.P.

Anantsharma October 19th, 2004 05:01 AM

HI,

Well seems u r using collation oppositly. Ur Query says to use "SQL_Latin1_General_CP1_CI_AS" with E. But U r saying E is with collation "Compatibility_52_409_30003".


Select Count(*) as TotRec
FROM Intranet.dbo.Events E LEFT OUTER JOIN PewCentral.dbo.staff P on P.INITIALS collate SQL_Latin1_General_CP1_CI_AS = E.EDITEDBY collate
Compatibility_52_409_30003

I am not sure about where u got coolation "Compatibility_52_409_30003"
I can't find this collation in SQL Server.

If still facing problems, I think u can change the fashion of query using WHERE IN etc.... Also u can go with SUM of Union ALLs. Further its the matter of digging the sea. Lets Keep trying.

By the way u can get the exact way to write this using Enterprice manage Query builder. I think u should create a dummy table in same database. Join these table through Enterprice Managers Graphical Query designer and see the generated query in SQL pane.

Gud luck.

B. Anant

sudhakaratp October 19th, 2004 06:10 AM

Hi Anant,

I will try this.

Thanks for your support.

Regards
Sudhakara.T.P.

Jeff Moden September 6th, 2018 01:16 PM

Don't bother... previous poster is a low life spammer
 
Title of this post says it all...


All times are GMT -4. The time now is 02:25 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.