WITH t(name, value) AS (
	SELECT 'A', 0 FROM dual UNION ALL 
	SELECT 'A', 8 FROM dual UNION ALL
	SELECT 'B', 0 FROM dual UNION ALL
	SELECT 'C', 8 FROM dual
)

SELECT name, value
FROM 
(
	SELECT name, value, count(DECODE(value, 0, 1)) OVER(PARTITION BY name) cnt
	FROM t
)
WHERE cnt > 0

우선 감사드립니다. 그러나 쿼리 실행해보면 "ORA-32033: 지원되지 않는 열 별칭 지정 "이라는 에러가 발생합니다. 

오라클 10g에서는 어떻게 하는지요?

오라클 10g는 별칭 설정을 달리 하셔야 합니다.

by 랑에 1님의 쿼리를 아래와 같이 변경 하시면 됩니다.

WITH t AS (
    SELECT 'A'  name, 0 value  FROM dual UNION ALL
    SELECT 'A', 8 FROM dual UNION ALL
    SELECT 'B', 0 FROM dual UNION ALL
    SELECT 'C', 8 FROM dual
)
SELECT name, value
FROM
(
    SELECT name, value, count(DECODE(value, 0, 1)) OVER(PARTITION BY name) cnt
    FROM t
)
WHERE cnt > 0


다른 방법은


WITH t AS (
    SELECT 'A'name , 0 value FROM dual UNION ALL
    SELECT 'A', 8 FROM dual UNION ALL
    SELECT 'B', 0 FROM dual UNION ALL
    SELECT 'C', 8 FROM dual
)
SELECT
*
FROM t a
WHERE EXISTS (SELECT 1 FROM t b 
               WHERE a.name = b.name
                 AND b.value = 0) 

답변 감사드립니다. 다시 한번 많은 도움되었습니다. 감사합니다.!

-- In SubQuery
SELECT *
  FROM t
 WHERE name IN (SELECT name FROM t WHERE value = 0)
;
-- Exists SubQuery
SELECT *
  FROM t m
 WHERE EXISTS (SELECT 1 FROM t s WHERE s.value = 0 AND s.name = m.name)
;
-- Self Join
SELECT m.*
  FROM t m
     , t s
 WHERE s.value = 0
   AND s.name = m.name
;
-- Analytic Function
SELECT *
  FROM (SELECT name, value
             , MIN(DECODE(value, 0, 0)) OVER(PARTITION BY name) x
          FROM t
        )
 WHERE x = 0
;