Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 664
  • Last Modified:

Multi Part Identifier could not be bound

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
JasBrad
Asked:
JasBrad
  • 10
  • 9
1 Solution
 
mcv22Commented:
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
 
JasBradAuthor Commented:
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
 
mcv22Commented:
Did you try pasting my command instead? It looks like it cannot reference the DDaP_DCCode column.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
JasBradAuthor Commented:
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
 
mcv22Commented:
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
 
mcv22Commented:
Is DDaP_DCCode a valid column in the table dbo.DDaPDischargeTranslation ?
0
 
mcv22Commented:
It would also help if you added both table definitions.
0
 
JasBradAuthor Commented:
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
 
JasBradAuthor Commented:
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
 
mcv22Commented:
Does the login executing the code have view definition and select on the DDaPDischargeTranslation table?
0
 
JasBradAuthor Commented:
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
 
JasBradAuthor Commented:
I am running this off the server, logged in as administrator.
0
 
mcv22Commented:
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
 
JasBradAuthor Commented:
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
 
mcv22Commented:
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
 
mcv22Commented:
In fact since the update with join didn't modify any rows, none of the rows match in the two tables
0
 
JasBradAuthor Commented:
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
 
mcv22Commented:
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
 
JasBradAuthor Commented:
I was able to get it to bind by backing up the database and running the maintenance plan.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now