HI,
I read this post (Understanding Join, Keep and Concatenate) and thought it it easy to get the records by using LEFT JOIN or LEFT KEEP. It turned out that it is not easy. The LEFT JOIN (or KEEP) returned all the records including the records does not match. What is my problem?
I this one in first script tab.
Encounters:
with DiabetesPatientList as (
select distinct a.pat_id
from problem_list a
where a.icd9_code like '250.%'
)
select a.pat_id, a.PAT_ENC_CSN_ID,a.Contact_date, a.PCP_PROV_ID, a.FIN_CLASS_C, a.VISIT_PROV_ID, a.VISIT_PROV_TITLE,
a.BMI, a.BP_SYSTOLIC, a.BP_DIASTOLIC, e.DEPARTMENT_NAME,
trunc ((a.contact_date - b.birth_date)/365.25) as "EncounterAge",
c.Prov_name as PCP,
d.prov_name as VisitPhysician,
e.Department_NAME as Department，
extract(Year from a.contact_date) as intYear, extract(Year from a.contact_date)*100+extract(month from a.contact_date) as IntYearMonth
from pat_enc a
inner join patient b on a.pat_id = b.pat_id
INNER JOIN DiabetesPatientList f on f.pat_id=a.pat_id
LEFT JOIN clarity_SER c on a.PCP_PROV_ID =c.prov_id
left join clarity_ser d on d.prov_id = a.visit_prov_id
left join clarity_dep e on a.DEPARTMENT_ID = e.DEPARTMENT_ID
where a.CONTACT_DATE >= to_date('01-Jan-2011', 'dd-mon-yy') and
(a.DEPARTMENT_ID in (1711521800,1710021800)
and a.APPT_STATUS_C=2
and a.ENC_TYPE_C =101;
And the script in the second tab:
Patient:
LOAD *;
left join (Encounters)
sql
select a.pat_id, a.PAT_NAME, a.PAT_MRN_ID, a.Birth_Date, a.CUR_PCP_PROV_ID, g.name as Sex
from patient a
left join ZC_SEX g on g.RCPT_MEM_SEX_C=a.sex_c;
