Error in updating SQL statement, The multi-part identifier could not be bound.

[tnguyen315]

Hello,
I tried to run the update state but i got errors and couldn't figure out what's wrong, please help.
Thanks in advance.
TN
Here's my code:



--Update Title X - addnote field
update ENHFILE
set e.X12_ADD_NOTE = (

select distinct
case when RACE = 'A' then '5'
when RACE = 'B' then '2'
when RACE = 'E' then '4'
when RACE = 'I' then '4'
when RACE = 'M' then '8'
when RACE = 'N' then '6'
when RACE = 'O' then '6'
when RACE = 'P' then '7'
when RACE = 'W' then '1'
else '6' end +
case when ETHNICITY = 'H' then '5'
else '0' end +
case when max(MARITAL_STAT) = 'M' then '1'
when max(MARITAL_STAT) = 'N' then '2'
else '3' end +
case when max(convert(varchar(2),NO_PREGNANCIES)) IS null then '00'
else ('0' + max(convert(varchar(2),NO_PREGNANCIES))) end +
case when max(convert(varchar(2),NO_LIVE_BIRTHS)) IS null then '00'
else ('0' + max(convert(varchar(2),NO_LIVE_BIRTHS))) end +
case when max(convert(varchar(2),NO_LIVING_CHILD)) IS null then '00'
else ('0' + max(convert(varchar(2),NO_LIVING_CHILD))) end +
case when max(VISIT_PURPOSE) = '3'
then 'N' else 'Y' end +
case when LEN(max(TOTAL_INCOME)) = 1 then ('00000000000' + max(CONVERT(varchar(12),TOTAL_INCOME)))
when LEN(max(TOTAL_INCOME)) = 2 then ('00000000000' + max(CONVERT(varchar(12),TOTAL_INCOME)))
when LEN(max(TOTAL_INCOME)) = 3 then ('000000000' + max(CONVERT(varchar(12),TOTAL_INCOME)))
when LEN(max(TOTAL_INCOME)) = 4 then ('00000000' + max(CONVERT(varchar(12),TOTAL_INCOME)))
when LEN(max(TOTAL_INCOME)) = 5 then ('0000000' + max(CONVERT(varchar(12),TOTAL_INCOME)))
when LEN(max(TOTAL_INCOME)) = 6 then ('000000' + max(CONVERT(varchar(12),TOTAL_INCOME)))
when LEN(max(TOTAL_INCOME)) = 7 then ('00000' + max(CONVERT(varchar(12),TOTAL_INCOME)))
when LEN(max(TOTAL_INCOME)) = 8 then ('0000' + max(CONVERT(varchar(12),TOTAL_INCOME)))
when LEN(max(TOTAL_INCOME)) = 9 then ('000' + max(CONVERT(varchar(12),TOTAL_INCOME)))
when LEN(max(TOTAL_INCOME)) = 10 then ('00' + max(CONVERT(varchar(12),TOTAL_INCOME)))
when LEN(max(TOTAL_INCOME)) = 11 then ('0' + max(CONVERT(varchar(12),TOTAL_INCOME)))
when LEN(max(TOTAL_INCOME)) = 12 then max(CONVERT(varchar(12),TOTAL_INCOME))
else '000000000000'
END +
case when LEN(max(family_size)) = 1 then ('00' + max(CONVERT(varchar(3),family_size)))
when LEN(max(FAMILY_SIZE)) = 2 then ('0' + max(CONVERT(varchar(3),family_size))) else
max(convert(varchar(3),(FAMILY_SIZE))) end +
'p' +
case when CONTRA_END = '01' then 'a'
when CONTRA_END = 'P' then 'a'
when CONTRA_END = '02' then 'c'
when CONTRA_END = 'DP' then 'c'
when CONTRA_END = '03' then 'd'
when CONTRA_END = 'D' then 'd'
when CONTRA_END = '04' then 'd'
when CONTRA_END = '05' then 'e'
when CONTRA_END = 'AM' then 'e'
when CONTRA_END = '06' then 'f'
when CONTRA_END = 'C' then 'g'
when CONTRA_END = '09' then 'h'
when CONTRA_END = '20' then 'i'
when CONTRA_END = '10' then 'j'
when CONTRA_END = '12' then 'k'
when CONTRA_END = 'I' then 'k'
when CONTRA_END = '21' then 'l'
when CONTRA_END = '13' then 'm'
when CONTRA_END = '11' then 'n'
when CONTRA_END = '14' then 'n'
when CONTRA_END = '22' then 'o'
when CONTRA_END = '07' then 'o'
when CONTRA_END = '98' then 'p'
when CONTRA_END = 'UN' then 'q'
when CONTRA_END = '99' then 'r'
when CONTRA_END = 'N' then 'r'
else 'q' END +
'1' +
case when CONTRA_END in ('99', 'N') then 'f'
else ' ' end +
CASe when max(COPAY_PCT) = '100' then 'F'
when max(COPAY_PCT) = '0' then 'N'
else 'P' end + max(convert(varchar(10),f.EFFECTIVE_DATE,112)) + 'B' +
case when (COUNTY) is NULL then '999'
else m.COUNTY
end as fpnote
from MPFILE m
join FIDFILE f on
f.PATIENT_NO = m.PATIENT_NO
join FMHFILE h on
h.PATIENT_NO = m.PATIENT_NO
join ENHFILE e on
e.PATIENT_NO = m.PATIENT_NO
join FAMFILE l on
l.PATIENT_NO = e.PATIENT_NO and
l.ENCOUNTER_NO = E.ENCOUNTER_NO

where
m.PATIENT_NO is not null and
f.PAYOR_NO = 'CAT' and f.PAYOR_CLASS = 'B' and
e.ENCOUNTER_DATE between '09/01/2012' and '12/31/2013'
group by m.RACE, m.ETHNICITY , CONTRA_END, COUNTY, e.ENCOUNTER_NO
)
where m.PATIENT_NO is not null and
f.PAYOR_NO = 'CAT' and f.PAYOR_CLASS = 'B'
and e.ENCOUNTER_DATE between '09/01/2012' and '12/31/2013'


Error Codes:

Msg 4104, Level 16, State 1, Line 101
The multi-part identifier "m.PATIENT_NO" could not be bound.
Msg 4104, Level 16, State 1, Line 102
The multi-part identifier "f.PAYOR_NO" could not be bound.
Msg 4104, Level 16, State 1, Line 102
The multi-part identifier "f.PAYOR_CLASS" could not be bound.
Msg 4104, Level 16, State 1, Line 103
The multi-part identifier "e.ENCOUNTER_DATE" could not be bound.
Msg 4104, Level 16, State 1, Line 103
The multi-part identifier "e.ENCOUNTER_DATE" could not be bound.

 

[fredericofonseca]

I've removed all unrequired code to explain the issue.

update ENHFILE
set e.X12_ADD_NOTE = [COLOR=#CC0000]( -- start subquery[/color]
                       select distinct case
                                       when RACE = 'A' then '5'
                                       when RACE = 'B' then '2'
.....
                         from MPFILE m
                         join FIDFILE f
                           on f.PATIENT_NO = m.PATIENT_NO
                         join FMHFILE h 
                           on h.PATIENT_NO = m.PATIENT_NO
                         join ENHFILE e 
                           on e.PATIENT_NO = m.PATIENT_NO
                         join FAMFILE l 
                           on l.PATIENT_NO = e.PATIENT_NO 
                          and l.ENCOUNTER_NO = E.ENCOUNTER_NO
                         where m.PATIENT_NO is not null
                          and f.PAYOR_NO = 'CAT'
                          and f.PAYOR_CLASS = 'B'
                          and e.ENCOUNTER_DATE between '09/01/2012' and '12/31/2013'
                         group by m.RACE, m.ETHNICITY , CONTRA_END, COUNTY, e.ENCOUNTER_NO
                     [COLOR=#CC0000]) -- end subquery[/color]
where m.PATIENT_NO is not null
  and f.PAYOR_NO = 'CAT'
  and f.PAYOR_CLASS = 'B'
  and e.ENCOUNTER_DATE between '09/01/2012' and '12/31/2013'

as you see from the above all your were clause is referencing table alias/fields that are within a subquery - that is not allowed.

without knowing more about the table keys and relationships I can't even give you a full solution - but will however give a sample that you may adapt to your own.

update ENHFILE
set x12_add_note = temp.note
from ENHFILE
INNER JOIN ( select distinct 
                    ( list of fields that uniquely identify the ENHFILE record to update)
                   ,case
                    when RACE = 'A' then '5'
                    when RACE = 'B' then '2'
                    .....
               from MPFILE m
               join FIDFILE f
               on f.PATIENT_NO = m.PATIENT_NO
               join FMHFILE h 
               on h.PATIENT_NO = m.PATIENT_NO
               join ENHFILE e 
               on e.PATIENT_NO = m.PATIENT_NO
               join FAMFILE l 
               on l.PATIENT_NO = e.PATIENT_NO 
               and l.ENCOUNTER_NO = E.ENCOUNTER_NO
               where m.PATIENT_NO is not null
               and f.PAYOR_NO = 'CAT'
               and f.PAYOR_CLASS = 'B'
               and e.ENCOUNTER_DATE between '09/01/2012' and '12/31/2013'
               where m.PATIENT_NO is not null
                 and f.PAYOR_NO = 'CAT'
                 and f.PAYOR_CLASS = 'B'
                 and e.ENCOUNTER_DATE between '09/01/2012' and '12/31/2013'
               group by m.RACE, m.ETHNICITY , CONTRA_END, COUNTY, e.ENCOUNTER_NO -- this group needs to change to catter for the new fields you need to add to select
           ) temp
on temp.( list of fields that uniquely identify the ENHFILE record to update = enhfile.( list of fields that uniquely identify the ENHFILE record to update

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886