Re: calculating values to be used later in Oracle SQL

select
* (SELECT MAX(CREAT) KEEP (DENSE_RANK FIRST ORDER BY X.DATE_OF_LAB DESC,
X.CREAT **DE**SC)*
* FROM PATIENT_LABS X*

• WHERE X.CREAT IS NOT NULL*
• AND X.PAT_ID = PD.PAT_ID *
• AND X.DATE_OF_LAB > sq1.lasttx* *)** AS MRCREAT*
  *from *
  (select
• (TO_DATE((MAX(PT.DATE_OF_TRANS) OVER (PARTITION BY P**T**.PAT_ID* *ORDER BY P**T**.PAT_ID*
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)), 'yyyy.mm.dd')) as lasttx*
• from t1, t2 ) * ) sq1 /

Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance, RAC and EBS
Blog: http://orainternals.wordpress.com/ Oracle ACE Director and OakTable member <http://www.oaktable.com/>

Co-author of the books: Expert Oracle
Practices<http://tinyurl.com/book-expert-oracle-practices/> , Pro Oracle SQL, <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert
RAC Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices <http://tinyurl.com/book-expert-plsql-practices>

<http://tinyurl.com/book-expert-plsql-practices>

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