SELECT EMPLY.*,
       EADDR.*,
       CASE EADDR.EMPLY_ADDR_TYPE
         WHEN 'R' THEN 1
         WHEN 'M' THEN 2
         WHEN 'P' THEN 3
         WHEN NULL THEN 4
       END SORT_KEY
  FROM EMPLY,
       EADDR
WHERE EMPLY.PIN = EADDR.PIN(+)
ORDER BY SORT_KEY