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 2008 Forums

Transact-SQL (2008)

multi-part identifier could not be bound.

Author

Topic

Mikehjun
Starting Member

24 Posts

Posted - 2011-01-10 : 20:45:03

When I run the following with first FROM clause, it ran fine but with second one, I got error saying 'The multipart identifier "dbo.vEOC_Emp_All.Available' could not be bound.

vEOC_Emp_All is a view table composed of INFO1 and INFO2.

Any idea help! Thanks,

SELECT Team =
CASE dbo.vEOC_Emp_All.Available
WHEN 'N' THEN 'Not Available'
ELSE ISNULL(dbo.vEOC_Emp_All.Team, 'Not Assigned')
END
--FROM dbo.vEOC_Emp_All

FROM dbo.vEOC_Emp_Info1 as t1 LEFT OUTER JOIN
dbo.vEOC_Emp_Info2 as t2 ON t1.EmpNo = t2.EmpNo

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-01-10 : 22:22:04

quote:
Originally posted by Mikehjun

When I run the following with first FROM clause, it ran fine but with second one, I got error saying 'The multipart identifier "dbo.vEOC_Emp_All.Available' could not be bound.

vEOC_Emp_All is a view table composed of INFO1 and INFO2.

Any idea help! Thanks,

SELECT Team =
CASE dbo.vEOC_Emp_All.Available
WHEN 'N' THEN 'Not Available'
ELSE ISNULL(dbo.vEOC_Emp_All.Team, 'Not Assigned')
END
--FROM dbo.vEOC_Emp_All

FROM dbo.vEOC_Emp_Info1 as t1 LEFT OUTER JOIN
dbo.vEOC_Emp_Info2 as t2 ON t1.EmpNo = t2.EmpNo

When you are running the second from clause, the view is not found in "FROM" statement so getting error.

Your select should be something like this:

SELECT Team =
CASE t1.Available
WHEN 'N' THEN 'Not Available'
ELSE ISNULL(t1.Team, 'Not Assigned')
END

or you need to add dbo.vEOC_Emp_All in from tables statement.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-11 : 03:13:44

pk_bohra has give you the solution to your problem, I thought I'd provide the reason :) And the reason this happens is because in the second one you are assigning aliases to your table names. When you do that the table names become "invisible" to the database engine and you will have to use the alias instead.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Mikehjun
Starting Member

24 Posts

Posted - 2011-01-11 : 11:09:37

Thank you so much guys!
It works!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-11 : 11:22:33

Not exactly that. In second from OP is missing the table dbo.vEOC_Emp_All altogether hence all columns refered from that table will be throwing this as it cant find relevant table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-12 : 03:30:18

quote:
Originally posted by visakh16

Not exactly that. In second from OP is missing the table dbo.vEOC_Emp_All altogether hence all columns refered from that table will be throwing this as it cant find relevant table

Hm, good catch visakh...or bad catch by me. The error message would still be the same tough, when using aliases for table names you will get errors if you reference the table names.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

merk
Starting Member

4 Posts

Posted - 2011-03-31 : 13:35:17

hello. Apologies on reviving a somewhat old message but since i am having the same problem and so far haven't figured out what i am doing wrong, i thought it would make more sense to post it here rather then start a new thread.

SELECT [msp].[LeadNumber], msp.[CustomerId], msp.[FirstName], msp.[LastName], u.[homephone],
msp.[ContractCode], msp.[SaleID], [msp].[LastUpdated], msp.[PayType],
CASE [msp].[Status]
WHEN 'V' THEN 'Approved'
WHEN 'D' THEN 'Declined'
WHEN 'A' THEN 'Active'
WHEN 'C' THEN 'Canceled'
END AS [status],
[msp].[LastUpdatedBy] AS 'AE', msp.PostDate, ed.first_name +' ' +ed.last_name AS 'AEName'
FROM [Membership_Sales_Postdate] msp, [users] u
LEFT JOIN Employee_Data ed ON msp.LastUpdatedBy = [ed].[UserId]
WHERE msp.[Status] IN ('A', 'C', 'D', 'V') and msp.[customerid] = u.[userid]
AND ( (@Lname IS null) OR (LastName like @Lname) )
AND ( (@Phone IS NULL) OR (homephone LIKE @Phone) )
AND ( (@Leadnumber IS null) OR (msp.LeadNumber = @Leadnumber) )
AND ( (@CustomerID IS null) OR (CustomerId = @CustomerID) )
AND PostDate >= @startdate
AND PostDate <= @enddate
ORDER BY [msp].[LastUpdated] desc
END

The error i am getting on the bolded line is:
The multi-part identifier "msp.LastUpdatedBy" could not be bound.

From what i have read in this discussion, it sounds like if i use a table alias, i must always reference the table using that alias otherwise i'll get the error i am getting. But i AM referencing the table using the alias, so i dont understand what the problem is here. The only thing i thought was that since earlier in the query i select msp.LastUpdatedBy as 'AE' that i would also need to reference that column by it's alias. But i tried doing that, as well as removing the column alias but i still get the same error.

Can someone please explain what i am doing wrong here?

thanks

merk
Starting Member

4 Posts

Posted - 2011-03-31 : 13:54:04

wow i am really clueless when it comes to joins. i didn't realize the order of the tables in the FROM would effect things.

I changed this:
FROM [Membership_Sales_Postdate] msp, [users] u
LEFT JOIN Employee_Data ed ON msp.LastUpdatedBy = [ed].[UserId]

to this:

FROM [users] u, [Membership_Sales_Postdate] msp
LEFT JOIN Employee_Data ed ON msp.LastUpdatedBy = [ed].[UserId]

And now it works. So just to be clear, the membership_sales_postdate isn't really part of the FROM portion of the sql statement, it's part of the LEFT JOIN, which in turn is part of the FROM

i.e. it might make more sense to write it like this:

FROM [users] u,
[Membership_Sales_Postdate] msp LEFT JOIN Employee_Data ed ON msp.LastUpdatedBy = [ed].[UserId]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources