SQL Script error - 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 2008 Forums

Transact-SQL (2008)

SQL Script error

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2012-03-27 : 11:00:04

Hi,i am ending with an error message
Msg 4104, Level 16, State 1, Line 78
The multi-part identifier "PIMS_MPI_MPIALL_RENAME.PIMS_UHPI" could not be bound.

these are my joins
.....
....
.....
FROM
dbo.PIMS_MPI_MPIALL_RENAME
INNER JOIN PIMS_MPI_MPI_RENAME
INNER JOIN PIMS_MPI_MPI_UPDATE
ON PIMS_MPI_MPIALL_RENAME.PIMS_UHPI =PIMS_MPI_MPI_UPDATE.[Unique Hospital Patient Identifier UHPI]
ON PIMS_MPI_MPI_RENAME.[Unique Hospital Patient Identifier UHPI] = PIMS_MPI_MPIALL_RENAME.PIMS_UHPI
LEFT JOIN PIMS_MPI_NOK_RENAME_OUTPUT
ON PIMS_MPI_MPI_RENAME.[Unique Hospital Patient Identifier UHPI] =PIMS_MPI_NOK_RENAME_OUTPUT.[Unique Hospital Patient Identifier (UHPI)]
LEFT JOIN PIMS_MPI_ALIAS_RENAME_OUTPUT
ON PIMS_MPI_MPI_RENAME.[Unique Hospital Patient Identifier UHPI] = PIMS_MPI_ALIAS_RENAME_OUTPUT.[Unique Hospital Patient Identifier (UHPI)]
LEFT JOIN PIMS_MPI_ALERTS
ON PIMS_MPI_MPIALL_RENAME.[PIMS_UHPI] = PIMS_MPI_ALERTS.[PASID]
LEFT JOIN PIMS_MPI_OTHERNUMBERS_RENAME_OUTPUT
ON PIMS_MPI_MPI_RENAME.[Unique Hospital Patient Identifier UHPI] = PIMS_MPI_OTHERNUMBERS_RENAME_OUTPUT.[Unique Hospital Patient Identifier (UHPI)]
LEFT JOIN PIMS_MPI_OTHERADDRESS_RENAME_SS
ON PIMS_MPI_MPI_UPDATE.[Unique Hospital Patient Identifier UHPI] = PIMS_MPI_OTHERADDRESS_RENAME_SS.[Unique Hospital Patient Identifier (UHPI)]

any idea, where iam doing wrong!

Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-27 : 11:18:13

Please post the SELECT part as well

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

satya068
Posting Yak Master

233 Posts

Posted - 2012-03-27 : 11:29:17

SELECT
PIMS_MPI_MPI_RENAME.[Unique Hospital Patient Identifier UHPI],
PIMS_MPI_MPI_RENAME.[CHI Number],
PIMS_MPI_MPI_RENAME.SURNAME,
PIMS_MPI_MPI_RENAME.FORNAME,
PIMS_MPI_MPI_RENAME.[Title Code],
PIMS_MPI_MPI_RENAME.[Sex Gender Code],
PIMS_MPI_MPI_RENAME.[Date Of Birth],
PIMS_MPI_MPI_RENAME.[Marital Status Code],
PIMS_MPI_MPI_RENAME.[Religion Code],
PIMS_MPI_MPI_RENAME.[Ethnic Origin],
PIMS_MPI_MPI_RENAME.[Interpreter Required],
PIMS_MPI_MPI_RENAME.[Preferred Language],
PIMS_MPI_MPI_RENAME.[VIP Flag],
PIMS_MPI_MPI_RENAME.[Address Line 1],
PIMS_MPI_MPI_RENAME.[Address Line 2],
PIMS_MPI_MPI_RENAME.[Address City Code],
PIMS_MPI_MPI_RENAME.[Province Code] AS COUNTRY,
PIMS_MPI_MPI_RENAME.[Post Code],
PIMS_MPI_MPI_RENAME.[Health Care Area],
PIMS_MPI_MPI_RENAME.[Telephone Home],
PIMS_MPI_MPI_RENAME.[Telephone Work],
PIMS_MPI_MPI_RENAME.[Foreign Phone Number],
PIMS_MPI_MPI_RENAME.[Mobile Phone Number],
PIMS_MPI_MPI_RENAME.[Other Phone Number],
PIMS_MPI_MPI_RENAME.[Email Address],
PIMS_MPI_MPI_RENAME.[Other Email Address],
PIMS_MPI_MPI_RENAME.[Preferred Contact Method],
--PIMS_MPI_MPI_RENAME.MFF_GP_DESC AS REGISTER_GP_NAME
PIMS_MPI_MPI_RENAME.[GP],
PIMS_MPI_MPI_RENAME.[Family Doctor Practice Code],
--PIMS_MPI_MPI_RENAME.MFF_CLINIC_DESC,
PIMS_MPI_MPI_RENAME.[Deceased Date],
PIMS_MPI_MPI_RENAME.[Deceased Time],
PIMS_MPI_MPI_RENAME.[Deceased Indicator Y N],
PIMS_MPI_MPI_RENAME.[Who Notified Death],
PIMS_MPI_NOK_RENAME_OUTPUT.[Contact Type],
PIMS_MPI_NOK_RENAME_OUTPUT.[Relationship Code],
PIMS_MPI_NOK_RENAME_OUTPUT.[Title Code],
PIMS_MPI_NOK_RENAME_OUTPUT.[SURNAME],
PIMS_MPI_NOK_RENAME_OUTPUT.[FORENAME],
PIMS_MPI_NOK_RENAME_OUTPUT.[Sex / Gender Code],
PIMS_MPI_NOK_RENAME_OUTPUT.[Address Line #1],
PIMS_MPI_NOK_RENAME_OUTPUT.[Address Line #2],
PIMS_MPI_NOK_RENAME_OUTPUT.[Country Code],
PIMS_MPI_NOK_RENAME_OUTPUT.[Post Code],
PIMS_MPI_NOK_RENAME_OUTPUT.[From Date],
PIMS_MPI_NOK_RENAME_OUTPUT.[To Date],
PIMS_MPI_ALIAS_RENAME_OUTPUT.[Type],
PIMS_MPI_ALIAS_RENAME_OUTPUT.[SURNAME] As [Alias Surname],
PIMS_MPI_ALIAS_RENAME_OUTPUT.[Forename] AS [Alias Forename],
PIMS_MPI_OTHERNUMBERS_RENAME_OUTPUT.[Medical Record Type],
PIMS_MPI_OTHERNUMBERS_RENAME_OUTPUT.[Other Number],
PIMS_MPI_OTHERNUMBERS_RENAME_OUTPUT.[Date Created],
PIMS_MPI_OTHERNUMBERS_RENAME_OUTPUT.[Time Created],
PIMS_MPI_OTHERNUMBERS_RENAME_OUTPUT.[User Created],
PIMS_MPI_ALERTS.[Alert Category Code],
PIMS_MPI_ALERTS.[Alert MessageText],
PIMS_MPI_ALERTS.[Review Date],
PIMS_MPI_OTHERADDRESS_RENAME_SS.[Address Type],
PIMS_MPI_OTHERADDRESS_RENAME_SS.[Address Line1],
PIMS_MPI_OTHERADDRESS_RENAME_SS.[Address Line 2],
PIMS_MPI_OTHERADDRESS_RENAME_SS.[City Code],
PIMS_MPI_OTHERADDRESS_RENAME_SS.[Country Code],
PIMS_MPI_OTHERADDRESS_RENAME_SS.[Post Code],
PIMS_MPI_OTHERADDRESS_RENAME_SS.[Phone],
PIMS_MPI_OTHERADDRESS_RENAME_SS.[Date From],
PIMS_MPI_OTHERADDRESS_RENAME_SS.[Date To]
--PIMS_MPI_GP_RENAME_SS.[GP Code],
--PIMS_MPI_GP_RENAME_SS.[GP Clinic Code],
--PIMS_MPI_GP_RENAME_SS.[Date From],
--PIMS_MPI_GP_RENAME_SS.[Date To]

FROM
dbo.PIMS_MPI_MPIALL_RENAME
INNER JOIN PIMS_MPI_MPI_RENAME
INNER JOIN PIMS_MPI_MPI_UPDATE
ON PIMS_MPI_MPIALL_RENAME.PIMS_UHPI =PIMS_MPI_MPI_UPDATE.[Unique Hospital Patient Identifier UHPI]
ON PIMS_MPI_MPI_RENAME.[Unique Hospital Patient Identifier UHPI] = PIMS_MPI_MPIALL_RENAME.PIMS_UHPI
LEFT JOIN PIMS_MPI_NOK_RENAME_OUTPUT
ON PIMS_MPI_MPI_RENAME.[Unique Hospital Patient Identifier UHPI] =PIMS_MPI_NOK_RENAME_OUTPUT.[Unique Hospital Patient Identifier (UHPI)]
LEFT JOIN PIMS_MPI_ALIAS_RENAME_OUTPUT
ON PIMS_MPI_MPI_RENAME.[Unique Hospital Patient Identifier UHPI] = PIMS_MPI_ALIAS_RENAME_OUTPUT.[Unique Hospital Patient Identifier (UHPI)]
LEFT JOIN PIMS_MPI_ALERTS
ON PIMS_MPI_MPIALL_RENAME.[PIMS_UHPI] = PIMS_MPI_ALERTS.[PASID]
LEFT JOIN PIMS_MPI_OTHERNUMBERS_RENAME_OUTPUT
ON PIMS_MPI_MPI_RENAME.[Unique Hospital Patient Identifier UHPI] = PIMS_MPI_OTHERNUMBERS_RENAME_OUTPUT.[Unique Hospital Patient Identifier (UHPI)]
LEFT JOIN PIMS_MPI_OTHERADDRESS_RENAME_SS
ON PIMS_MPI_MPI_UPDATE.[Unique Hospital Patient Identifier UHPI] = PIMS_MPI_OTHERADDRESS_RENAME_SS.[Unique Hospital Patient Identifier (UHPI)]
--LEFT JOIN PIMS_MPI_GP_RENAME_SS
--ON PIMS_MPI_MPI_UPDATE.[Unique Hospital Patient Identifier UHPI] = PIMS_MPI_GP_RENAME_SS.[Unique Hospital Patient Identifier(UHPI)]

Error message:
Msg 4104, Level 16, State 1, Line 78
The multi-part identifier "PIMS_MPI_MPIALL_RENAME.PIMS_UHPI" could not be bound.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-27 : 11:33:18

Well...first your joins are out of whack...and maybe tru the aliases as I put forth here



      FROM  [dbo].[PIMS_MPI_MPIALL_RENAME]					ra 
INNER JOIN  [dbo].[PIMS_MPI_MPI_RENAME]						r 
	ON      r.[Unique Hospital Patient Identifier UHPI]		 =  ra.PIMS_UHPI
INNER JOIN	  dbo.PIMS_MPI_MPI_UPDATE						u	
	ON     ra.[PIMS_UHPI]									 =   u.[Unique Hospital Patient Identifier UHPI]
 LEFT JOIN  [dbo].[PIMS_MPI_NOK_RENAME_OUTPUT]				ro
	ON      r.[Unique Hospital Patient Identifier UHPI]		 =  ro.[Unique Hospital Patient Identifier (UHPI)]
 LEFT JOIN  [dbo].[PIMS_MPI_ALIAS_RENAME_OUTPUT]			ao
	ON		r.[Unique Hospital Patient Identifier UHPI]		 =  ao.[Unique Hospital Patient Identifier (UHPI)]
 LEFT JOIN  [dbp].[PIMS_MPI_ALERTS]							a
	ON	   ra.[PIMS_UHPI]									 =   a.[PASID]
 LEFT JOIN  [dbo].[PIMS_MPI_OTHERNUMBERS_RENAME_OUTPUT]		oro
	ON	    r.[Unique Hospital Patient Identifier UHPI]		 = oro.[Unique Hospital Patient Identifier (UHPI)]
 LEFT JOIN  [dbo].[PIMS_MPI_OTHERADDRESS_RENAME_SS]			ss
	ON      u.[Unique Hospital Patient Identifier UHPI]		 =  ss.[Unique Hospital Patient Identifier (UHPI)]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-27 : 11:34:26

To me this is wrong..not sure it would work..but


FROM 
dbo.PIMS_MPI_MPIALL_RENAME

INNER JOIN PIMS_MPI_MPI_RENAME 
INNER JOIN PIMS_MPI_MPI_UPDATE
ON PIMS_MPI_MPIALL_RENAME.PIMS_UHPI =PIMS_MPI_MPI_UPDATE.[Unique Hospital Patient Identifier UHPI]
ON PIMS_MPI_MPI_RENAME.[Unique Hospital Patient Identifier UHPI] = PIMS_MPI_MPIALL_RENAME.PIMS_UHPI

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources