Solved

Multi Part Identifier could not be bound

Posted on 2010-08-24
19
636 Views
Last Modified: 2012-05-10
I am trying to write a simple query but has the above error message for one of the tables that I am trying to reference.  I am assuming that it has something to do with the table itself and the properties of it.  Can anyone tell me what property it needs besides have a primary key and identity column?  when writing the query, SQL does not want to recognize that this is part of the database.  I am sure it is something simple overlooked, any assistance would be helpful.

The table of dbo.DDaPDischargeTranslation is the one that cannot be bound in this instance.  Thanks.



 
UPDATE dbo.DDAPTransmittal 

SET dbo.DDAPTransmittal.DischargeReason = dbo.DDaPDischargeTranslation.DDaP_DCCode

FROM dbo.DDAPTransmittal

LEFT JOIN dbo.DDaPDischargeTranslation 

ON dbo.DDAPTransmittal.DischargeReason = dbo.DDaPDischargeTranslation.DC_Code;

Open in new window

0
Comment
Question by:JasBrad
  • 10
  • 9
19 Comments
 
LVL 12

Expert Comment

by:mcv22
ID: 33515068
Is this what you are trying to do?
UPDATE dbo.DDAPTransmittal 

SET dbo.DDAPTransmittal.DischargeReason = dbo.DDaPDischargeTranslation.DC_Code

FROM dbo.DDAPTransmittal

LEFT JOIN dbo.DDaPDischargeTranslation 

ON dbo.DDAPTransmittal.DischargeReason = dbo.DDaPDischargeTranslation.DC_Code;

Open in new window

0
 

Author Comment

by:JasBrad
ID: 33515142
Yes, however I am getting an error of "The Multi part identifier dbo.DDaPDischargeTranslation.DDaP_DCCode could not be bound" which does not allow the query to give me the results I need.

0
 
LVL 12

Expert Comment

by:mcv22
ID: 33515241
Did you try pasting my command instead? It looks like it cannot reference the DDaP_DCCode column.
0
 

Author Comment

by:JasBrad
ID: 33515318
Yes, even though I could not see the difference in the code.  I agree that it does not seem to be able to reference the DDaPDischargeTranslation table......but is there something that needs to be done to the table to make it register? Something that I might be overlooking
0
 
LVL 12

Expert Comment

by:mcv22
ID: 33515387
Double check the table names and column names to make sure that they exist.

Try using table aliases instead:


UPDATE transmit
SET DischargeReason = translate.DDaP_DCCode
FROM dbo.DDAPTransmittal transmit
LEFT JOIN dbo.DDaPDischargeTranslation translate
ON transmit.DischargeReason = translate.DC_Code;

Open in new window

0
 
LVL 12

Expert Comment

by:mcv22
ID: 33515400
Is DDaP_DCCode a valid column in the table dbo.DDaPDischargeTranslation ?
0
 
LVL 12

Expert Comment

by:mcv22
ID: 33515453
It would also help if you added both table definitions.
0
 

Author Comment

by:JasBrad
ID: 33515464
Yes it is.  I rebuilt the table twice already thinking there might be corruption in the data due to the fact that it was originally imported from Access. But I scripted it and added in the primary key as well as the identity column.  There are only 4 fields in the table.  It is not acknowledging the table at all.
0
 

Author Comment

by:JasBrad
ID: 33515493
Here is the script that creates the table:

