More Common Table Expressions!

Maybe I am beating a dead horse here, but I use CTE's daily.  Today I was asked to look into self service address changes that were pending approval to provide a list of address changes that have not been processed.

First I created a SQL statement that allowed me to identify active employee's with pending address changes on the HR_ADDR_CHG_DAT.

This is what I came up with:

SELECT *
  FROM PS_HR_ADDR_CHG_DAT A
 WHERE ACTION_DT_SS = (
 SELECT MAX(B.ACTION_DT_SS)
  FROM PS_HR_ADDR_CHG_DAT B
 WHERE A.EMPLID = B.EMPLID
   AND A.ADDRESS_TYPE = B.ADDRESS_TYPE
   AND B.ACTION_DT_SS <= TO_DATE(SYSDATE))
   AND A.EFFSEQ = (
 SELECT MAX(Z.EFFSEQ)
  FROM PS_HR_ADDR_CHG_DAT Z
 WHERE A.EMPLID = Z.EMPLID
   AND A.ADDRESS_TYPE = Z.ADDRESS_TYPE
   AND A.ACTION_DT_SS = Z.ACTION_DT_SS)
   AND A.ADDRESS_TYPE = 'HOME'
   AND A.SS_STAT_INDICATOR = 'M'
   AND EXISTS(
 SELECT 'X'
  FROM PS_EMPLOYEES X
 WHERE A.EMPLID = X.EMPLID )
 Then I knew I wanted to compare the results of this SQL with current address data that can be found in the PS_ADDRESSES4_VW.  So rather then joining this record into the SQL above I used a CTE to create a dynamic view and joined the current address record to the CTE.

 WITH ADDR_CHG AS (
 SELECT *
  FROM PS_HR_ADDR_CHG_DAT A
 WHERE ACTION_DT_SS = (
 SELECT MAX(B.ACTION_DT_SS)
  FROM PS_HR_ADDR_CHG_DAT B
 WHERE A.EMPLID = B.EMPLID
   AND A.ADDRESS_TYPE = B.ADDRESS_TYPE
   AND B.ACTION_DT_SS <= TO_DATE(SYSDATE))
   AND A.EFFSEQ = (
 SELECT MAX(Z.EFFSEQ)
  FROM PS_HR_ADDR_CHG_DAT Z
 WHERE A.EMPLID = Z.EMPLID
   AND A.ADDRESS_TYPE = Z.ADDRESS_TYPE
   AND A.ACTION_DT_SS = Z.ACTION_DT_SS)
   AND A.ADDRESS_TYPE = 'HOME'
   AND A.SS_STAT_INDICATOR = 'M'
   AND EXISTS(
 SELECT 'X'
  FROM PS_ZZ_IDM_DATA X
 WHERE A.EMPLID = X.EMPLID2
   AND X.HR_STATUS IN ('A','L')))
 SELECT SS.EMPLID
 , SS.effseq
 , SS.ADDRESS_TYPE
 , SS.ADDRESS1 SS_ADDRESS1
 , CUR.ADDRESS1
 , SS.ADDRESS2 SS_ADDRESS2
 , CUR.ADDRESS2
 , SS.CITY SS_CITY
 , CUR.CITY
 , SS.POSTAL SS_POSTAL
 , CUR.POSTAL
 , SS.STATE SS_STATE
 , CUR.STATE
  FROM ADDR_CHG SS
  , PS_ADDRESSES4_VW CUR
 WHERE CUR.EMPLID = SS.EMPLID
   AND CUR.ADDRESS_TYPE = 'HOME'
   AND (UPPER(SS.ADDRESS1) <> UPPER(CUR.ADDRESS1)
    OR UPPER(SS.ADDRESS2) <> UPPER(CUR.ADDRESS2)
    OR UPPER(SS.CITY) <> UPPER(CUR.CITY)
    OR UPPER(SS.STATE) <> UPPER(CUR.STATE)
    OR SS.POSTAL <> CUR.POSTAL)


 This simplifies the SQL in my opinion.