WITH DUPLICATE_EMP AS (
    SELECT
        EMP_LAST_NAME
        , EMP_FIRST_NAME
        , EMP_LAST_NAME || EMP_FIRST_NAME AS FULL_NAME
    FROM
        EMP
    WHERE
        VALID_FLG = 1   -- 有効
    GROUP BY
        REPLACE(EMP_LAST_NAME, ' ', '')
        , REPLACE(EMP_FIRST_NAME, ' ', '')
    HAVING
        COUNT(EMP_LAST_NAME) > 1
)

SELECT
    EMP_CODE AS CODE
    , EMP_LAST_NAME AS SURNAME
    , EMP_FIRST_NAME AS NAME
FROM
    EMP
WHERE
    REPLACE(EMP_LAST_NAME, ' ', '') || REPLACE(EMP_FIRST_NAME, ' ', '')
    IN (
        SELECT
            FULL_NAME
        FROM
            DUPLICATE_EMP
    )
ORDER BY
    REPLACE(EMP_LAST_NAME, ' ', '') ASC
    , REPLACE(EMP_FIRST_NAME, ' ', '') ASC
    , EMP_CODE ASC
;