SELECT
    EMP_CODE AS CODE
    ,REPLACE(EMP_LAST_NAME," ","") AS SURNAME
    ,REPLACE(EMP_FIRST_NAME," ","") AS NAME
FROM  EMP
JOIN 
    (
        SELECT 
            EMP_KANA_NAME
        FROM 
            (SELECT
                *
            FROM EMP
            WHERE VALID_FLG = 1 
            )
        GROUP BY EMP_KANA_NAME
        HAVING count(EMP_KANA_NAME) > 1
    ) DUPE
ON EMP.EMP_KANA_NAME = DUPE. EMP_KANA_NAME
ORDER BY 
    SURNAME ASC, NAME ASC, CODE ASC