Removing 2000 Compatibility Mode - New Problem

Question

Post reply

Removing 2000 Compatibility Mode - New Problem

homebrew01

SSC Guru

Points: 55548
More actions
March 25, 2014 at 5:29 am

#284923

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:
[font="Courier New"]ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.[/font]
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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18842 More actions · Answer 1

Try 'ORDER BY JOB_Date' not 'ORDER BY a.JOB_Date'.

The query should then be order by the JOB_Date expression.

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

Any luck with UPGRADE ADVISOR?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

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.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

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.
General Disclaimer:
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?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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:
[font="Courier New"]ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.[/font]
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.

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

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.