Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Removing 2000 Compatibility Mode - New Problem Expand / Collapse
Author
Message
Posted Tuesday, March 25, 2014 5:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:24 PM
Points: 2,829, Visits: 8,477
We came across a "gotcha" while testing our application without being in 2000 compatibility mode. We fixed all the *= left join syntax, but in another stored procedure got the error:

ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

The problem is that in our SELECT statement we convert a date to varchar, but then also want to use that column in the ORDER BY. But since it is not included in the select as a standalone field, we get the error.

SELECT DISTINCT                    
a.JOB_ID,
a.Fld2,
a.Fld3,
CONVERT(datetime, CONVERT(varchar(10), a.JOB_Date, 101)) AS JOB_Date,
UNION
SELECT DISTINCT
a.JOB_ID,
a.Fld2,
a.Fld3,
CONVERT(datetime, CONVERT(varchar(10), a.JOB_Date, 101)) AS JOB_Date,
ORDER BY a.JOB_Date

If I remove the alias from Job_Date in the order by, then it works in 2008 mode

Any ideas how to search for similar situations ??

I suppose I can scan all our code for: UNION, INTERSECT or EXCEPT



Post #1554383
Posted Tuesday, March 25, 2014 5:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 5:09 AM
Points: 848, Visits: 5,488
Try 'ORDER BY JOB_Date' not 'ORDER BY a.JOB_Date'.
The query should then be order by the JOB_Date expression.

Post #1554396
Posted Tuesday, March 25, 2014 6:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
Any luck with UPGRADE ADVISOR?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1554418
Posted Tuesday, March 25, 2014 7:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:24 PM
Points: 2,829, Visits: 8,477
Yes, removing the alias works, but it seems strange that it is not valid code. Upgrade Advisor won't work because we have Cold Fusion and PHP code outside of SQL.


Post #1554445
Posted Tuesday, March 25, 2014 9:27 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 3,545, Visits: 7,648
The reason that makes the query work without the alias is that a.JOB_Date is different to JOB_Date which is the column alias.
I'm not sure how would you be able to look for this problems. A third party tool might work, but I don't work with any.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1554542
Posted Tuesday, March 25, 2014 10:20 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:37 PM
Points: 23,292, Visits: 32,021
homebrew01 (3/25/2014)
We came across a "gotcha" while testing our application without being in 2000 compatibility mode. We fixed all the *= left join syntax, but in another stored procedure got the error:

ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

The problem is that in our SELECT statement we convert a date to varchar, but then also want to use that column in the ORDER BY. But since it is not included in the select as a standalone field, we get the error.

SELECT DISTINCT                    
a.JOB_ID,
a.Fld2,
a.Fld3,
CONVERT(datetime, CONVERT(varchar(10), a.JOB_Date, 101)) AS JOB_Date,
UNION
SELECT DISTINCT
a.JOB_ID,
a.Fld2,
a.Fld3,
CONVERT(datetime, CONVERT(varchar(10), a.JOB_Date, 101)) AS JOB_Date,
ORDER BY a.JOB_Date

If I remove the alias from Job_Date in the order by, then it works in 2008 mode

Any ideas how to search for similar situations ??

I suppose I can scan all our code for: UNION, INTERSECT or EXCEPT


You couldn't use a.JOB_DATE in the order by clause because it isn't a column in the table aliased by a, it is a column alias for the CONVERT function in your select list.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1554573
Posted Tuesday, March 25, 2014 10:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:24 PM
Points: 2,829, Visits: 8,477
My snippet is not very clear.

Job_Date is a DATETIME column in the table, and it's also getting named as a field as a result of the convert. So a.Job_Date exists, but not listed as part of the SELECT clause, so I guess in 2008, a UNION needs the columns selected if they are going to be used in the ORDER BY ??

The code works in 2000, but not 2008.



Post #1554587
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse