x

Non integer constants are not allowed in ORDER BY clause in 90 or later compatibility modes

HEllo All,

For one of the procedure Upgarde Advisor has thrown this error.

"Non integer constants are not allowed in ORDER BY clause in 90 or later compatibility modes"

Please let me know hoew to solve this

Procedure :-

ORDER BY 'MYFullNAME (Last Name/First Name)',A.CreationDate

Thanks Naina

more ▼

asked Jun 15, 2010 at 05:06 AM in Default

avatar image

Naina
63 13 13 16

Thanks a lot Kev......for expalining this so nicely...thanks :) Thanks ThomasRushton

In one of the Procedure they have done like

ORDER BY "MYFullName (Last Name/First Name)",A.CreationDate .......Can we use " (double quotes ) as well.

Jun 16, 2010 at 08:41 AM Naina
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

assuming that your non-integer constant 'MYFullNAME (Last Name/First Name)' is actually a column alias....

replace the non-integer constant with whatever the column is in the select

so

select
 a.firstname + ' ' + a.lastname as 'MYFullNAME (Last Name/First Name)',
 a.creationdate
from
 mytable a
order by 
 'MYFullNAME (Last Name/First Name)',
 a.creationdate

becomes

select
 a.firstname + ' ' + a.lastname as 'MYFullNAME (Last Name/First Name)',
 a.creationdate
from
 mytable a
order by 
 a.firstname + ' ' + a.lastname,
 a.creationdate

or

select
 a.firstname + ' ' + a.lastname as 'MYFullNAME (Last Name/First Name)',
 a.creationdate
from
 mytable a
order by 
 [MYFullNAME (Last Name/First Name)],
 a.creationdate
more ▼

answered Jun 15, 2010 at 05:53 AM

avatar image

Kev Riley ♦♦
65.2k 48 62 81

Or even "ORDER BY 1,2"...

Jun 15, 2010 at 05:56 AM ThomasRushton ♦♦

Thomas: that is also a workaround, but I wouldn't recommend it as best practice for production code - a change to the select list would change the order of the output - explicitly defining the order by ensures that the order is always maintained.

Jun 15, 2010 at 07:52 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2137
x1076

asked: Jun 15, 2010 at 05:06 AM

Seen: 4126 times

Last Updated: Jun 15, 2010 at 05:06 AM

Copyright 2016 Redgate Software. Privacy Policy