Hi! I'm replacing a coworker and my boss is asking me to develop a pentaho report which should retrieve all medical reports signed over a patient. As you will see, I have very little SQL knowledge.
I've somehow managed to get it "working", but the query retrieves repetead reports, which shouldn't happen. And I can't figure out why. Can someone pleaaase throw me a tip??
This is the query I've got so far:
SELECT
pac.PIN AS DNI,
pac.chn as NHC,
(pac.name ||' '||pac.surname1) Paciente,
DECODE(pac.sex_type_key,1000019,'F',1000020,'M') Sexo,
trunc(months_between(sysdate,pac.BIRTH_DATE_TIME) / 12) Edad,
CASE WHEN pac.FAMILY_TELEPHONE IS NOT NULL THEN pac.FAMILY_TELEPHONE ELSE pac.MOBILE_TELEPHONE END AS Telefono,
a.ADDRESS || ' - ' || l1.DEFAULT_TEXT || ', ' || l2.DEFAULT_TEXT || ', '||l3.DEFAULT_TEXT || ' CP: ' || a.PC AS Direccion,
lblseguro.DEFAULT_TEXT AS Seguro,
app.DATE_TIME,
l_motivo.DEFAULT_TEXT motivo_alta,
L_DESTINO.DEFAULT_TEXT DESTINO,
(u.NAME ||' '|| u.SURNAME1) AS Profesional,
lbevol.DEFAULT_TEXT AS Tipo_informe,
r.INSERT_DATE AS Fecha_Firma,
clindiag.CLINICAL_FREE_TEXT AS diagnosticoLIBRE,
CASE WHEN lblcatalogcod.DEFAULT_TEXT IS NULL THEN '-' ELSE lblcatalogcod.DEFAULT_TEXT END AS DESCCIE10,
CASE WHEN lblcatalogdesc.DEFAULT_TEXT IS NULL THEN 'Sin codificar' ELSE lblcatalogdesc.DEFAULT_TEXT END AS CIE10
FROM HEALTH_KERNEL.PATIENT pac
LEFT JOIN HEALTH_KERNEL.EPISODE epi ON pac.PATIENT_KEY = epi.PATIENT_KEY
LEFT JOIN HEALTH_KERNEL.APPOINTMENT app ON epi.PATIENT_KEY = app.PATIENT_KEY
LEFT JOIN HEALTH_KERNEL.OUTPATIENT_CARE_EPISODE OUT_EPI ON OUT_EPI.EPISODE_KEY = APP.OUTPATIENT_CARE_EPISODE_KEY
LEFT JOIN HEALTH_KERNEL.OUTPATIENT_CARE_EPISODE cex_epi ON epi.EPISODE_KEY = cex_epi.EPISODE_KEY
LEFT JOIN HEALTH_KERNEL.SECTION_SERVICE_CENTER ssc ON epi.ADDMISSION_SECTION_KEY = ssc.SECTION_SERVICE_CENTER_KEY
LEFT JOIN HEALTH_KERNEL.SERVICE svc ON ssc.SERVICE_ID = svc.SERVICE_KEY
LEFT JOIN EHCOS.LABEL lblsvc ON svc.SHORT_DESC_LABEL = lblsvc.LABEL_CODE
LEFT JOIN HEALTH_KERNEL."SECTION" sect ON ssc.SECTION_ID = sect.SECTION_KEY
LEFT JOIN EHCOS.LABEL lblsect ON sect.LABEL_SHORT_DESC = lblsect.LABEL_CODE
--Diagnosis
LEFT JOIN EHCS.CLINICAL_DIAG_TECH_EPISODE cldiag ON epi.EPISODE_KEY = cldiag.EPISODE_KEY AND cldiag.active = 1 --- cldiag.MAIN TRAE TODOS LOS DIAGNOSTICOS
LEFT JOIN EHCS.CLINICA_DIAGNOSTI_EPISOD cldiaep ON cldiag.CLINICAL_DIAG_TECH_EPISODE_KEY = cldiaep.CLINICAL_DIAG_TECH_EPISODE_KEY
LEFT JOIN EHCS.CLINICAL_DIAGNOSTIC clindiag ON cldiaep.CLINICAL_DIAGNOSTIC_KEY = clindiag.CLINICAL_DIAGNOSTIC_KEY
LEFT JOIN HEALTH_KERNEL.CATALOG_LOCAL_TERMS catalog ON clindiag.CATALOG_LOCAL_TERMS_KEY = catalog.CATALOG_LOCAL_TERMS_KEY
LEFT JOIN EHCOS.LABEL lblcatalogdesc ON catalog.LABEL_LONG_DESC = lblcatalogdesc.LABEL_CODE
LEFT JOIN EHCOS.LABEL lblcatalogcod ON catalog.LABEL_SHORT_DESC = lblcatalogcod.LABEL_CODE
--Insurance
LEFT JOIN HEALTH_KERNEL.INSURE_DATA_EPISODE insdep ON epi.EPISODE_KEY = insdep.EPISODE_KEY
LEFT JOIN HEALTH_KERNEL.HEALTH_INSURANCE_PLAN inspl ON insdep.HEALTH_INSURANCE_PLAN_KEY = inspl.HEALTH_INSURANCE_PLAN_KEY
LEFT JOIN HEALTH_KERNEL.INSURER ins ON inspl.INSURER_KEY = ins.INSURER_KEY
LEFT JOIN EHCOS.LABEL lblseguro ON lblseguro.label_code=ins.NAME_LONG_DESC
--Agenda
LEFT JOIN HEALTH_KERNEL.AGENDA_SCHEDULE as2 ON app.AGENDA_SCHEDULE_KEY = as2.AGENDA_SCHEDULE_KEY
LEFT JOIN HEALTH_KERNEL.AGENDA a ON as2.AGENDA_KEY = a.AGENDA_KEY
--Discharge reason & destination
LEFT JOIN EHCOS.TYPE_MASTER tm_motivo ON tm_motivo.TYPE_MASTER_KEY = out_epi.OUTPATIE_CA_DISCHAR_REAS_KEY
LEFT JOIN ehcos.LABEL l_motivo ON tm_motivo.LONG_DESC_LABEL = l_motivo.LABEL_CODE
LEFT JOIN EHCOS.TYPE_MASTER TM_DESTINO ON tm_destino.TYPE_MASTER_KEY = out_epi.OUTPATI_C_DISCHA_DESTINAT_KEY
LEFT JOIN ehcos.LABEL l_destino ON tm_destino.LONG_DESC_LABEL = l_destino.LABEL_CODE
LEFT JOIN HEALTH_KERNEL.APPOINTMENT_STATUS appst ON app.APPOINTMENT_KEY = appst.APPOINTMENT_KEY
LEFT JOIN EHCOS.TYPE_MASTER tm ON appst.APPOINTMENT_STATUS_TYPE_KEY = tm.TYPE_MASTER_KEY
LEFT JOIN EHCOS.LABEL lblstatus ON tm.LONG_DESC_LABEL=lblstatus.LABEL_CODE
--Address
left join HEALTH_KERNEL.ADDRESS a on a.PATIENT_KEY=pac.PATIENT_KEY
left JOIN EHCOS.COUNTRY CO ON A.COUNTRY_KEY = CO.COUNTRY_KEY
LEFT JOIN EHCOS.LABEL l1 ON co.LONG_DESC_LABEL = l1.LABEL_CODE
left JOIN EHCOS.STATE st ON a.STATE_KEY = st.STATE_KEY
LEFT JOIN EHCOS.LABEL l2 ON st.LONG_DESC_LABEL = l2.LABEL_CODE
LEFT JOIN ehcos.TOWN Tw ON a.TOWN_KEY = Tw.TOWN_KEY
LEFT JOIN EHCOS.LABEL l3 ON tw.LONG_DESC_LABEL = l3.LABEL_CODE
--Report
LEFT JOIN EHREPORT_GENERATOR.CLINIC_REPORT cr ON epi.EPISODE_KEY = cr.EPISODE_KEY --Evolución
LEFT JOIN EHREPORT_GENERATOR.REPORT r ON r.REPORT_KEY = cr.REPORT_KEY AND r.REPORT_KEY IS NOT NULL
LEFT JOIN EHCOS.LABEL lbevol ON r.TITLE = lbevol.LABEL_CODE
LEFT JOIN EHCOS.PROFESSIONAL prof ON cr.PROFESSIONAL = prof.PROFESSIONAL_KEY --Profesional
LEFT JOIN EHCOS.TYPE_MASTER tm ON prof.PROFESSIONAL_TYPE_KEY = tm.TYPE_MASTER_KEY
LEFT JOIN EHCOS.EH_USER u ON prof.USER_KEY = u.USER_KEY
WHERE 1=1 AND pac.chn <> 0 AND a.SHORT_DESC = 'MFCIRMUJ'
ORDER BY pac.chn
Please, don't