Welcome to the NEW CHUG— your ultimate destination for cutting-edge insights and discussions at the intersection of healthcare and technology.

Our platform is dedicated to exploring the dynamic landscape of healthcare technology, empowering professionals, enthusiasts, and innovators alike with the knowledge and resources to navigate the rapidly evolving field.

From telemedicine revolutionizing patient care to the transformative potential of artificial intelligence in diagnostics, we delve into a myriad of topics shaping the future of healthcare delivery. Whether you’re a seasoned healthcare IT professional, an aspiring entrepreneur in the digital health space, or simply curious about the latest innovations, our curated content, expert interviews, and thought-provoking articles provide a comprehensive exploration of healthcare technology’s most pressing issues.

Join us as we navigate the forefront of innovation, driving forward the transformation of healthcare for a healthier, more connected world.

Have questions or suggestions? Please use the contact button below.

Notifications

Clear all

Oracle SQL code to show most recent obs

MEL, SQL, Oracle

Last Post by Anonymous 1 second ago

4 Posts

3 Users

0 Reactions

58 Views

RSS

rwilliams

(@rwilliams)

Posts: 454

Honorable Member

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

jsander

(@jsander)

Posts: 108

Estimable Member

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

jamesedward

(@jamesedward)

Posts: 257

Reputable Member

LASTLABS is the way to go!

Posted : July 22, 2015 4:24 am

rwilliams

(@rwilliams)

Posts: 454

Honorable Member

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

24 Forums
9,940 Topics
39.9 K Posts
1 Online
6,470 Members

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed