Re: calculating values to be used later in Oracle SQL

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.

>
> *Cheers, - Bill.*
>
>
>

Regards

Kim Berg Hansen

http://dspsd.blogspot.com
kibeha_at_gmail.com
_at_kibeha

--
http://www.freelists.org/webpage/oracle-l