need help with subquery in select
I want to pull from a table both the weight and height, they are in same table with a name field of either weight or height and a value field.
I'm trying this, but the subquery returns more than one value:
a.vital_result as height,
(select d.vital_result from MRVL99 d where D.PTID = b.ptid and d.vital_name = 'Weight') as weight
from MRVL99 a,
a.PTID = B.PTID
and a.vital_name = 'Height'
order by b.pat_ext_id
Any ideas are greatly appreciated. I'm also afraid that this only gives me results if a person has both a height and a weight in the table.
Please post table desc
Either way - if table design is any close to what I think it is, a self join will do it, no need for a subquery.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
VITAL_NAME Char(30 Byte)
VITAL_RESULT Char(20 Byte)
Try something like this:
WITH mrvl99 AS
(SELECT 1 ptid, SYSDATE d_vital,'Weight' vital_name, 101.1 vital_result FROM DUAL UNION
SELECT 1, SYSDATE,'Height', 5.2 FROM DUAL UNION
SELECT 2, SYSDATE,'Weight', 212.2 FROM DUAL UNION
SELECT 2, SYSDATE,'Height', 5.7 FROM DUAL UNION
SELECT 3, SYSDATE,'Weight', 183.3 FROM DUAL UNION
SELECT 3, SYSDATE,'Height', 4.11 FROM DUAL)
FROM (SELECT ptid, vital_name, vital_result
PIVOT (MAX( vital_result ) FOR vital_name IN ('Height', 'Weight'));
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Click Here to Expand Forum to Full Width