Re: calculating values to be used later in Oracle SQL
Date: Sat, 22 Feb 2014 15:27:23 +0100
Message-ID: <CALH8A921OcZv+QGTHC6Jujcru1YzgsB4n4Nxn=JaUGQB80jHaQ_at_mail.gmail.com>
Bill, Kim,
I tried to understand the "reusability" of code. My assumption is the same
"snipplet" should be reused in different queries= (or on different places
within the same query) so a view comes to my mind.
What's about (untested):
CREATE VIEW v_patient_labs_max_creat
AS
(SELECT P.pat_id,
P.date_of_lab,
*Max*(P.creat) MAX_CREAT
FROM patient_labs P
WHERE P.creat IS NOT NULL
GROUP BY P.pat_id,
P.date_of_lab);
CREATE VIEW v_pat_transplant_max_date_trns
AS
(SELECT PT.pat_id,
*Max*(PT.date_of_trans) MAX_DATE_OF_TRANS
FROM pat_transplant PT
GROUP BY PT.pat_ud);
SELECT PLMC.pat_id,
PLMC.max_creat
FROM v_patient_labs_max_creat PLMC,
v_pat_transplant_max_date_trns PTMDT WHERE PLMC.pat_id = PTMDT.pat_id
AND PLMC.date_of_lab > PTMDT.max_date_of_trans;
Sometimes it also might be useful to put the subquery into a WITH clause,
e.g.
WITH v_patient_labs_max_creat
AS (SELECT P.pat_id,
P.date_of_lab,
*Max*(P.creat) MAX_CREAT
FROM patient_labs P
WHERE P.creat IS NOT NULL
GROUP BY P.pat_id,
P.date_of_lab),
v_pat_transplant_max_date_trns
AS (SELECT PT.pat_id,
*Max*(PT.date_of_trans) MAX_DATE_OF_TRANS
FROM pat_transplant PT
GROUP BY PT.pat_ud)
SELECT PLMC.pat_id,
PLMC.max_creat
FROM v_patient_labs_max_creat PLMC,
v_pat_transplant_max_date_trns PTMDT
WHERE PLMC.pat_id = PTMDT.pat_id
AND PLMC.date_of_lab > PTMDT.max_date_of_trans;
To make the views more useful for different purposes you can put all possible interesting aggregates on table patient_labs in relation to pat_id, date_of_lab into one view. Not only *Max*(P.creat) but also *Min*(P. creat) and anything else which might be of any value for you. If you don't need *Min*(P.creat) in your specific query, Oracle will not calculate it at all, so there is no waste of resources.
Maybe this comes close to your needs?
Martin
On Sat, Feb 22, 2014 at 9:56 AM, Kim Berg Hansen <kibeha_at_gmail.com> wrote:
> Hi, Bill
>
>
> On Fri, Feb 21, 2014 at 11:07 PM, William Threlfall <
> William.Threlfall_at_albertahealthservices.ca> wrote:
>
>> *Hi Kim,*
>>
>>
>>
>> *Thanks for your input. **J*
>>
>>
>>
>> *Yeah, I thought of using nested queries. I was hoping there was an
>> easier way, especially a way to avoid repeating the same lengthy pieces of
>> code in multiple subqueries dozens of times. Cut and paste works well for
>> that, but it makes a long bloated-code query, and it seems very inefficient
>> to me to have to recalculate the same thing over and over again.*
>>
>>
> The nested queries is the method to avoid cut-and-paste repeating same
> expressions.
>
>
>>
>>
>> *Almost all the query requests I deal with will require one row of
>> specific single values for each patient, if that's what you mean by "scalar
>> subqueries".*
>>
>> *For example, pat_id, ID2, ID3 (where ID2 and ID3 are from a one-to-many
>> table and of specific ID_TYPE for each), patient status description (from a
>> one-to-many table linked by patient_status code), patient program
>> description (from a one-to-many table linked by program_id code), then
>> things like the date_of_lab and max or min of the most recent of a bunch of
>> lab results that aren't null (along with the age of the patient on that
>> date) and are after the most recent transplant date, or specific lab
>> results pre-transplant, and then closest to 1 month, 3 months, 6 months and
>> 1 year post-transplant, either for all transplants or only for the most
>> recent transplant. Of course, there can be many lab results of the same
>> type on the same date and on different dates.*
>>
>>
>>
>
> Some of the things you might approach differently than 4 identical scalar
> subqueries differing only in 1 month / 3 months / 6 months / 12 months as
> that hits the same data 4 times. That might possibly be rewritten to a join
> that gathers all 4 results in one pass of the data. Multiple scalar
> subqueries can be temptingly "easy" but once you start feeling (as you do)
> that it is very repetitive, perhaps it is time to look into alternatives?
>
>
>> *Other types of requests require for each patient the date and
>> description and outcome of certain actions of specific action_types during
>> the time period starting from evaluation_date to evaluation_end_date, along
>> with the time intervals (in days, or possibly hours) from evaluation_date
>> to action1, action1 to action2, action2 to action3, and action3 to
>> evaluation_end_date (if those actions exist during that time period that
>> is).*
>>
>>
>>
>> *To answer other people's questions, I am using SQL Developer, creating
>> queries using the Worksheet and/or Query Builder. I am trying to use pure
>> SQL, rather than PL/SQL or SQL*PLUS, to hopefully avoid compatibility
>> issues and avoid looping through the entire database sequentially. By
>> "later on", what I mean is that I would like to be able to calculate things
>> like date of last transplant, or date of most recent lab result X that
>> isn't null and is after the last transplant date, or max or min (along with
>> the corresponding date) of most recent lab result Y after the last
>> transplant date, so that I can then use those values elsewhere (i.e. "later
>> on") in the same query to calculate age at certain events or select out
>> certain date ranges and/or certain value ranges of the lab results.*
>>
>>
> As you are answering other peoples questions, you should have used "reply
> all" so your reply didn't go only to me, but also to oracle-l :-)
> I've put oracle-l as recipient of this mail to ensure that the others get
> your comments as well...
>
>>
>>
>> *Don't shoot me, but my background is in FoxPro (and later on SQL Server)
>> and SPSS, where calculating and storing specific values for each patient
>> for use later on in the same query (or program) was fairly simple to do.*
>>
>>
> Nobody is shooting you, but from your original question it was difficult
> to understand that when you said "reuse value later on", you meant *within
> the same* query. Many of the other answers you got assumed you were
> needing to reuse values retrieved in one query in a different query and
> then the answer to your question would very much depend on the environment.
> It was because your original question wasn't really clear that you were
> talking multiple scalar subqueries within the same "master" query. (I
> guessed it because your cut-and-pasted code snippets contained "as <column
> alias>" ;-)
>
>>
>>
>> *I may need to use PL/SQL to calculate those kinds of specific values
>> (usually one value for each patient) and store them in variables so I can
>> use them in other calculations or to filter the results. I'm not sure yet.*
>>
>>
> For doing it completely as scalar subqueries in a "master" query, it would
> not be much useful to use PL/SQL.
> But it just *might* be possible that your application is a case where you
> profitably could exchange a lot of the scalar subquerying with function
> calls. Normally I very much advocate doing as much as you can in straight
> SQL alone, but no rule without exception and there may sometimes be good
> arguments for doing it with lots of functions in a package, for example.
> Even though it will be less efficient getting the data and there will be
> overhead in context switching between SQL and PL/SQL, you may have a case
> where the reusability of PL/SQL will outweigh the downsides.
>
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Feb 22 2014 - 15:27:23 CET