WITH table1 AS(
SELECT
    EMP_CODE AS CODE
    ,REPLACE(EMP_LAST_NAME, ' ', '') AS SURNAME
    ,REPLACE(EMP_FIRST_NAME, ' ', '') AS NAME
    ,REPLACE(EMP_LAST_NAME, ' ', '')||REPLACE(EMP_FIRST_NAME, ' ', '') AS FULLNAME
FROM EMP
WHERE VALID_FLG = 1
),
table2 AS(
SELECT 
    FULLNAME
    ,COUNT(CODE) AS match_name_cnt
FROM table1
GROUP BY 1
HAVING match_name_cnt > 1
)
,
TABLE3 AS(
SELECT
    CODE
    ,SURNAME
    ,NAME
FROM table1
WHERE FULLNAME IN (SELECT FULLNAME FROM table2)
)

SELECT
    EMP_CODE AS CODE
    ,EMP_LAST_NAME AS SURNAME
    ,EMP_FIRST_NAME AS NAME
FROM EMP
WHERE EMP_CODE IN (SELECT CODE FROM table3)
ORDER BY 2,1
;