SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 help with query, wierd error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike123
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 06/07/2005 :  19:40:10  Show Profile  Reply with Quote
I'm just modifying the ORDER BY clause on a pre-existing working query and getting the following error:

Server: Msg 207, Level 16, State 3, Procedure select_Media_FrontPage_TopRated, Line 8
Invalid column name 'votes'.

When I order by VOTES or POINTS on their own, rather than (points/votes) the query works fine. Is this a hint to any possible problem?

Thanks once again! :)
mike123



alter PROCEDURE dbo.select_Media_FrontPage_TopRated

AS

SET NOCOUNT ON


SELECT
M.mediaID,
pageName,
mediaTitle,
mediaDesc,
datePosted,
COUNT(UV.mediaID) AS votes,
SUM (
CASE
WHEN points IS NULL
THEN 0
ELSE points
END ) as points
FROM
tblMedia M
LEFT OUTER JOIN tblUserVote UV ON UV.mediaID = M.mediaID

WHERE m.activeStatus ='1'

GROUP BY

M.mediaID,
pageName,
mediaTitle,
mediaDesc,
datePosted

ORDER BY
(points / votes) DESC







GO

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 06/07/2005 :  19:43:02  Show Profile  Visit robvolk's Homepage  Reply with Quote
Votes is a calcuation, not an actual column, so it cannot participate in another calculation. Either replace it in the ORDER BY with Count(UV.mediaID) or wrap the whole thing in a subquery, then put the ORDER BY outside of it.

Don't ask me why it worked before, it shouldn't have.
Go to Top of Page

mike123
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 06/07/2005 :  20:11:31  Show Profile  Reply with Quote
Hey Rob thanks for the hand.

I added what you said

Count(UV.mediaID)

Server: Msg 8127, Level 16, State 1, Procedure select_Media_FrontPage_TopRated, Line 8
Column name 'UV.points' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

I got this error and then after I fix what it recommends my query ends up bringing back 258 rows instead of the 40 it should. It's doing this because of something screwy in the groupby clause I believe.

I'd like to try your subquery method but I haven't done one in so long I'm not sure I understand the approach on this one. One other thing that I realized I needed to add a simple "WHERE VOTES > 5" , simple obviously in most cases but it was causing me complications in this one.

Thanks very much once again!
mike123

Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 06/07/2005 :  20:22:21  Show Profile  Visit robvolk's Homepage  Reply with Quote
This should do it:

alter PROCEDURE dbo.select_Media_FrontPage_TopRated

AS

SET NOCOUNT ON

SELECT * FROM (
SELECT
M.mediaID,
pageName,
mediaTitle,
mediaDesc,
datePosted,
COUNT(UV.mediaID) AS votes,
SUM (
CASE
WHEN points IS NULL
THEN 0
ELSE points
END ) as points
FROM
tblMedia M
LEFT OUTER JOIN tblUserVote UV ON UV.mediaID = M.mediaID

WHERE m.activeStatus ='1'

GROUP BY

M.mediaID,
pageName,
mediaTitle,
mediaDesc,
datePosted ) a

ORDER BY
(a.points / a.votes) DESC
Go to Top of Page

mike123
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 06/07/2005 :  22:51:25  Show Profile  Reply with Quote
thanks alot rob! ..

just one quick question, is it ok to do SELECT *'s on subquery's ? Or are you just writing it quickly and I should know better? :)


thx!
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 06/07/2005 :  23:49:48  Show Profile  Visit robvolk's Homepage  Reply with Quote


There was some debate about that recently:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50761

I don't have a preference either way, I don't think SELECT * will cause you any problems here.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000