/*
SELECT
    EMP_CODE AS CODE
    , EMP_LAST_NAME AS SURNAME
    , EMP_FIRST_NAME AS NAME 
FROM
    EMP 
WHERE
    VALID_FLG = '1' 
   -- 社員姓、社員名の半角スペースを空文字に置き換えて比較 
    AND REPLACE (EMP_LAST_NAME || EMP_FIRST_NAME, ' ', '') IN ( 
        -- 副問合せで同姓同名が2件以上存在するデータを抽出
        SELECT
            REPLACE (EMP_LAST_NAME || EMP_FIRST_NAME, ' ', '') AS EMP_NAME 
        FROM
            EMP 
        WHERE
            VALID_FLG = '1' 
        GROUP BY
            EMP_NAME 
        HAVING
            COUNT(*) >= 2
    ) 
ORDER BY
    REPLACE (EMP_LAST_NAME || EMP_FIRST_NAME, ' ', '') ASC
    , EMP_CODE ASC;
*/
    
WITH temp AS (
    SELECT
        REPLACE(emp_last_name, ' ', '') AS emp_last_name
        , REPLACE(emp_first_name, ' ', '') AS emp_first_name
    FROM
        emp
    WHERE
        valid_flg = '1'
    GROUP BY
        REPLACE(emp_last_name, ' ', '')
        , REPLACE(emp_first_name, ' ', '')
    HAVING
        COUNT(1) >= 2
)
SELECT
    emp.emp_code AS CODE
    , emp.emp_last_name AS SURNAME
    , emp.emp_first_name AS NAME
FROM
    emp
    INNER JOIN 
        temp
        ON REPLACE(emp.emp_last_name || emp.emp_first_name, ' ', '') = temp.emp_last_name || temp.emp_first_name
ORDER BY
    REPLACE(emp.emp_last_name || emp.emp_first_name, ' ', '') ASC
    , emp.emp_code ASC