Select within a Select using decode

This is a discussion on Select within a Select using decode within the Oracle Tools forums in Oracle Database category; I am novice at SQL, and I am trying to get only the lab test of GLUCOSE out. The following code works great, but pulls all of the tests. How do I return just one test? I have tried a select within a select, but I keep getting errors. Thanks for any help. SELECT cci.PATCOMMON.NAME, cci.CRITICALVALUESNOTIFICATION_201.patid, cci.CRITICALVALUESNOTIFICATION_201.key, max(decode(cci.CRITICALVALUESNOTIFICATION_201.tag, 5,cci.CRITICALVALUESNOTIFICATION_201.data)) Lab Test , max(decode(cci.CRITICALVALUESNOTIFICATION_201.tag, 1,cci.CRITICALVALUESNOTIFICATION_201.data)) Time Lapse Date , max(decode(cci.CRITICALVALUESNOTIFICATION_201.tag, 56,cci.CRITICALVALUESNOTIFICATION_201.data)) Lab to Phys by Nursing , ...

Go Back   Database Forum > Oracle Database > Oracle Tools

Database Forums

Register FAQ Calendar Search Today's Posts Mark Forums Read
  #1  
Old 05-23-2008, 01:54 PM
Exclamation Select within a Select using decode

I am novice at SQL, and I am trying to get only the lab test of "GLUCOSE" out. The following code works great, but pulls all of the tests. How do I return just one test? I have tried a select within a select, but I keep getting errors. Thanks for any help.

SELECT cci.PATCOMMON.NAME,
cci.CRITICALVALUESNOTIFICATION_201.patid, cci.CRITICALVALUESNOTIFICATION_201.key,
max(decode(cci.CRITICALVALUESNOTIFICATION_201.tag, 5,cci.CRITICALVALUESNOTIFICATION_201.data)) "Lab Test",
max(decode(cci.CRITICALVALUESNOTIFICATION_201.tag, 1,cci.CRITICALVALUESNOTIFICATION_201.data)) "Time Lapse Date",
max(decode(cci.CRITICALVALUESNOTIFICATION_201.tag, 56,cci.CRITICALVALUESNOTIFICATION_201.data)) "Lab to Phys by Nursing",
max(decode(cci.CRITICALVALUESNOTIFICATION_201.tag, 57,cci.CRITICALVALUESNOTIFICATION_201.data)) "Physian to Nursing",
max(decode(cci.CRITICALVALUESNOTIFICATION_201.tag, 39,cci.CRITICALVALUESNOTIFICATION_201.data)) "Lab to Physician",
cci.NAME_517.patid, cci.PATID_3592.patid, cci.SSN_518.patid
FROM cci.CRITICALVALUESNOTIFICATION_201, cci.NAME_517, cci.PATID_3592, cci.SSN_518, cci.PATCOMMON
WHERE
cci.CRITICALVALUESNOTIFICATION_201.patid = cci.NAME_517.patid AND
cci.CRITICALVALUESNOTIFICATION_201.patid = cci.PATID_3592.patid AND
cci.CRITICALVALUESNOTIFICATION_201.patid = cci.SSN_518.patid AND
cci.CRITICALVALUESNOTIFICATION_201.patid = cci.PATCOMMON.patid
GROUP by cci.PATCOMMON.NAME, cci.CRITICALVALUESNOTIFICATION_201.key, cci.CRITICALVALUESNOTIFICATION_201.patid, cci.NAME_517.patid, cci.PATID_3592.patid, cci.SSN_518.patid
Reply With Quote
Reply


Thread Tools
Display Modes



All times are GMT -4. The time now is 10:51 PM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Integrated by bbpixel2008 :: jvbPlugin R1013.368.1

Search Engine Friendly URLs by vBSEO 3.1.0
vB Ad Management by =RedTyger=
In an effort to better serve ads to our visitors, cookies are used on Mydatabasesupport.com. For more information, check out our Privacy Policy.