quote:

---

Originally posted by nt4vn

hello, anyone could help me to find the problem of my query. I need to modify the code from my co-worker and add new subquery for table itcorres, but I got syntax error. Wonder if you can help me to organize the code and find out the problem. Thanks a bunch! The blue highlighed that is my new subquery. I wonder I put in the right place.

SELECT
distinct A.CASE_ID,
B.ROOTKEY,
C.LAST_NAME,
C.FIRST_NAME,
a.CORRES_NAME,
B.CORRES_1ST_ADDR,
B.CORRES_2ND_ADDR,
B.CITY,
B.STATE,
B.ZIP_5,
B.PHONE_NUM as PHONE,
A.CALCULATED_TAX
FROM
DBA.ITCORRES B
inner join
(SELECT
replace (replace (replace (replace (replace (replace (replace(replace(replace(replace(replace
(C.CORRES_NAME, ' ESQ', ''),
' ATTY', ''), '-ESQ', ''), '-ATTY',''),'.',''),',', ''),'JR', ''),'ATTORNEY', ''),'-',''), 'III', ''), '&', '')
as CORRES_NAME,
CC_FILE_NO,
B.CASE_ID,
B.CALCULATED_TAX
FROM DBA.TKESTAT A on a.cc_file_no=c.rootkey
INNER JOIN DBA.tkcasit D ON a.estate_id = d.estate_id
INNER JOIN DBA.TKCASE B ON B.case_id = d.case_id
inner join (
select xx.rootkey, max(corres_create_dt) as createdate
from dba.itcorres
where corres_type='P'
group by rootkey
)c on c.rootkey=A.cc_file_no

WHERE B.STATUS_ID <> 2
AND D.ACN_DLN in ('101', '102', '201', '202')
AND B.CASE_TYPE_ID = 1
AND CORRES_NAME <>''
AND C.CORRES_TYPE ='P'
GROUP BY CORRES_NAME, CC_FILE_NO, B.CASE_ID, B.CALCULATED_TAX
) a ON a.CC_FILE_NO= B.ROOTKEY, a.createdate=b.corres_create_dt

INNER JOIN DBA.ITGNINFO c ON b.ROOTKEY = C.ROOTKEY
WHERE B.CORRES_TYPE = 'P'
and a.case_id=360580

ORDER BY C.LAST_NAME, C.FIRST_NAME

---