Forum Stats

  • 3,854,649 Users
  • 2,264,394 Discussions
  • 7,905,747 Comments

Discussions

Multiple identical results in query

Melina Schreiber
Melina Schreiber Member Posts: 10 Green Ribbon

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

Answers

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 4,011 Silver Crown

    One of the joins you have included joins to more than one record... but it is impossible for us to tell since we do not know the data model at all

    Try with a very simple query from the mail report table and start adding one join at a time, at some moment you will see data gets duplicated (or even triple). Then you will know that that is the JOIN that is missing some condition,

    Melina Schreiber