Error in update 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.

 

[r937]

you're in the wrong forum

what you posted is a sql server error message

try again in forum183

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon