Strange error - SQL Server Forums

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)

Strange error

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-05-19 : 05:42:09

SELECT TOP 9 id,vehicleref,capid,manufacturer,model,derivative,ch,'3' as term,'10000' as mileage,'orderby' as orderby,cvehicle_shortmodtext,source,studioimages FROM vwAllMatrixWithLombardAndShortModel
WHERE locatcode =1
GROUP BY id,vehicleref,capid,manufacturer,model,derivative,ch,term,mileage,orderby,cvehicle_shortmodtext,source,studioimages

Msg 207, Level 16, State 1, Line 2
Invalid column name 'mileage'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'orderby'.

Can anyone help?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-19 : 05:45:24

No need to group by mileage, term and orderby, since they are very much static.
Delete these three column names from GROUP BY statement.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-19 : 06:33:42

quote:
Originally posted by Mondeo

SELECT TOP 9 id,vehicleref,capid,manufacturer,model,derivative,ch,'3' as term,'10000' as mileage,'orderby' as orderby,cvehicle_shortmodtext,source,studioimages FROM vwAllMatrixWithLombardAndShortModel
WHERE locatcode =1
GROUP BY id,vehicleref,capid,manufacturer,model,derivative,ch,term,mileage,orderby,cvehicle_shortmodtext,source,studioimages

Msg 207, Level 16, State 1, Line 2
Invalid column name 'mileage'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'orderby'.

Can anyone help?

Thanks

Cant use aliases in group by. You need the keep the main select inside a derived table and then do GROUP BY outside

SELECT * FROM
(
SELECT TOP 9 id,vehicleref,capid,manufacturer,model,derivative,ch,'3' as term,'10000' as mileage,'orderby' as orderby,cvehicle_shortmodtext,source,studioimages FROM vwAllMatrixWithLombardAndShortModel 
WHERE locatcode =1 )t
GROUP BY id,vehicleref,capid,manufacturer,model,derivative,ch,term,mileage,orderby,cvehicle_shortmodtext,source,studioimages

Also as Peso suggested you dont reuire three static fields in GROUP By

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources