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

SQL Query with multiple obs term values (last value only)

MEL, SQL, Oracle

Last Post by Anonymous 1 second ago

5 Posts

3 Users

0 Reactions

64 Views

RSS

cprice7

(@cprice7)

Posts: 71

Estimable Member

Topic starter

I have the following SQL query which uses the lastlabs view to pull the last obs value for the MHScore observation term. I'd like to add a third column to return the last obs value of another observation term called "MedScore" with HDID # 32401266. Any help is appreciated.

SELECT LASTLABS.PID,OBSVALUE AS MHScore
FROM dbo.OBS INNER JOIN
LASTLABS ON OBS.PID = LASTLABS.PID AND OBS.HDID = LASTLABS.HDID AND OBS.OBSDATE = LASTLABS.MAXLABDATE
WHERE LASTLABS.HDID = '32401265'

I'd then like to create a custom view in SSMS, but am not sure if I can use a view inside another view (use LASTLABS view in the query of a new view). If not, any thoughts on how to restructure it for a custom view?

Thanks!
Crystal

Posted : March 1, 2018 1:10 pm

cliffstark

(@cliffstark)

Posts: 194

Reputable Member

I think this should work:

SELECT LASTLABS.PID,OBSVALUE AS MHScore, (select obsvalue from obs where hdid = '32401266') as MHScore2

FROM dbo.OBS INNER JOIN

LASTLABS ON OBS.PID = LASTLABS.PID AND OBS.HDID = LASTLABS.HDID AND OBS.OBSDATE = LASTLABS.MAXLABDATE

WHERE LASTLABS.HDID = '32401265'

Posted : March 2, 2018 4:15 am

cprice7

(@cprice7)

Posts: 71

Estimable Member

Topic starter

Thank you for your response. Unfortunately I get the same error return with each way I tried before posting. The error says "Msg 512, Level 16, State 1, Line 1. Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >=, or when the subquery is used as an expression.

Do you know how to resolve this error?

Posted : March 2, 2018 5:18 am

rwilliams

(@rwilliams)

Posts: 454

Honorable Member

I was not sure if you wanted to include patients that do not have these obs values at all so I errored on the side of including everything. You could use a where clause to limit it to patients that had one or the other of the observations. Also, I wrote this on Oracle. I don't see anything that shouldn't work on MSSQL but I can't guarantee that it will.
SELECT PERSON.PID, OBS1.OBSVALUE AS MHScore, OBS2.OBSVALUE AS MEDSCORE FROM PERSON LEFT JOIN ( SELECT OBS.* FROM OBS INNER JOIN LASTLABS ON OBS.PID = LASTLABS.PID AND OBS.HDID = LASTLABS.HDID AND OBS.OBSDATE = LASTLABS.MAXLABDATE WHERE LASTLABS.HDID = 32401265 ) OBS1 ON OBS1.PID = PERSON.PID LEFT JOIN ( SELECT OBS.* FROM OBS INNER JOIN LASTLABS ON OBS.PID = LASTLABS.PID AND OBS.HDID = LASTLABS.HDID AND OBS.OBSDATE = LASTLABS.MAXLABDATE WHERE LASTLABS.HDID = 32401266 ) OBS2 ON OBS2.PID = PERSON.PID

Posted : March 2, 2018 7:48 am

cprice7

(@cprice7)

Posts: 71

Estimable Member

Topic starter

rwilliams you are amazing. It worked like a charm. Thanks!

Posted : March 2, 2018 9:03 am

Share:

24 Forums
9,940 Topics
39.9 K Posts
3 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

Powered by wpForo Powered by wpForo version 2.4.0