Notifications
Clear all
Topic starter
I have been writing quite a few Crystal reports that require me to check the most recent OBS value for multiple different OBS terms. It was frustrating because I was having to join the obs row multipe times, perform outer joins and group by person to make sure I got everything, didn't exclude rows and make sure that only one row showed up per person. I ended up deciding that the best way to perform this was via an SQL function that I could pass a PID and OBSHEAD.NAME to and have it return the most recent value. I was thinking about making a second one that would possibly return the OBSDATE but I have not as of yet. I wanted to share my code with the community incase it helps someone else out and to also get feedback on it.
Here is the sql function:
CREATE OR REPLACE FUNCTION "ML"."IMA_GET_OBS" (PID_IN IN NUMBER, OBSNAME_IN IN VARCHAR2)
RETURN VARCHAR2
IS OBSVALUE_OUT VARCHAR2(2000);
BEGIN
SELECT OBS1.OBSVALUE INTO OBSVALUE_OUT FROM OBS OBS1 INNER JOIN
(SELECT MAX(OBS.OBSID) AS OBSID FROM OBS INNER JOIN OBSHEAD ON OBS.HDID = OBSHEAD.HDID INNER JOIN PERSON ON PERSON.PID = OBS.PID
WHERE OBS.XID = 1e+035 AND OBS.CHANGE IN (1,2,5,6) AND OBSHEAD.NAME = OBSNAME_IN AND PERSON.PID = PID_IN GROUP BY OBS.PID) SUB1 ON OBS1.OBSID = SUB1.OBSID;
RETURN(OBSVALUE_OUT);
END;
/
GRANT EXECUTE ON "ML"."IMA_GET_OBS" TO "REPORT";
It returns signed or unsigned OBS values. If you only want signed, you would change the function to only allow obs.change to be 1 or 2.
To use it in a crystal report you simply need to put the following in an "SQL Expression" and it will return the latest OBS value, in this examle it returns the latest OBS for "REF_CCC":
(ML.IMA_GET_OBS("PERSON"."PID", 'REF_CCC'))
Thoughts?
Posted : July 21, 2015 5:03 pm
Very nice! There is also a SQL view in CPS called LastLabs. I think it shows one entry for each obs term for each patient, and contains PID,HDID and MAXLABDATE. Here is an example from the last time I used it for a query:
SELECT LASTLABS.MAXLABDATE, OBS.OBSVALUE, PatientProfile.First, PatientProfile.Last
FROM OBS INNER JOIN
LASTLABS ON OBS.PID = LASTLABS.PID AND OBS.HDID = LASTLABS.HDID AND OBS.OBSDATE = LASTLABS.MAXLABDATE INNER JOIN
PatientProfile ON LASTLABS.PID = PatientProfile.PId
WHERE (LASTLABS.HDID = 95886) AND (LOWER(OBS.OBSVALUE) LIKE '%secmsg%') AND (LOWER(OBS.OBSVALUE) 'secmsg')
Posted : July 22, 2015 3:37 am
LASTLABS is the way to go!
Posted : July 22, 2015 4:24 am
Topic starter
I was unaware of the LASTLABS view. It appears to exist in Oracle/EMR as well. I may use it in the future but I was really looking for a function to pull the information in with as I have a bunch of OBS I need in the same report and I would have to join at least 2 extra tables per OBS to get the value. The one thing I did not like about my function was that it relied on the latest entered OBS not the newest obsdate. I used the logic from LASTLABS to come up with a new version that is based on OBSDATE instead of OBSID. I also added a BEGIN_DATE as an optional parameter which allows you to only return an OBSVALUE newer than a certain date.
In case anyone is interested:
create or replace FUNCTION IMA_GET_OBS(PID_IN IN NUMBER, OBSNAME_IN IN VARCHAR2, BEGIN_DATE IN DATE DEFAULT TO_DATE('1960-01-01', 'YYYY-MM-DD'))
RETURN VARCHAR2
IS OBSVALUE_OUT VARCHAR2(2000);
BEGIN
SELECT OBS2.OBSVALUE INTO OBSVALUE_OUT FROM OBS OBS2 INNER JOIN
(SELECT OBS1.PID, OBS1.HDID, MAX(OBS1.OBSDATE) AS OBSDATE FROM OBS OBS1
INNER JOIN OBSHEAD OH1 ON OBS1.HDID = OH1.HDID
WHERE OBS1.CHANGE IN (2,6) AND OBS1.XID = 1e+35
AND OH1.NAME = OBSNAME_IN AND OBS1.PID = PID_IN
GROUP BY OBS1.PID, OBS1.HDID) SUBQ1 ON OBS2.PID = SUBQ1.PID
AND OBS2.HDID = SUBQ1.HDID AND OBS2.OBSDATE = SUBQ1.OBSDATE
AND OBS2.OBSDATE >= BEGIN_DATE
AND OBS2.XID = 1e+35 AND OBS2.CHANGE IN (2,6) AND ROWNUM < 2;
RETURN(OBSVALUE_OUT);
END;
GRANT EXECUTE ON "ML"."IMA_GET_OBS" TO "REPORT";
GRANT EXECUTE ON "ML"."IMA_GET_OBS" TO "MLREPORT";
EDIT: I added the grant lines in and I am now properly converting the date with to_date()
Posted : July 22, 2015 6:08 am
