Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old October 18th, 2004, 08:01 AM
Registered User
 
Join Date: Dec 2003
Location: Bangalore, Karntaka, India.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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.
Reply With Quote
  #2 (permalink)  
Old October 19th, 2004, 04:20 AM
Friend of Wrox
 
Join Date: Sep 2004
Location: New Delhi, Dekhi, India.
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Anantsharma Send a message via Yahoo to Anantsharma
Default

HI,

Use COLLATE at Left side of comparison.

B. Anant
Reply With Quote
  #3 (permalink)  
Old October 19th, 2004, 04:25 AM
Registered User
 
Join Date: Dec 2003
Location: Bangalore, Karntaka, India.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #4 (permalink)  
Old October 19th, 2004, 04:43 AM
Friend of Wrox
 
Join Date: Sep 2004
Location: New Delhi, Dekhi, India.
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Anantsharma Send a message via Yahoo to Anantsharma
Default

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
Reply With Quote
  #5 (permalink)  
Old October 19th, 2004, 05:31 AM
Friend of Wrox
 
Join Date: Sep 2004
Location: New Delhi, Dekhi, India.
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Anantsharma Send a message via Yahoo to Anantsharma
Default

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
Reply With Quote
  #6 (permalink)  
Old October 19th, 2004, 05:35 AM
Registered User
 
Join Date: Dec 2003
Location: Bangalore, Karntaka, India.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #7 (permalink)  
Old October 19th, 2004, 06:01 AM
Friend of Wrox
 
Join Date: Sep 2004
Location: New Delhi, Dekhi, India.
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Anantsharma Send a message via Yahoo to Anantsharma
Default

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
Reply With Quote
  #8 (permalink)  
Old October 19th, 2004, 07:10 AM
Registered User
 
Join Date: Dec 2003
Location: Bangalore, Karntaka, India.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Anant,

I will try this.

Thanks for your support.

Regards
Sudhakara.T.P.
Reply With Quote
  #9 (permalink)  
Old September 6th, 2018, 10:39 AM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2018
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Help

Hey guys, could anybody recommend some good online courses for learning SQL? I'm trying to find something cool but still couldn't. I need it to operate with really big libraries to improve my content on my new site.
Reply With Quote
  #10 (permalink)  
Old September 6th, 2018, 02:16 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default Don't bother... previous poster is a low life spammer

Title of this post says it all...
__________________
--Jeff Moden
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Compound Select Statements mphare XSLT 4 October 14th, 2008 11:31 PM
code on page 218 two select statements in one uvis130 BOOK: Access 2007 VBA Programmer's Reference ISBN: 978-0-470-04703-3 0 September 3rd, 2007 07:56 AM
CASE Statements in T-SQL atcs2152 SQL Server 2000 3 April 28th, 2006 11:53 AM
Case Select Statements & Updating the Database jackiew General .NET 1 April 11th, 2006 12:47 PM
SQL Statements marmer Classic ASP Basics 3 November 13th, 2003 01:42 AM



All times are GMT -4. The time now is 08:58 AM.


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