Re: calculating values to be used later in Oracle SQL

From: Martin Berger <>
Date: Sat, 22 Feb 2014 15:27:23 +0100
Message-ID: <>

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
  (SELECT P.pat_id,

          *Max*(P.creat) MAX_CREAT

   FROM patient_labs P
   GROUP BY P.pat_id,


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);


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,
                *Max*(P.creat) MAX_CREAT
         FROM   patient_labs P
         WHERE  P.creat IS NOT NULL
         GROUP  BY P.pat_id,
     AS (SELECT PT.pat_id,
                *Max*(PT.date_of_trans) MAX_DATE_OF_TRANS
         FROM   pat_transplant PT
         GROUP  BY PT.pat_ud)
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?

On Sat, Feb 22, 2014 at 9:56 AM, Kim Berg Hansen <> wrote:

> Hi, Bill
> On Fri, Feb 21, 2014 at 11:07 PM, William Threlfall <
>> 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.

Received on Sat Feb 22 2014 - 15:27:23 CET

Original text of this message