July 31, 2009 at 1:47 pm
Hello,
I'm upgrading sql server 2000 to 2005 and I ran upgrade adviser and it giving me the below warning after the analysis:
In SQL Server 2005 Column aliases in the ORDER BY clause cannot be prefixed by the table alias
Effected Objects:
Type: Database
Database name: Dbname
Object name:VIEW dbo.View_abcValue
Object type: V
Here is the view its complaining. Please advice what should I exactly change and this change should be done before changing the compatibility to 90? OR After changing to 90?
CREATE VIEW dbo.View_abcValue
AS
SELECT TOP 100 PERCENT View_abc.ObjectID AS [PropertyID], CBZ_Object.ObjectID AS [ObjectID], View_abc.ObjectName AS PropertyName,
View_abc.ObjectAlias AS PropertyAlias, View_abc.ObjectTypeNamespace AS PropertyNamespace,
View_abc.ObjectTypeAlias AS PropertyTypeAlias, CBZ_Object.ObjectName AS ObjectName,
CBZ_Object.ObjectAlias AS ObjectAlias, CBZ_ObjectType.ObjectTypeAlias AS ObjectTypeAlias,
CBZ_PropertyValue.PropertyValue AS PropertyValue
FROM View_abc
INNER JOIN CBZ_ObjectType ON View_abc.ObjectTypeNamespace = CBZ_ObjectType.ObjectTypeNamespace
INNER JOIN CBZ_Object ON dbo.CBZ_ObjectType.ObjectTypeID = dbo.CBZ_Object.ObjectTypeID
LEFT OUTER JOIN CBZ_PropertyValue ON CBZ_PropertyValue.PropertyID = View_abc.ObjectID AND CBZ_PropertyValue.ObjectID = CBZ_Object.ObjectID
ORDER BY View_abc.PropertyName, View_abc.PropertyAlias
July 31, 2009 at 2:14 pm
It's complaining about the two-part naming in the order by, but you should just remove the order by and the Top 100 % completely. Order by is not honoured in a view, the ordering should go in the outer select statement, ie the one that queries the view
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 31, 2009 at 3:15 pm
I beleive what it is actually complaining about is that it wants you to use the original column name in the order by and not the alias that is set in the select clause
EG - use view_abc.objectname instead of view_abc.propertyname and use view_abc.objectalias instead of view_abc.propertyalias
Those changes would get you by.
it just wants you to reference the true column names in the order by, not the aliases from the select.
That being said, Gails advice on how to handle the situation is much better advice.
July 31, 2009 at 3:58 pm
Thanks,
I need to change last line in code as below right?
Alter VIEW dbo.View_abcValue
AS
SELECT TOP 100 PERCENT View_abc.ObjectID AS [PropertyID], CBZ_Object.ObjectID AS [ObjectID], View_abc.ObjectName AS PropertyName,
View_abc.ObjectAlias AS PropertyAlias, View_abc.ObjectTypeNamespace AS PropertyNamespace,
View_abc.ObjectTypeAlias AS PropertyTypeAlias, CBZ_Object.ObjectName AS ObjectName,
CBZ_Object.ObjectAlias AS ObjectAlias, CBZ_ObjectType.ObjectTypeAlias AS ObjectTypeAlias,
CBZ_PropertyValue.PropertyValue AS PropertyValue
FROM View_abc
INNER JOIN CBZ_ObjectType ON View_abc.ObjectTypeNamespace = CBZ_ObjectType.ObjectTypeNamespace
INNER JOIN CBZ_Object ON dbo.CBZ_ObjectType.ObjectTypeID = dbo.CBZ_Object.ObjectTypeID
LEFT OUTER JOIN CBZ_PropertyValue ON CBZ_PropertyValue.PropertyID = View_abc.ObjectID AND CBZ_PropertyValue.ObjectID = CBZ_Object.ObjectID
ORDER BY View_abc.ObjectName, View_abc.ObjectAlias
August 1, 2009 at 2:01 am
As I said, you should take the order by out completely. In SQL 2005 and above, order by in a view is not honoured. SQL will ignore that TOP 100% ... ORDER BY.
Take the order by out and put it into the outer select statement - the one that retrieves from the view.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2009 at 2:04 am
Adam Angelini (7/31/2009)
I beleive what it is actually complaining about is that it wants you to use the original column name in the order by and not the alias that is set in the select clause
Not quite. Aliases are quite valid in an order by, only place they are. What it's saying is that the alias name cannot be preceded by the table name. So either of these is fine
Order by View_abc.ObjectName, View_abc.ObjectAlias
or
Order by PropertyName, PropertyAlias
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2009 at 5:28 am
My Question here is does this code works in SQl Server 2000, i guess it wont work in SQL Server 2000 also, becuase you are mapping to reference of object which is not there in your selection.
However you can achieve this by this method also:
Alter VIEW dbo.View_abcValue
AS
SELECT TOP 100 PERCENT View_abc.ObjectID AS [PropertyID], CBZ_Object.ObjectID AS [ObjectID], View_abc.ObjectName AS PropertyName,
View_abc.ObjectAlias AS PropertyAlias, View_abc.ObjectTypeNamespace AS PropertyNamespace,
View_abc.ObjectTypeAlias AS PropertyTypeAlias, CBZ_Object.ObjectName AS ObjectName,
CBZ_Object.ObjectAlias AS ObjectAlias, CBZ_ObjectType.ObjectTypeAlias AS ObjectTypeAlias,
CBZ_PropertyValue.PropertyValue AS PropertyValue
FROM View_abc
INNER JOIN CBZ_ObjectType ON View_abc.ObjectTypeNamespace = CBZ_ObjectType.ObjectTypeNamespace
INNER JOIN CBZ_Object ON dbo.CBZ_ObjectType.ObjectTypeID = dbo.CBZ_Object.ObjectTypeID
LEFT OUTER JOIN CBZ_PropertyValue ON CBZ_PropertyValue.PropertyID = View_abc.ObjectID AND CBZ_PropertyValue.ObjectID = CBZ_Object.ObjectID
ORDER BY View_abcValue.ObjectName, View_abcValue.ObjectAlias
Note: Gila, Please correct me if i am wrong, we can use ORDER BY in SQL Server 2000,2005,2008... with TOP command.Though, ORDER BY clause does not guarantee the results ordered.
Manoj
August 1, 2009 at 6:39 am
manoj2001 (8/1/2009)
My Question here is does this code works in SQl Server 2000
That code will work in SQL 2000. The SQL 2000 parser was rather lax in what it allowed for order by statements, which occasionally produced very unexpected results.
Note: Gila, Please correct me if i am wrong, we can use ORDER BY in SQL Server 2000,2005,2008... with TOP command.Though, ORDER BY clause does not guarantee the results ordered.
Sure you can use it, it's a legal set of commands, but since SQL will completely ignore the order by when the TOP does not limit rows (top 100 percent), there's no point having it there and it's just going to confuse people when they see an order by that doesn't order the rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply