September 21, 2012 at 8:24 am
(received this from a developer - Anything stand out here??)
This statement used to run with the COLLATE statement, now..
When I run this SQL:
declare @acctcorp int, @house char(3), @cust char(3)
select distinct wpcnt from tmp_DST_DOR_Activity_Table_raw_data_wtg
where case
when ftax_acctcorp is not null then 16591
when acctcorp = 9519 and ftax in (1,2,3,4,5) then 95191
else acctcorp end = @acctcorp and house = @house and cust = @cust
order by wpcnt collate SQL_Latin1_General_CP1_CS_AS
I receive:
Msg 145, Level 15, State 1, Line 4
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
If I comment out the COLLATE (eg. order by wpcnt --collate SQL_Latin1_General_CP1_CS_AS)
it runs but the time to complete is 40 min's instead of the 3 min's it used to take with thje COLLATE in there. Thoughts? Am I missing something here?
September 21, 2012 at 8:27 am
Seems strange. Can you check/post the execution plan in both cases?
September 21, 2012 at 8:32 am
Execution plan (when COLLATE is commented out) shows 99% of work being done as TABLE SCAN on table tmp_DST_DOR_Activity_Table_raw_data_wtg w/ 243k rows in it...
Execution plan fails when I UN-comment the COLLATION statement. With same error:
Msg 145, Level 15, State 1, Line 3
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
September 21, 2012 at 8:44 am
Solution: I changed the ORDER BY / COLLATE statement to:
order by wpcnt COLLATE DATABASE_DEFAULT
and seems to have resolved my issue.. thanks
September 21, 2012 at 9:26 am
It may have let your query run but I don't think it solved your issue. The problem is that the order by specifies a case sensitive sort order but the distinct on the column name is using a different collation. For this to work correctly you need to have the same collation on the column you are selecting as you have on the order by.
Try this:
select distinct wpcnt collate SQL_Latin1_General_CP1_CS_AS
from tmp_DST_DOR_Activity_Table_raw_data_wtg
where case
when ftax_acctcorp is not null then 16591
when acctcorp = 9519 and ftax in (1,2,3,4,5) then 95191
else acctcorp end = @acctcorp and house = @house and cust = @cust
order by wpcnt collate SQL_Latin1_General_CP1_CS_AS
The results should now include distinct case sensitivity which I think must be the reason your dev added that to the sort. Otherwise there is no reason to use collation in the order by at all.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply