A constant expression was encountered in the ORDER

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

A constant expression was encountered in the ORDER

Author

Topic

soonyu
Starting Member

13 Posts

Posted - 2009-02-25 : 04:11:22

SELECT * FROM company u (NOLOCK) WHERE ORDER BY 'u.name'

Server: Msg 408, Level 16, State 1, Line 1
A constant expression was encountered in the ORDER BY list, position 1.

I know we can successful running the query with take out "'" from query above, but it quite difficult because of dynamic sql generate from the software framework and the problem is why this sucessful in Sql 2000 but not Sql 2005

above line ok in sql 2000, but not in sql 2005
my sql version is
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

any help?

Cheers,

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-25 : 04:13:47

Check this once,

declare @t table (id int , ename varchar(32))

insert into @t select 12, 'bklr'
insert into @t select 6, 'raju'
insert into @t select 2, 'hari'
insert into @t select 4, 'nag'
insert into @t select 1, 'chiru'
insert into @t select 12, 'bklr'

select * from @t order by 'ename'

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-25 : 04:15:25

Suppose u put an alias name for table , that is not work for 'u.name'

Like

select * from company c order by 'c.name' ( it doesn't work)

suppose u write,

select * from company c order by 'name' ( it works )

soonyu
Starting Member

13 Posts

Posted - 2009-02-25 : 04:19:27

quote:
Originally posted by Nageswar9

Check this once,

declare @t table (id int , ename varchar(32))

insert into @t select 12, 'bklr'
insert into @t select 6, 'raju'
insert into @t select 2, 'hari'
insert into @t select 4, 'nag'
insert into @t select 1, 'chiru'
insert into @t select 12, 'bklr'

select * from @t order by 'ename'

no problem
but if run with

select * from @t t order by 'u.ename'

then hit problem

Cheers

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-25 : 04:21:17

quote:
Originally posted by soonyu

quote:
Originally posted by Nageswar9

Check this once,

declare @t table (id int , ename varchar(32))

insert into @t select 12, 'bklr'
insert into @t select 6, 'raju'
insert into @t select 2, 'hari'
insert into @t select 4, 'nag'
insert into @t select 1, 'chiru'
insert into @t select 12, 'bklr'

select * from @t order by 'ename'

no problem
but if run with

select * from @t t order by ' u.ename'

then hit problem

Cheers

soonyu
Starting Member

13 Posts

Posted - 2009-02-25 : 04:27:08

quote:
Originally posted by Nageswar9

Suppose u put an alias name for table , that is not work for 'u.name'

Like

select * from company c order by 'c.name' ( it doesn't work)

suppose u write,

select * from company c order by 'name' ( it works )

quote:

refer to the above.
this work good in sql 2000, but not in sql 2005

early i also say the same thing.
so developer throw this question
we have so many sqlserver,
why it work in sql 2000?

nishita_s
Yak Posting Veteran

61 Posts

Posted - 2009-02-25 : 06:37:37

execute this SELECT * FROM company u (NOLOCK) WHERE ORDER BY 'name'

soonyu
Starting Member

13 Posts

Posted - 2009-02-25 : 21:08:24

quote:
Originally posted by nishita_s

execute this SELECT * FROM company u (NOLOCK) WHERE ORDER BY 'name'

i know the above.

my problem is
SELECT * FROM company u (NOLOCK) WHERE ORDER BY 'u.name'
work for sql 2000
but not for sql 2005 9.00.3054.00

bjoerns
Posting Yak Master

154 Posts

Posted - 2009-02-26 : 06:17:35

WHERE is the WHERE-clause?

If none required,

SELECT *
FROM company WITH(NOLOCK)
ORDER BY [Name]

will work for sure in 2005.

soonyu
Starting Member

13 Posts

Posted - 2009-03-02 : 20:26:34

We should NOT have single quotation in column name which use behind ORDDER BY syntax
It won't perform as expected in SQL 2000 and will throw error in SQL 2005

we should write query in this way ORDER BY u.name
should not have single quotation between the fieldname in order by ORDER BY 'u.name'

when we run the below query in SQL 2000
SELECT u.* FROM user u (NOLOCK) ORDER BY 'u.name'

SQL 2000 will ONLY execute the below query only and won't return result as our expected
SELECT u.* FROM user u (NOLOCK)

SQL 2005 will throw error.
"Server: Msg 408, Level 16, State 1, Line 1
A constant expression was encountered in the ORDER BY list, position 1."
The error message was introduced with SQL 2005 and will treat 'u.name' as constant instead column in table.
reference : http://www.sql-server-performance.com/faq/error408_p1.aspx

We need to take note on capatibility level of database as well
I run the query
SELECT u.* FROM user u (NOLOCK) ORDER BY 'u.name'
in some of my sql 2005 server and didn't hit error and found because i attached the SQL 2000 database to SQL 2005 and the Compatibility level for the database is SQL2000 (80) where the database will partial backward compatibility with earlier versions of SQL Server
If I change the Compatibility level for this database to SQL 2005 (90), the query will throw the error as well

FYI, Compatibility is database level, not for the entire server and can change in SQL Server.

reference : http://blog.sqlauthority.com/2007/05/29/sql-server-2005-change-database-compatible-level-backward-compatibility/

The point is, if we put A constant expression in ORDER BY LIST, it actually won't return the exepected
result in SQL 2000 even didn't throw the error.

example :
declare @t table (id int , ename varchar(32))

insert into @t select 12, 'bklr'
insert into @t select 6, 'raju'
insert into @t select 2, 'hari'
insert into @t select 4, 'nag'
insert into @t select 1, 'chiru'
insert into @t select 12, 'bklr'

--query 1
select * from @t t order by 't.ename'
--"SQL 2000 actually ignore the sort by command and the result set didn't sort by ename"
--"SQL 2005 will throw error and tell use the result won't perform as expected"

-- correct SQL statement should be
select * from @t t order by t.ename

--query 2
select * from @t t order by '1'
--"SQL 2000 actually ignore the sort by command and the result set didn't sort by Id where we assume it might"
--"SQL 2005 will throw error and tell use the result won't perform as expected"

--correct SQL Statement should be
select * from @t t order by 1

--the return result for both query, which are not as our expected
12 bklr
6 raju
2 hari
4 nag
1 chiru
12 bklr

--Expected result for query 1 should as below :
id ename
----------- -------
12 bklr
12 bklr
1 chiru
2 hari
4 nag
6 raju

--Expected result for query 2 should as below:
id ename
----------- -------
1 chiru
2 hari
4 nag
6 raju
12 bklr
12 bklr

Reference :
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/9b1d0188-4494-42a6-af4d-5fe24bb0897f/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources