Views with Order By not in Select List
mikeATCompuware
Posts: 9
I noticed that if you have a View which has an item in the Order by list, but not in the select list the script will error out. For example:
CREATE VIEW user.view
AS
SELECT TOP 3 Id, Naam, Host, Woonplaats, IdAbon, WidthH, HeightH, YesCount, Datum
FROM user.table
WHERE (IdAbon = 3) AND (Foto = 1)
ORDER BY (YesCount+Datum)
Will give you the following error:
"Error 4511: Could not perform CREATE VIEW because no column name was specified for column X"
It is possible to make these views, however it needs to be created first and then Altered to the final select statement (the check only happens on create). I know it might be difficult to know in advance whether a statement would produce this error, but it would be really nice if SQL Compare would create a dummy view first, then alter it with the correct view statement in these situations.
Anyways, now I have to manually edit the scripts to make it work.
CREATE VIEW user.view
AS
SELECT TOP 3 Id, Naam, Host, Woonplaats, IdAbon, WidthH, HeightH, YesCount, Datum
FROM user.table
WHERE (IdAbon = 3) AND (Foto = 1)
ORDER BY (YesCount+Datum)
Will give you the following error:
"Error 4511: Could not perform CREATE VIEW because no column name was specified for column X"
It is possible to make these views, however it needs to be created first and then Altered to the final select statement (the check only happens on create). I know it might be difficult to know in advance whether a statement would produce this error, but it would be really nice if SQL Compare would create a dummy view first, then alter it with the correct view statement in these situations.
Anyways, now I have to manually edit the scripts to make it work.
Comments
We can have a look into this. It would seem SQL Server's syntax checker has a bug if it's fickle and changes its mind to allow something when you alter a view that it wouldn't allow on create view!
There is a section in the help about making scripts SQL 7 compatible. I don't believe this is addressed in there so we'll have to look into adding it for sure!