WITH TARGET_NAME AS 
(
  SELECT 
    REPLACE(REPLACE(EMP_LAST_NAME, ' ', ''), '　', '') AS EMP_LAST_NAME
    ,REPLACE(REPLACE(EMP_FIRST_NAME, ' ', ''), '　', '') AS EMP_FIRST_NAME
    ,COUNT(0) AS _COUNT
  FROM EMP 
  WHERE VALID_FLG = '1'
  GROUP BY 1,2
  HAVING _COUNT > 1
)
SELECT 
  EMP_CODE AS CODE 
  ,EMP_LAST_NAME AS SURNAME 
  ,EMP_FIRST_NAME AS NAME 
FROM EMP 
WHERE VALID_FLG = '1'
  AND EXISTS 
  (
    SELECT 0 
    FROM TARGET_NAME AS TN 
    WHERE REPLACE(REPLACE(EMP.EMP_LAST_NAME, ' ', ''), '　', '') = TN.EMP_LAST_NAME
      AND REPLACE(REPLACE(EMP.EMP_FIRST_NAME, ' ', ''), '　', '') = TN.EMP_FIRST_NAME
  )
ORDER BY REPLACE(REPLACE(EMP.EMP_LAST_NAME, ' ', ''), '　', ''), REPLACE(REPLACE(EMP.EMP_FIRST_NAME, ' ', ''), '　', ''), EMP_CODE 
;