Last post Dec 05, 2016 02:59 AM by wmec
Dec 02, 2016 05:52 AM|Nishantg|LINK
I have a requirement as follows for a multilingual application, where the columns displayed on a search are picked up from configuration, and the data can be sorted based on configuration.
Question 1 - I am using a Select query like this:
"SELECT * FROM TABLE ORDER BY 1 COLLATE SQL_Latin1_General_CP1_CI_AS DESC, ORDER BY 4 COLLATE
Getting an error "Expression type int is invalid for COLLATE clause."
Question 2 - I would also need to support integer column based sorting like "ID"
But get the same error as mentioned for question 1
Are there any workaround to support both use-cases.
Or is this an SQL Server limitation that we have to live with even with the latest version of SQL Server 2016?
Would appreciate a quick response.
Thanks & Regards,
Dec 02, 2016 07:59 AM|eralper|LINK
Please note that COLLATION property can be applied only to character data types
So you cannot use COLLATE with int, bigint, etc data types only on varchar and nvarchar, etc character data types can be used with collation property
In fact you don't need collation for numeric values, 1,3,6 numbers are all same in every culture or language
Please check following Select
Use field names and do not use ORDER BY repeatedly
USERNAME COLLATE SQL_Latin1_General_CP1_CI_AS DESC,
I hope it helps
Dec 05, 2016 02:59 AM|wmec|LINK
Please try to remove 'Collate' option to the Order by clause.