Bill, Use an analytical function instead, something like select ast from (SELECT ast, row_number() over (order by abs(pt.date_of_trans - x.date_of_lab)) rn FROM patient_labs x WHERE x.ast IS NOT NULL AND x.pat_id = pd.pat_id and x.date_of_lab between add_months(pt.date_of_trans,1) - 10) AND add_months(pt.date_of_trans,1) + 10)) where rn = 1 HTH, -- Stéphane Faroult RoughSea Ltd <http://www.roughsea.com> Konagora <http://www.konagora.com> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd> Author, SQL Success <http://www.amazon.com/SQL-Success-Database-Programming-Proficiency/dp/1909765007/>, The Art of SQL <http://www.amazon.com/Art-SQL-Stephane-Faroult/dp/0596008945/>, Refactoring SQL Applications <http://www.amazon.com/Refactoring-SQL-Applications-Stephane-Faroult/dp/0596514972/> On 02/06/2014 09:54 PM, William Threlfall wrote: > The following SQL code picks the first occurrence of an AST lab result > that exists on the latest date during the time period of one month > post-transplant +/- 10 days > > (SELECT ast FROM patient_labs x > WHERE x.ast IS NOT NULL > AND x.pat_id = pd.pat_id > AND x.date_of_lab = > (SELECT MAX(pl.date_of_lab) FROM patient_labs pl > WHERE pl.ast IS NOT NULL > AND pl.pat_id = pd.pat_id > AND (pl.date_of_lab >= (add_months(pt.date_of_trans,1) > - 10) AND pl.date_of_lab <= (add_months(pt.date_of_trans,1) + 10))) > AND ROWNUM < 2) "AST 1 Month", > > What I really want is the AST lab result that exists on the date > closest to one month post-transplant within the range of one month > post-transplant +/- 10 days. > I tried using MEDIAN instead of MAX, but that doesn't consistently > give the AST result on the date closest to one month post-transplant > either. > > So I thought of using a CASE to get the AST (if it exists) on the > exact date of one month post-transplant, or failing that, try +/- 1 > day, then +/- 3 days, +/- 5 days, +/- 7 days and finally +/- 10 days, > otherwise NULL, using the following code: > > (SELECT AST FROM patient_labs x > WHERE x.AST IS NOT NULL > AND x.pat_id = pd.pat_id > AND x.date_of_lab = > CASE WHEN pl.ast IS NOT NULL AND pl.pat_id = pd.pat_id AND > pl.date_of_lab = add_months(pt.date_of_trans,1) THEN pl.date_of_lab > WHEN pl.ast IS NOT NULL AND pl.pat_id = > pd.pat_id AND pl.date_of_lab >= add_months(pt.date_of_trans,1) - 1 AND > pl.date_of_lab <= add_months(pt.date_of_trans,1) + 1 THEN pl.date_of_lab > WHEN pl.ast IS NOT NULL AND pl.pat_id = > pd.pat_id AND pl.date_of_lab >= add_months(pt.date_of_trans,1) - 3 AND > pl.date_of_lab <= add_months(pt.date_of_trans,1) + 3 THEN pl.date_of_lab > WHEN pl.ast IS NOT NULL AND pl.pat_id = > pd.pat_id AND pl.date_of_lab >= add_months(pt.date_of_trans,1) - 5 AND > pl.date_of_lab <= add_months(pt.date_of_trans,1) + 5 THEN pl.date_of_lab > WHEN pl.ast IS NOT NULL AND pl.pat_id = > pd.pat_id AND pl.date_of_lab >= add_months(pt.date_of_trans,1) - 7 AND > pl.date_of_lab <= add_months(pt.date_of_trans,1) + 7 THEN pl.date_of_lab > WHEN pl.ast IS NOT NULL AND pl.pat_id = > pd.pat_id AND pl.date_of_lab >= add_months(pt.date_of_trans,1) - 10 > AND pl.date_of_lab <= add_months(pt.date_of_trans,1) + 10 THEN > pl.date_of_lab > ELSE NULL > END > AND ROWNUM < 2) "AST 1 Month", > > However, the code above produces an error, telling me that > pl.date_of_lab is an invalid identifier. So it seems that I am doing > something wrong in my use of the CASE statement. > > Can anyone give me some idea of how to get what I want? > > Oh and while I'm posting, I have noticed that almost all the posts are > about DBA and/or System Administrator issues, mostly dealing with the > inner workings of Oracle. > I have been a DBA in the past and a System Administrator in the past, > but in my current role, I'm an Oracle SQL Developer. > I am struggling at this point in time because it's been 15 years since > the last time I did Oracle SQL and PL/SQL development. During my last > contract I was using FoxPro 9, and the contract before that I was > using MS SQL Server T-SQL, both of which have built-in procedural > command language to calculate things (e.g. picking out the AST lab > result closest to 1 month post-transplant, or calculating age at most > recent CREA lab result) much easier than I can do in Oracle using pure > SQL. PL/SQL would probably be lots easier, but for now I am trying > to do what I want to do using pure SQL. If I can't do what I want in > pure SQL, then I will try using PL/SQL. > > So if there is a better place than this to get input/advice/help for > SQL or PL/SQL development, please let me know. > There's no point in posting here if nobody (or almost nobody) has any > interest in how to do certain things in an SQL query or a PL/SQL > procedure. > > Thanks and Cheers, - Bill. > > > _ ________________________________ _ > 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.