Invalid column name

Below is the T-SQL

SELECT ci.name,
ci.address,
CASE ISNULL(cci.geography,'')
WHEN 'P' THEN 'port'
WHEN 'A' THEN 'appt'
WHEN 'X' THEN 'xatt'
ELSE ''
END AS Link,
ci.InsuranceType
FROM dbo.Insurance ci
INNER JOIN dbo.Contract cci
ON ci.InsuranceId = cci.InsuranceId
AND cci.ContractId = 1266
ORDER BY
ci.Link,
ci.InsuranceType

===========================

My work environment is all in SQL SERVER 2005.

I have a problem with above T-SQL. There is an error while I run above code in Develoment Server.The error disappears when I put 'ci.Link' as 'Link' in OrderBy clause. My work environment has TEST, PRE-PRODUCTION and PRODUCTION. In all other environments the code runs fine with ci.Link but only in DEVELOPMENT the error persists. It has an error as 'Link is invalid column name'.

Hi madhav,

You could use is clause:

SELECT ...
ORDER BY 2 ASC, 3 ASC;

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

Thanks Javier for your response. My all work environments are same( from DEV to PROD). Without adding a single line or changing anything, above code runs fine in all but fails only in DEVELOPMENT. But runs if you put as Link only as mentioned above.

|||

Cool.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

The error happens because of invalid use of expressions in the ORDER BY clause. Older versions had several bugs where expressions in ORDER BY clause was allowed or wrongly evaluated to those in the FROM clause. But SQL Server 2005 corrects the behavior and is more closer towards standard SQL syntax. And depending on compatibility level (<90) the query will work fine. So on your servers other than DEVELOPMENT the database compatibility level is not 90 hence the query runs fine. See below link for more details (search for "ORDER BY clause"):

http://msdn2.microsoft.com/en-us/library/ms143359.aspx

sp_dbcmptlevel topic:

http://msdn2.microsoft.com/en-us/library/ms178653.aspx

|||

Your answer solved the problem. Much appreciated.