January 29, 2015 at 7:56 am
This query is the first time I am using the Unpivot syntax and I am coming across a problem. When I try to unpivot my data, I get the following statement:
"Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Table3.DocketId" could not be bound."
What is the cause of this issue?
Select
Table3.DocketId,
UP.AssignmentType,
Up.AssignedStaff
From
(
Select distinct
Table2.DocketId,
Table2.BusinessName,
Table2.Complainant,
table2.caseclosed,
--Who is the most recent person to work on the mediation if
--the mediation is not complete and the case is not closed
Case
when Table2.AssignedMediator <> ' ' and table2.caseclosed = 0
then Table2.AssignedMediator else null
End as AssignedMediator,
--Who is the most recent person to work on the investigation if
--the investigation is not complete and the case is not closed
Case
when Table2.AssignedInvestigator <> ' ' and Table2.CaseClosed = 0
then Table2.AssignedInvestigator else null
End as AssignedInvestigator,
--who is the most recent person to work on the hearing noh if the case
--is not closed
Case
when Table2.HearingNOHStaff <> ' ' and Table2.CaseClosed = 0
then Table2.HearingNOHStaff else null
End as HearingNOHStaff,
--who is the most recent person to work on the compliance conference if
--the case is not closed
Case
when Table2.ComplianceConferenceStaff <> ' ' and Table2.CaseClosed = 0
then Table2.ComplianceConferenceStaff else null
End as ComplianceConferenceStaff,
--who is the most recent person to work on the hearing process if the
--case is not closed
Case
when Table2.HearingProcessStaff <> ' ' and Table2.CaseClosed = 0
then Table2.HearingProcessStaff else null
End as HearingProcessStaff,
--who is the most recent person to close the case
Case
when Table2.CloseStaff = ' ' then null
else Table2.CloseStaff
End as CloseStaff,
--Used in the case that the case has all its workflow complete but
--the case is not closed
Case
when Table2.CloseStaff = ' ' then Table2.CurrentStaff
else Table2.CloseStaff
End as CloseCurrentStaff
--staff that is currently working on the case
From
(
--...further nested select statements
) as Table2
) as Table3
Unpivot
(
AssignedStaff for AssignmentType in
(
table3.AssignedMediator,
table3.AssignedInvestigator,
table3.HearingNOHStaff,
table3.ComplianceConferenceStaff,
table3.HearingProcessStaff,
table3.CloseStaff
)
) as UP
January 29, 2015 at 1:11 pm
Try:
Select
DocketId,
AssignmentType,
AssignedStaff
From -- ...
Hope this helps.
January 29, 2015 at 1:26 pm
The Table3 results are consumed by the UNPIVOT operator, producing a new derived table with the alias UP. So the Table3 alias in out of scope in the outermost SELECT.
Either DocketId or UP.DocketId would work.
January 29, 2015 at 2:49 pm
Thank you for the help. You are correct, it should have been UP.DocketId, and not Table3.DocketId, etc.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply