The multi-part identifier could not be bound

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)

The multi-part identifier could not be bound

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-10-19 : 03:43:56

Hi,

I have the following query, and I am getting the following error below. I designed this part a few months ago and have left and now I am just coming back to it. Not sure if this is possible but I swear it was working before, not sure if this is possible because of different data in the tables, regardless even if this is the case obviously my query needs help.

Any help is much appreciated!!

thanks once again!

Server: Msg 4104, Level 16, State 1, Procedure select_videos_mostViewed, Line 8
The multi-part identifier "vv.totalViews" could not be bound.



CREATE PROCEDURE [dbo].[select_videos_mostViewed]
(
@days int
)
	 
AS SET NOCOUNT ON 

SELECT counterID, videoID, v.userID, caption, ratingID, UD.nameOnline,v.votes,v.points,v.videoDate,v.lengthSeconds, 

(select count(*) from tblVideoViews VV WHERE VV.userID = V.userID) as totalViews

FROM tblVideos V 

JOIN tblUserDetails UD on UD.userID = V.userID

WHERE status = 1

ORDER BY vv.totalViews DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-19 : 03:49:44

ORDER BY is NOT vv.totalViews

Either ORDER BY 11 (not recommended)
or ORDER BY totalViews (without prefix)

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

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-10-19 : 03:58:09

ahhh, nice and simple to fix..

thanks so much peso!! :) hope things are goin well up there in sweden :D

till next time ;)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-19 : 04:03:36

[code]CREATE PROCEDURE dbo.uspSelectVideosMostViewed
(
@Days int
)
AS

SET NOCOUNT ON

;WITH Yak (CounterID, VideoID, UserID, CaptionID, RatingID, NameOnline, Votes, Points, VideoDate, LengthSeconds, TotalViews)
AS (
SELECT v.CounterID,
v.VideoID,
v.UserID,
v.CaptionID,
v.RatingID,
ud.NameOnline,
v.Votes,
v.Points,
v.VideoDate,
v.LengthSeconds,
COUNT(vv.UserID) OVER (PARTITION BY v.UserID) AS TotalViews
FROM tblVideos AS v
INNER JOIN tblUserDetails AS ud ON ud.UserID = v.UserID
LEFT JOIN tblVideoViews AS vv ON vv.UserID = v.UserID
)

SELECT CounterID,
VideoID,
UserID,
CaptionID,
RatingID,
NameOnline,
Votes,
Points,
VideoDate,
LengthSeconds,
TotalViews
FROM Yak
ORDER BY TotalViews DESC[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-19 : 04:04:23

Don't forget to include the @Days parameter somewhere...

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

chrysler
Starting Member

3 Posts

Posted - 2007-10-23 : 23:57:43

hi i get the same error too.

-------------

CREATE PROCEDURE updateShelf
-- Add the parameters for the stored procedure here
@BookId varchar(50) = NULL,
@ShelfName varchar(50) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
UPDATE tblBook
SET strShelfName = @BookId
WHERE tblShelf.strBook = @ShelfName
END
GO

The multi-part identifier "tblShelf.strBook" could not be bound.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-24 : 00:00:32

You suppose to update tblBook or tblShelf table ?


UPDATE tblBook
SET    strShelfName     = @BookId
WHERE  tblShelf.strBook = @ShelfName

KH
[spoiler]Time is always against us[/spoiler]

chrysler
Starting Member

3 Posts

Posted - 2007-10-24 : 00:07:54

oops. tblBook

quote:
Originally posted by khtan

You suppose to update tblBook or tblShelf table ?

UPDATE tblBook
SET strShelfName = @BookId
WHERE tblShelf.strBook = @ShelfName

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-24 : 00:12:44

quote:
Originally posted by chrysler

oops. tblBook

Sorry, don't understand your reply.
maybe you should use the following update syntax. It is clearer.


UPDATE a
SET    strShelfName  = @BookId
FROM   tblBook a
WHERE  a.strBook     = @ShelfName

KH
[spoiler]Time is always against us[/spoiler]

chrysler
Starting Member

3 Posts

Posted - 2007-10-24 : 03:43:16

may i ask if there are more than 1 value in an attribute(strbook FROM tblRack) that is one of my conditions, how do i go about doing it?

my strBook FROM tblRack consists of more than one value, it's a string of values("value1,value2,value3") how do i split it out to compare with tblBook.strBook.

AND ALSO. i get errors for my "tblShelf.strShelfName"
(The multi-part identifier "tblShelf.strShelfName" could not be bound.)

CREATE PROCEDURE updateBook
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
UPDATE tblBook
SET tblBook.strShelfName = tblShelf.strShelfName
WHERE tblBook.strBook = (SELECT strBook FROM tblShelf)
END
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 03:45:31

Don't hijack topics.
Start your own.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources