SQL - Example of Sorting Tables that have config table in Golden

PeopleSoft delivered the config table for Address Type, it can be found via Home > Set Up HRMS > Foundation Tables > Personal > Address Type.

The table name is PS_ADDRESS_TYP_TBL. This table is currently setup as HOME, MAIL, BUSN and PERM etc…

The SQL statement as follow:

SELECT *
FROM PS_ADDRESSES A
WHERE A.EMPLID = '40752994'
AND A.ADDRESS_TYPE =
(SELECT B.ADDRESS_TYPE
FROM PS_ADDRESS_TYP_TBL B
WHERE B.ORDER_BY_SEQ =
(SELECT MIN(B_SQ.ORDER_BY_SEQ)
FROM PS_ADDRESS_TYP_TBL B_SQ, PS_ADDRESSES C
WHERE C.EMPLID = A.EMPLID
AND C.ADDRESS_TYPE = B_SQ.ADDRESS_TYPE
AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT)
FROM PS_ADDRESSES C_ED
WHERE C_ED.EMPLID = C.EMPLID
AND C_ED.ADDRESS_TYPE = C.ADDRESS_TYPE
AND C_ED.EFFDT <= SYSDATE))) AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_ADDRESSES A_ED WHERE A_ED.EMPLID = A.EMPLID AND A_ED.ADDRESS_TYPE = A.ADDRESS_TYPE AND A_ED.EFFDT <= SYSDATE) ;
If students have Home address it will return Home Address, if not Mailing address and so on.