select
    emp_code as CODE,
    emp_last_name as SURNAME,
    emp_first_name as NAME
from
    emp
where
--重複者テーブルに姓名が含まれている（コードだとだめ）
    replace(emp_last_name||emp_first_name,' ','') in 
        (
        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
;