title: Oracle SQL Compare 4 date fields return greatest
body: <p>I have 4 date columns CMA_COMPLETED_DATE, APPRAISAL_COMPLETED_DATE, LNA_APPRAISAL_COMPLETED_DATE, APPRAISAL_DATE. I need to do two things with these fields.</p>
  
  <ol>
  <li><p>Find the one that has the greatest value.</p></li>
  <li><p>find the VALUE_AMOUNT that corresponding to that date. Each of the 4 fields are in different tables.</p></li>
  </ol>
  
  <p>I think I have 1 covered but if there is a better way let me know.</p>
  
  <pre><code> WITH Q1
   AS (SELECT/*+ index(mlm.LOAN_NUMBER) +*/
             DISTINCT MLM.LOAN_NUMBER, MLM.RUN_YR_MO
         FROM MORTGMGR.MSP_LOAN_M mlm
        WHERE MLM.RUN_YR_MO = TO_CHAR (ADD_MONTHS (SYSDATE, -1), 'YYYYMM')
              AND mlm.FIRST_PRINCIPAL_BALANCE &gt; 0
              AND MLM.INVESTOR_ID IN ('EJ2', 'G03')),
   q32
   AS (SELECT q1.loan_number,
              mvm.CMA_COMPLETED_DATE,
              MVM.CMA_AS_IS_VALUE,
              mam.APPRAISAL_COMPLETED_DATE,
              MAM.APPRAISED_AS_IS_VALUE,
              mlam.LNA_APPRAISAL_COMPLETED_DATE,
              MLAM.LNA_APPRAISED_AS_IS_VALUE,
              mpm.APPRAISAL_DATE,
              MPM.PROPERTY_VALUE_AMOUNT
         FROM q1
              LEFT JOIN MORTGMGR.MSP_PROPERTY_M mpm
                 ON q1.loan_number = mpm.loan_number
                    AND q1.run_yr_mo = mpm.run_yr_mo
              LEFT JOIN MORTGMGR.MSP_LOAN_APPRAISAL_M mlam
                 ON q1.loan_number = mlam.loan_number
                    AND q1.run_yr_mo = mlam.run_yr_mo
              LEFT JOIN MORTGMGR.MSP_APPRAISAL_M mam
                 ON q1.loan_number = mam.loan_number
                    AND q1.run_yr_mo = mam.run_yr_mo
              LEFT JOIN MORTGMGR.MSP_MARKET_VALUE_M mvm
                 ON q1.loan_number = mvm.loan_number
                    AND q1.run_yr_mo = mvm.run_yr_mo)
   SELECT q1.loan_number,
     TRUNC (
        GREATEST (NVL (q32.CMA_COMPLETED_DATE, SYSDATE - 36500),
                  NVL (q32.APPRAISAL_COMPLETED_DATE, SYSDATE - 36500),
                  NVL (q32.LNA_APPRAISAL_COMPLETED_DATE, SYSDATE - 36500),
                  NVL (q32.APPRAISAL_DATE, SYSDATE - 36500)))
        Current_Value_Dt
   FROM q1 LEFT JOIN q32 ON q1.loan_number = q32.loan_number
  </code></pre>