check dup with over partition by instead having multi subquery

WITH SUBVEN1 AS (

  SELECT DISTINCT MATERIAL_NO,SUPPLIER_CODE 

  FROM SUBMAT_VENDOR

  WHERE INTERFACE_FILE_NAME = INF_FILE_NAME_ERR 

  AND MATERIAL_NO IS NOT NULL

), CHECKDUP AS (

  SELECT MATERIAL_NO,SUPPLIER_CODE, count(1) over (partition by MATERIAL_NO) AS s

  FROM  SUBVEN1 

)

SELECT *

FROM CHECKDUP

WHERE s = 1;