Java Programming

-- yourdata:
with xxxx as
(
select 999 w, null c, 'aaa' name from dual union all
select null, 222, 'aaa' from dual union all
select 911,null, 'bbb' from dual union all
select 922, null, 'bbb' from dual union all
select null, 111, 'bbb' from dual union all
select 933, null ,'ccc' from dual union all
select null, 333, 'ddd' from dual union all
select null, 444, 'ddd' from dual
)
-- query:
select * from xxxx 
where exists(select w from xxxx x where x.name=xxxx.name and w is not null)
   and exists(select c from xxxx x where x.name=xxxx.name and c is not null)

SELECT * FROM tbl t1
WHERE 1= CASE
          WHEN    (SELECT COUNT(w) FROM tbl t2 WHERE t1.nm=t2.nm) >0
              AND (SELECT COUNT(c) FROM tbl t2 WHERE t1.nm=t2.nm)  >0           
          THEN 1
          ELSE 0
          END; 

this is 2nd time i am posting,

 
SQL> 
SQL> Create Table t As
  2  Select 1 r, 999 w, Null c,  'aaa' Name From dual Union All
  3  Select 2 ,  Null , 222   ,  'aaa'      From dual Union All
  4  Select 3 ,  911  , Null  ,  'bbb'      From dual Union All
  5  Select 4 ,  922  , Null  ,  'bbb'      From dual Union All
  6  Select 5 ,  Null , 111   ,  'bbb'      From dual Union All
  7  Select 6 ,  933  , Null  ,  'ccc'      From dual Union All
  8  Select 7 , Null  , 333   ,  'ddd'      From dual Union All
  9  Select 8 , Null  , 444   ,  'ddd'      From dual
 10  /
 
Table created
 
SQL> 
SQL> Select r,
  2         w,
  3         c,
  4         Name
  5  From   t
  6  Where  Exists (Select 'X' From t t1 Where t1.name = t.name And c Is Not Null)
  7  And    Exists (Select 'X' From t t2 Where t2.name = t.name And w Is Not Null)
  8  /
 
         R          W          C NAME
---------- ---------- ---------- ----
         1        999            aaa
         2                   222 aaa
         3        911            bbb
         4        922            bbb
         5                   111 bbb
 
SQL> 

select * from xxxx where name in (
select name from xxxx
group by name
having sum(w) >1 and sum(c)>1
)

SELECT r, w, c, NAME
  FROM (SELECT r, w, c, NAME, COUNT(w) OVER(PARTITION BY NAME) AS ct_w,
               COUNT(c) OVER(PARTITION BY NAME) AS ct_c
          FROM xxxx)
 WHERE ct_w > 0 AND ct_c > 0

with xxxx as
(
select 999 w, null c, 'aaa' name from dual union all
select null, 222, 'aaa' from dual union all
select 911,null, 'bbb' from dual union all
select 922, null, 'bbb' from dual union all
select null, 111, 'bbb' from dual union all
select 933, null ,'ccc' from dual union all
select null, 333, 'ddd' from dual union all
select null, 444, 'ddd' from dual
)

select name from (
select count(w) cnt1,count(c)cnt2,name from xxxx
group by name)
where cnt1>0 and cnt2 >0

with xxxx as(
select 999 w, null c, 'aaa' name from dual union all
select null, 222, 'aaa' from dual union all
select 911,null, 'bbb' from dual union all
select 922, null, 'bbb' from dual union all
select null, 111, 'bbb' from dual union all
select 933, null ,'ccc' from dual union all
select null, 333, 'ddd' from dual union all
select null, 444, 'ddd' from dual)
select Row_Number() over(order by name,w,c) as rn,
w,c,name
from (select w,c,name,
      count(w) over(partition by name) as cnt1,
      count(c) over(partition by name) as cnt2
      from xxxx)
where 1 <= all(cnt1,cnt2);

RN     W     C  NAM
--  ----  ----  ---
 1   999  null  aaa
 2  null   222  aaa
 3   911  null  bbb
 4   922  null  bbb
 5  null   111  bbb