Expression type int is invalid for COLLATE clause

anushahanna
anushahanna used Ask the Experts™
on
When I run the following in SQL Server it runs fine:
select * from TableA order by First_Candidate collate SQL_Latin1_General_CP1_CI_AS

But when I do the order by the ordering of the column, then I get the error:
"Expression type int is invalid for COLLATE clause. "
select * from TableA order by 7 collate SQL_Latin1_General_CP1_CI_AS

How can you overcome this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

The COLLATE clause can be applied only for the char, varchar, text, nchar, nvarchar, and ntext data types.
Can you try replacing the "ORDER BY 7" with "ORDER BY <Name of column>"?
 
Top Expert 2010

Commented:
Silly question: is First_Candidate the 7th column in TableA, or is some other column in the 7th position?

If it's someother column, then I imagine that that column is [n][var]char, for which specifying a collation makes sense...
COLLATE is applicable only for columns of the char, varchar, nchar, and nvarchar data types.

For more info:
http://msdn.microsoft.com/en-us/library/ms188385.aspx
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Author

Commented:
Yes, the 7th column is varchar. that is my point- as to why i am not able to just say order by 7 with the collate.
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> as to why i am not able to just say order by 7 with the collate.

column order no. is added to simplify typing your large column names..
In order to specify collate keyword or any other operation, you need to specify the column name in order to work..

Author

Commented:
ok. thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial