Notifications
Clear all
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
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
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
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
Topic starter
rwilliams you are amazing. It worked like a charm. Thanks!
Posted : March 2, 2018 9:03 am