CREATE TABLE [dbo].[DDaPDischargeTranslation](
[DC_Code] [int] Identity (1,1) NOT NULL,
[DischargeReason] [nvarchar](100) NULL,
[DDaP_DCCode] [int] NULL,
[DDaPReason] [nvarchar](100) NULL,
CONSTRAINT [PK_DDaPDischargeTranslation] PRIMARY KEY CLUSTERED
(
[DC_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 12

Expert Comment

by:mcv22
ID: 33515575
Does the login executing the code have view definition and select on the DDaPDischargeTranslation table?
0
 

Author Comment

by:JasBrad
ID: 33515597
The main table is quite large with approximately 136 fields.  SQL has no problem with it.
CREATE TABLE [dbo].[DDAPTransmittal](

	[ProviderID] [nchar](10) NOT NULL,

	[ProviderRecordID] [int] IDENTITY(1,1) NOT NULL,

	[ProviderClientID] [int] NOT NULL,

	[SocialSecurity] [nchar](15) NULL,

	[NoSSReason] [int] NULL,

	[DOB] [date] NULL,

	[NoDOBReason] [int] NULL,

	[LastName] [nchar](25) NULL,

	[FirstName] [nchar](25) NULL,

	[MiddleInitial] [nchar](5) NULL,

	[Suffix] [int] NULL,

	[Gender] [nchar](5) NULL,

	[Race1] [int] NULL,

	[Race2] [int] NULL,

	[Race3] [int] NULL,

	[Race4] [int] NULL,

	[Race5] [int] NULL,

	[Ethnicity] [int] NULL,

	[Language1] [int] NULL,

	[Language2] [int] NULL,

	[Religion] [int] NULL,

	[Address1] [nchar](25) NULL,

	[Address2] [nchar](25) NULL,

	[City] [nchar](25) NULL,

	[State] [nchar](5) NULL,

	[Zip] [nchar](10) NOT NULL,

	[MaritalStatus] [int] NULL,

	[VeteranStatus] [nchar](5) NULL,

	[MilitaryStartDate] [date] NULL,

	[MilitaryEndDate] [date] NULL,

	[InsuranceType] [int] NULL,

	[InsurancePolicyNum] [nchar](15) NULL,

	[InsuranceStartDate] [date] NULL,

	[InsuranceEndDate] [date] NULL,

	[ProgramID] [nchar](50) NULL,

	[AdmissionDate] [date] NULL,

	[FirstServiceRequest] [date] NULL,

	[PrimaryReferral] [int] NULL,

	[PregnancyStatus] [nchar](5) NULL,

	[TobaccoUse] [nchar](5) NULL,

	[DischargeDate] [date] NULL,

	[DischargeReason] [int] NULL,

	[CoOccurringDate] [date] NULL,

	[MHScreeningUsed] [int] NULL,

	[SAScreenUsed] [int] NULL,

	[MHScore] [int] NULL,

	[SAScore] [int] NULL,

	[MHSuicidality] [nchar](5) NULL,

	[MHTrauma] [nchar](5) NULL,

	[MHGambling] [nchar](5) NULL,

	[SAIPLifetime] [int] NULL,

	[SAOPLifetime] [int] NULL,

	[AssessmentDate] [date] NULL,

	[EmploymentStatus] [int] NULL,

	[HighestGrade] [int] NULL,

	[DependentIncome] [int] NULL,

	[MinorsIncome] [int] NULL,

	[SourceSupport] [int] NULL,

	[LivingSituation] [int] NULL,

	[Homeless6Mos] [nchar](5) NULL,

	[ControlledEnviron30] [int] NULL,

	[Arrests30days] [int] NULL,

	[SocialSupportSelfHelp] [int] NULL,

	[SocialSupportFamily] [nchar](5) NULL,

	[DrugType1] [int] NULL,

	[Drug1Method] [int] NULL,

	[Drug1DaysUsed30] [int] NULL,

	[Drug1AgeFirst] [int] NULL,

	[DrugType2] [int] NULL,

	[Drug2Method] [int] NULL,

	[Drug2DaysUsed30] [int] NULL,

	[Drug2AgeFirst] [int] NULL,

	[DrugType3] [int] NULL,

	[Drug3Method] [int] NULL,

	[Drug3DaysUsed30] [int] NULL,

	[Drug3AgeFirst] [int] NULL,

	[DrugType4] [int] NULL,

	[Drug4Method] [int] NULL,

	[Drug4DaysUsed30] [int] NULL,

	[Drug4AgeFirst] [int] NULL,

	[DrugType5] [int] NULL,

	[Drug5Method] [int] NULL,

	[Drug5DaysUsed30] [int] NULL,

	[Drug5AgeFirst] [int] NULL,

	[TypeDiagnosis] [nchar](5) NULL,

	[EffectiveDateDiag] [date] NULL,

	[Axis1_1] [nchar](10) NULL,

	[Axis1_2] [nchar](10) NULL,

	[Axis1_3] [nchar](10) NULL,

	[Axis1_4] [nchar](10) NULL,

	[Axis1_5] [nchar](10) NULL,

	[Axis1_6] [nchar](10) NULL,

	[Axis1_7] [nchar](10) NULL,

	[Axis2_1] [nchar](10) NULL,

	[Axis2_2] [nchar](10) NULL,

	[Axis2_3] [nchar](10) NULL,

	[Axis2_4] [nchar](10) NULL,

	[Axis2_5] [nchar](10) NULL,

	[Axis3_1] [nchar](10) NULL,

	[Axis3_2] [nchar](10) NULL,

	[Axis3_3] [nchar](10) NULL,

	[Axis3_4] [nchar](10) NULL,

	[Axis3_5] [nchar](10) NULL,

	[Axis4_1] [nchar](5) NULL,

	[Axis4_2] [nchar](5) NULL,

	[Axis4_3] [nchar](5) NULL,

	[Axis4_4] [nchar](5) NULL,

	[Axis4_5] [nchar](5) NULL,

	[Axis4_6] [nchar](5) NULL,

	[Axis4_7] [nchar](5) NULL,

	[Axis4_8] [nchar](5) NULL,

	[Axis4_9] [nchar](5) NULL,

	[Axis5] [int] NULL,

	[ServiceCode] [nchar](10) NULL,

	[ServiceStartDate] [date] NULL,

	[ServiceEndDate] [date] NULL,

	[ServiceUnits] [int] NULL,

	[Duration] [int] NULL,

	[ClinicianLastName] [nchar](25) NULL,

	[ClinicianFirstName] [nchar](25) NULL,

	[ClinicianCredentials] [int] NULL,

	[ServiceLocation] [int] NULL,

	[DiagnosisCode] [nchar](10) NULL,

	[InsType2] [int] NULL,

	[InsPolicy2Number] [nchar](15) NULL,

	[InsPolicy2StartDate] [date] NULL,

	[InsPolicy2EndDate] [date] NULL,

	[InsType3] [int] NULL,

	[InsPolicy3Number] [nchar](15) NULL,

	[InsPolicy3StartDate] [date] NULL,

	[InsPolicy3EndDate] [date] NULL,

	[InsType4] [int] NULL,

	[InsPolicy4Number] [nchar](15) NULL,

	[InsPolicy4StartDate] [date] NULL,

	[InsPolicy4EndDate] [date] NULL,

 CONSTRAINT [PK_DDAPTransmittal_1] PRIMARY KEY CLUSTERED 

(

	[ProviderRecordID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]



GO



ALTER TABLE [dbo].[DDAPTransmittal] ADD  CONSTRAINT [DF_DDAPTransmittal_ProviderID]  DEFAULT ((152)) FOR [ProviderID]

GO



ALTER TABLE [dbo].[DDAPTransmittal] ADD  CONSTRAINT [DF_DDAPTransmittal_VeteranStatus]  DEFAULT (N'U') FOR [VeteranStatus]

GO



ALTER TABLE [dbo].[DDAPTransmittal] ADD  CONSTRAINT [DF_DDAPTransmittal_MHSuicidality]  DEFAULT (N'N') FOR [MHSuicidality]

GO



ALTER TABLE [dbo].[DDAPTransmittal] ADD  CONSTRAINT [DF_DDAPTransmittal_MHTrauma]  DEFAULT (N'N') FOR [MHTrauma]

GO

Open in new window

0
 

Author Comment

by:JasBrad
ID: 33515635
I am running this off the server, logged in as administrator.
0
 
LVL 12

Expert Comment

by:mcv22
ID: 33515707
Does this work?
UPDATE transmit

SET DischargeReason = translate.DDaP_DCCode

FROM dbo.DDAPTransmittal transmit

JOIN dbo.DDaPDischargeTranslation translate

ON transmit.DischargeReason = translate.DC_Code;

Open in new window

0
 

Author Comment

by:JasBrad
ID: 33515824
That helped.  Before it was wiping out my data instead of updating it now it comes back with 0 rows affected and doesnt change anything.  Maybe running maintenance on the whole database would help?
0
 
LVL 12

Expert Comment

by:mcv22
ID: 33515939
The reason it was being wiped out was because you had the LEFT JOIN in there. It would appear that most of the rows didn't have a corresponding row in the translationtable in which case DDaP_DCCode would be null which would be assigned to the DischargeReason column. JOIN on the other hand matches only the rows that are present in both tables and then performs the update.
0
 
LVL 12

Expert Comment

by:mcv22
ID: 33515950
In fact since the update with join didn't modify any rows, none of the rows match in the two tables
0
 

Author Comment

by:JasBrad
ID: 33516014
The problem is that I do have matching rows, does that mean I should have a Right Join or reverse the order of the tables in the query?
0
 
LVL 12

Expert Comment

by:mcv22
ID: 33516139
Try this
create table #temp

(

  providerrecordid int,

  ddap_dccode int

)



insert #temp (providerrecordid, ddap_dccode)

select

    transmit.providerrecordid,

    translate.DDaP_DCCode

from

    dbo.DDAPTransmittal transmit

JOIN

    dbo.DDaPDischargeTranslation translate

ON

    transmit.DischargeReason = translate.DC_Code



UPDATE

   transmit

SET

   DischargeReason = t.ddap_dccode

from

    dbo.DDAPTransmittal transmit

JOIN

  #temp t

ON 

    transmit.ProviderRecordID = t.ProviderRecordID

Open in new window

0
 

Accepted Solution

by:
JasBrad earned 0 total points
ID: 33560783
I was able to get it to bind by backing up the database and running the maintenance plan.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql join/ assign small # first 10 83
all records from previous month 6 45
testing sql16 on win10 vs OS16 2 36
Get Duration of last Status Update 4 32
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now