Notifications
Clear all
Topic starter
I'm building a SQL report using multiple HDID's with obsvalues from the same encounter form, below I posted my code, I am having trouble figuring out how to pull NULLs from the rest of the HDID's and just pull each line for ML.RPTOBS.
SELECT PERSON.SEARCHNAME, PERSON.ADDRESS1, PERSON.CITY, PERSON.PSTATUS, ML.RPTOBS.OBSVALUE AS AdminDate, RPTOBS_1.OBSVALUE AS DischargeDate, RPTOBS_2.OBSVALUE AS Location,
RPTOBS_3.OBSVALUE AS ProgramSkills, RPTOBS_4.OBSVALUE AS Reason, RPTOBS_5.OBSVALUE AS OverOneHour, PERSON.ADDRESS2
FROM ML.RPTOBS, ML.RPTOBS RPTOBS_1, ML.RPTOBS RPTOBS_5, ML.RPTOBS RPTOBS_2, ML.RPTOBS RPTOBS_3, ML.PERSON PERSON, ML.RPTOBS RPTOBS_4
WHERE ML.RPTOBS.PID = RPTOBS_1.PID AND ML.RPTOBS.SDID = RPTOBS_1.SDID AND ML.RPTOBS.PID = RPTOBS_5.PID AND ML.RPTOBS.SDID = RPTOBS_5.SDID AND ML.RPTOBS.PID = RPTOBS_2.PID AND
ML.RPTOBS.SDID = RPTOBS_2.SDID AND ML.RPTOBS.PID = RPTOBS_3.PID AND ML.RPTOBS.SDID = RPTOBS_3.SDID AND ML.RPTOBS.PID = PERSON.PID AND ML.RPTOBS.PID = RPTOBS_4.PID AND
ML.RPTOBS.SDID = RPTOBS_4.SDID AND (PERSON.PSTATUS = 'A') AND (ML.RPTOBS.HDID = 62157) AND (RPTOBS_1.HDID = 62149) AND (RPTOBS_2.HDID = 43266) AND (RPTOBS_5.HDID = 62158) AND
(RPTOBS_3.HDID = 43267) AND (RPTOBS_4.HDID = 62152) AND (PERSON.SEARCHNAME <> 'AATEST, HEALTHY')
ORDER BY PERSON.SEARCHNAME, ML.RPTOBS.OBSDATE
Any help is appreciated!
Posted : May 9, 2019 5:08 am
Something like this is how I would write that in MSSQL, it looks like you may be using Oracle so you might have to make some changes.
This would require them to have the OBSTERM with HDID = 62157, it would pull in even when they have NULL for 62419. I didn't rewrite your whole query, just enough to give idea. If you need more let me know, I'm no expert in Oracle though.
Select p.PID,
p.LASTNAME,
p.FIRSTNAME,
p.DATEOFBIRTH,
r1.obsdate,
r1.obsvalue,
r2.obsdate,
r2.obsvalue
From RPTOBS r1
INNER JOIN PERSON p ON r1.pid = p.PID
LEFT JOIN RPTOBS r2 ON r1.pid = r2.pid AND r1.sdid = r2.sdid AND r2.hdid = 62149
Where r1.hdid = 62157
Posted : May 10, 2019 9:01 am
Topic starter
This helped a bunch, I'm getting nulls now in the second rptobs_1 column, but im still having trouble now adding a third rptobs here is my code with just two tables
SELECT PERSON.SEARCHNAME, PERSON.ADDRESS1, PERSON.CITY, PERSON.PSTATUS, ML.RPTOBS.OBSVALUE AS AdminDate, RPTOBS_1.OBSVALUE AS DischargeDate, PERSON.ADDRESS2
FROM ML.PERSON PERSON, { oj ML.RPTOBS LEFT OUTER JOIN
ML.RPTOBS RPTOBS_1 ON ML.RPTOBS.SDID = RPTOBS_1.SDID AND RPTOBS_1.HDID = 62149 }
WHERE PERSON.PID = ML.RPTOBS.PID AND (PERSON.PSTATUS = 'A') AND (ML.RPTOBS.HDID = 62157) AND (PERSON.SEARCHNAME <> 'AATEST, HEALTHY')
ORDER BY PERSON.SEARCHNAME
Posted : May 13, 2019 2:59 am
