Re: calculating values to be used later in Oracle SQL
From: <Ronan.Merrick_at_cso.ie>
Date: Fri, 21 Feb 2014 17:13:54 +0000
Message-ID: <OF07AEDE5F.56211033-ON80257C86.005E88F9-80257C86.005EA89A_at_cso.ie>
Question for the SQL experts …
I need to calculate a value and then use it many times as a criterion for other selections. What is the best way to do that in Oracle SQL?
For example, I need to calculate, for each patient, the date of last transplant (call it LASTTX) and then select a bunch of lab results where one of the criteria in the WHERE clause would be DATE_OF_LAB > LASTTX.
In the following example I want to select the greatest non-NULL CREAT lab result from the most recent DATE_OF_LAB where the DATE_OF_LAB is greater than the date of last transplant.
This subquery gives me the date of last transplant:
(TO_DATE((MAX(PT.DATE_OF_TRANS) OVER (PARTITION BY PT.PAT_ID ORDER BY PT.PAT_ID
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)), 'yyyy.mm.dd')) AS LASTTX,
However, if I try to use LASTTX in subsequent SELECT subquery statements, I get an error that it is an invalid identifier.
Is there a way to do what I want without having to use that code snippet dozens of times, as in:
(SELECT MAX(CREAT) KEEP (DENSE_RANK FIRST ORDER BY X.DATE_OF_LAB DESC, X.CREAT DESC)
FROM PATIENT_LABS X
WHERE X.CREAT IS NOT NULL
AND X.PAT_ID = PD.PAT_ID
AND X.DATE_OF_LAB > LASTTX) AS MRCREAT
The nuisance way would be to re-use that code snippet over and over again, such as:
(SELECT MAX(CREAT) KEEP (DENSE_RANK FIRST ORDER BY X.DATE_OF_LAB DESC, X.CREAT DESC)
FROM PATIENT_LABS X
WHERE X.CREAT IS NOT NULL
AND X.PAT_ID = PD.PAT_ID
AND X.DATE_OF_LAB >
(SELECT MAX(PT.DATE_OF_TRANS) OVER (PARTITION BY PT.PAT_ID ORDER BY PT.PAT_ID
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM PAT_TRANSPLANT PT)
) AS MRCREAT
I think there ought to be a way to calculate variables and use them later on, but I’m just not clueing in at the moment.
Thanks, - Bill.
_______________________________________
Willliam J. (Bill) Threlfall, MSc
Clinical Informatics Coordinator - OTTR
Transplant Services, University of Alberta Hospital
Alberta Health Services
Aberhart Centre, Room 9221
8440 112 Street
Edmonton, AB T6G 2B7
Telephone: 780-407-6175 FAX: 780-407-8981
Alberta Health Services
www.albertahealhservices.ca
________________________________
This message and any attached documents are only for the use of the intended recipient(s), are confidential and may contain privileged information. Any unauthorized review, use, retransmission, or other disclosure is strictly prohibited. If you have received this message in error, please notify the sender immediately, and then delete the original message. Thank you.
Date: Fri, 21 Feb 2014 17:13:54 +0000
Message-ID: <OF07AEDE5F.56211033-ON80257C86.005E88F9-80257C86.005EA89A_at_cso.ie>
Hi Bill,
It sounds like something you might use subquery factoring for....
http://www.oracle-base.com/articles/misc/with-clause.php
Ronan Merrick
Oracle DBA
Census IT
Ph: (+3531) 8951309
William Threlfall <William.Threlfall@albertahealthservices.ca>
Sent by: oracle-l-bounce_at_freelists.org 21/02/2014 17:10
|
|
Question for the SQL experts …
I need to calculate a value and then use it many times as a criterion for other selections. What is the best way to do that in Oracle SQL?
For example, I need to calculate, for each patient, the date of last transplant (call it LASTTX) and then select a bunch of lab results where one of the criteria in the WHERE clause would be DATE_OF_LAB > LASTTX.
In the following example I want to select the greatest non-NULL CREAT lab result from the most recent DATE_OF_LAB where the DATE_OF_LAB is greater than the date of last transplant.
This subquery gives me the date of last transplant:
(TO_DATE((MAX(PT.DATE_OF_TRANS) OVER (PARTITION BY PT.PAT_ID ORDER BY PT.PAT_ID
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)), 'yyyy.mm.dd')) AS LASTTX,
However, if I try to use LASTTX in subsequent SELECT subquery statements, I get an error that it is an invalid identifier.
Is there a way to do what I want without having to use that code snippet dozens of times, as in:
(SELECT MAX(CREAT) KEEP (DENSE_RANK FIRST ORDER BY X.DATE_OF_LAB DESC, X.CREAT DESC)
FROM PATIENT_LABS X
WHERE X.CREAT IS NOT NULL
AND X.PAT_ID = PD.PAT_ID
AND X.DATE_OF_LAB > LASTTX) AS MRCREAT
The nuisance way would be to re-use that code snippet over and over again, such as:
(SELECT MAX(CREAT) KEEP (DENSE_RANK FIRST ORDER BY X.DATE_OF_LAB DESC, X.CREAT DESC)
FROM PATIENT_LABS X
WHERE X.CREAT IS NOT NULL
AND X.PAT_ID = PD.PAT_ID
AND X.DATE_OF_LAB >
(SELECT MAX(PT.DATE_OF_TRANS) OVER (PARTITION BY PT.PAT_ID ORDER BY PT.PAT_ID
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM PAT_TRANSPLANT PT)
) AS MRCREAT
I think there ought to be a way to calculate variables and use them later on, but I’m just not clueing in at the moment.
Thanks, - Bill.
_______________________________________
Willliam J. (Bill) Threlfall, MSc
Clinical Informatics Coordinator - OTTR
Transplant Services, University of Alberta Hospital
Alberta Health Services
Aberhart Centre, Room 9221
8440 112 Street
Edmonton, AB T6G 2B7
Telephone: 780-407-6175 FAX: 780-407-8981
Alberta Health Services
www.albertahealhservices.ca
________________________________
This message and any attached documents are only for the use of the intended recipient(s), are confidential and may contain privileged information. Any unauthorized review, use, retransmission, or other disclosure is strictly prohibited. If you have received this message in error, please notify the sender immediately, and then delete the original message. Thank you.
Subject to the disclaimer at http://www.cso.ie/en/emaildisclaimer/
Faoi réir ag an séanadh ag http://www.cso.ie/en/seanadhriomhphoist/
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Feb 21 2014 - 18:13:54 CET
