weird error in SPROC - 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 2000 Forums

Transact-SQL (2000)

weird error in SPROC

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-07-07 : 00:59:24

I'm trying to create the procedure below, but I'm getting a wierd error that I don't understand.

This procedure is a slightly modified version of a perfectly working one. The modifications where in the ORDER BY clause, I had to change this one to order by (votes/points) so it has complicated things a little bit.

Thanks again for any input!
mike123

Server: Msg 207, Level 16, State 3, Procedure select_MediaList_Page_TopRated, Line 23
Invalid column name 'votes'.
Server: Msg 207, Level 16, State 1, Procedure select_MediaList_Page_TopRated, Line 23
Invalid column name 'votes'.

Line 23 for reference is the INSERT line

alter PROCEDURE dbo.select_MediaList_Page_TopRated
(
@Page smallint,
@RecsPerPage tinyint
)
AS
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempMedia
(
ID int IDENTITY,
mediaID int,
pageName varchar(100),
mediaTitle varchar(200),
mediaDesc varchar(500),
datePosted datetime,
votes int,
points int
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempMedia (mediaID, pageName, mediaTitle, mediaDesc, datePosted, votes, points)

SELECT * FROM(
SELECT
M.mediaID,
-- M.categoryID,
-- Cat.categoryDesc,
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' and votes > 5

GROUP BY
M.mediaID,
pageName,
mediaTitle,
mediaDesc,
datePosted) a
ORDER BY
a.points / a.votes DESC

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(mediaID)
FROM #TempMedia TM
WHERE TM.ID >= @LastRec
)
FROM #TempMedia
WHERE ID > @FirstRec AND ID < @LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

GO

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-07-07 : 05:57:11

enumerate the fields from your select *, then remove the alias a for votes and points

--------------------
keeping it simple...

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-07-07 : 18:30:22

Hi Jen,

Thanks for the help, but I'm not completely following you. I haven't done anything like this before could you provide an example ?

thanks very much !:)
mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-07 : 18:40:38

Without the DDL of the two tables involved, we can't help you quickly, so you'll have to do the testing. Hint, hint...

Anyway, let's take a step back from the stored procedure. Try running this inside Query Analyzer:



CREATE TABLE #TempMedia
(
	ID int IDENTITY,
	mediaID int,
	pageName varchar(100),
	mediaTitle varchar(200),
	mediaDesc varchar(500),
	datePosted datetime,
	votes int,
	points int
)

INSERT INTO #TempMedia (mediaID, pageName, mediaTitle, mediaDesc, datePosted, votes, points)
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' and votes > 5
	GROUP BY M.mediaID, pageName, mediaTitle, mediaDesc, datePosted
) a

SELECT *
FROM #TempMedia

Do you get errors? If so, please post them.

What jen meant by enumerating your select * is to provide an explicit column list in the select portion of the insert query. Then your ORDER BY becomes ORDER BY points/votes DESC.

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-07-07 : 19:02:02

Hi Tara,

Thanks for the hand here. Yes I do get errors with that query you posted. The error is

Server: Msg 207, Level 16, State 3, Line 13
Invalid column name 'votes'.
Server: Msg 207, Level 16, State 1, Line 13
Invalid column name 'votes'.

Line 13 being the INSERT once again.

I think I now understand what your saying with what jen said. I will try to implement that and see if it works ?

Thanks again,
mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-07-07 : 19:03:53

This is my understanding up jens code update...... not sure its correct or not but I do get the same error.

Thanks,
mike123

alter PROCEDURE dbo.select_MediaList_Page_TopRated '1', '5'
(
@Page smallint,
@RecsPerPage tinyint
)
AS
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempMedia
(
ID int IDENTITY,
mediaID int,
pageName varchar(100),
mediaTitle varchar(200),
mediaDesc varchar(500),
datePosted datetime,
votes int,
points int
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempMedia (mediaID, pageName, mediaTitle, mediaDesc, datePosted, votes, points)

SELECT mediaID,pageName,mediaTitle,mediaDesc,datePosted,votes,points FROM(
SELECT
M.mediaID,
-- M.categoryID,
-- Cat.categoryDesc,
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
--LEFT OUTER JOIN tblCategories Cat ON M.categoryID = cat.categoryID

WHERE m.activeStatus ='1' and votes > 5

GROUP BY
-- M.categoryID,
--Cat.categoryDesc,
M.mediaID,
pageName,
mediaTitle,
mediaDesc,
datePosted) a
ORDER BY
a.points / a.votes DESC

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(mediaID)
FROM #TempMedia TM
WHERE TM.ID >= @LastRec
)
FROM #TempMedia
WHERE ID > @FirstRec AND ID < @LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-07 : 19:05:34

Please post the DDL for both tables in order for us to further help you.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-07 : 19:07:57

Nevermind, I see the problem now:

WHERE m.activeStatus ='1' and votes > 5

You are referring to votes which doesn't exist in either table.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-07 : 19:11:22

See if this works:



CREATE TABLE #TempMedia
(
	ID int IDENTITY,
	mediaID int,
	pageName varchar(100),
	mediaTitle varchar(200),
	mediaDesc varchar(500),
	datePosted datetime,
	votes int,
	points int
)

INSERT INTO #TempMedia (mediaID, pageName, mediaTitle, mediaDesc, datePosted, votes, points)
SELECT mediaID, pageName, mediaTitle, mediaDesc, datePosted, votes, points 
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
WHERE votes > 5

SELECT *
FROM #TempMedia

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-07-07 : 19:18:30

hi tara, I understand my mistake now .. :)

Thanks once again! :)

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources