SQL - Nested query referencing.

Values in nested queries should be linked to the parent SQL statement via aliases/qualifiers. Do not pass/code the values directly into the nested query if the parent also uses the values.

Recommended

SELECT A.NAME_DISPLAY
FROM PS_NAMES A
WHERE A.EMPLID = ‘12345678’
AND A.NAME_TYPE = ‘PRI’
AND A.EFFDT = (
SELECT MAX(B.EFFDT)
FROM PS_NAMES B
WHERE B.EMPLID = A.EMPLID
AND B.NAME_TYPE = A.NAME_TYPE
AND B.EFFDT <= SYSDATE
)
AND A.EFF_STATUS = ‘A’;