Multipart 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

General SQL Server Forums

New to SQL Server Programming

Multipart Identifier Could not Be Bound...

Author

Topic

brmcdani441
Starting Member

7 Posts

Posted - 2011-10-14 : 13:29:23

I am getting the good ol MultiPart identifier could not be bound error message when creating the following stored procedure. The issue is with the very last WHERE clause "WHERE commonprod.dbo.personnel.per_id_pk=imsprod.dbo.incident.assigned_to_fk"

CREATE PROCEDURE AssignedCasesByDateRange
	@BegDate DateTime,
	@EndDate DateTime
AS
BEGIN
 SELECT imsprod.dbo.incident.assigned_to_fk, imsprod.dbo.incident.inc_no, imsprod.dbo.incident.inc_datetime
 FROM   imsprod.dbo.incident
 LEFT JOIN commonprod.dbo.personnel ON incident.assigned_to_fk=commonprod.dbo.personnel.per_id_pk
 WHERE  imsprod.dbo.incident.inc_datetime Between @BegDate AND @EndDate 
 UNION ALL 
 SELECT commonprod.dbo.agency.agency, commonprod.dbo.personnel.agcy_no, commonprod.dbo.personnel.name, commonprod.dbo.personnel.per_id_pk
 FROM   commonprod.dbo.personnel
 LEFT OUTER JOIN commonprod.dbo.agency ON commonprod.dbo.personnel.agency_id_fk=commonprod.dbo.agency.agency_id_pk
 WHERE  commonprod.dbo.personnel.per_id_pk=imsprod.dbo.incident.assigned_to_fk

 ORDER BY imsprod.dbo.incident.assigned_to_fk

Any help is appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 13:32:32

[code]
CREATE PROCEDURE AssignedCasesByDateRange
@BegDate DateTime,
@EndDate DateTime
AS
BEGIN
SELECT imsprod.dbo.incident.assigned_to_fk, imsprod.dbo.incident.inc_no, imsprod.dbo.incident.inc_datetime
FROM imsprod.dbo.incident
LEFT JOIN commonprod.dbo.personnel ON incident.assigned_to_fk=commonprod.dbo.personnel.per_id_pk
WHERE imsprod.dbo.incident.inc_datetime Between @BegDate AND @EndDate
UNION ALL
SELECT commonprod.dbo.agency.agency, commonprod.dbo.personnel.agcy_no, commonprod.dbo.personnel.name, commonprod.dbo.personnel.per_id_pk
FROM commonprod.dbo.personnel
INNER JOIN imsprod.dbo.incident
ON commonprod.dbo.personnel.per_id_pk=imsprod.dbo.incident.assigned_to_fk
LEFT OUTER JOIN commonprod.dbo.agency ON commonprod.dbo.personnel.agency_id_fk=commonprod.dbo.agency.agency_id_pk
[/code]

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

sshelper
Posting Yak Master

216 Posts

Posted - 2011-10-14 : 17:36:03

I think the issue is not with the WHERE clause but in the ORDER BY clause. To verify that this is causing the error, try running your query without the ORDER BY clause. If it is indeed the ORDER BY clause causing the issue, try changing it to the following, which doesn't include the table name:

ORDER BY assigned_to_fk

Regards,
SQL Server Helper
http://www.sql-server-helper.com/error-messages/msg-1-500.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 21:45:40

Error is simple. There're couple of references to table imsprod.dbo.incident in second query but the table is not included in it. you cant use table used in one part of the query in other if they're separated by UNION ALL. You can use like this only in case JOIN,APPLY operators.

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

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-15 : 05:44:55

In your second part of your query you have used imsprod.dbo.incident.assigned_to_fk in where clause where you have not mentioned this table of this data base in from or either join clause.

UNION ALL
SELECT commonprod.dbo.agency.agency, commonprod.dbo.personnel.agcy_no, commonprod.dbo.personnel.name, commonprod.dbo.personnel.per_id_pk
FROM commonprod.dbo.personnel
LEFT OUTER JOIN commonprod.dbo.agency ON commonprod.dbo.personnel.agency_id_fk=commonprod.dbo.agency.agency_id_pk
WHERE commonprod.dbo.personnel.per_id_pk=imsprod.dbo.incident.assigned_to_fk

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources