Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQL Query

WilliamsOct 18 2010 — edited Oct 21 2010
Hi Friends,

I have a table called xxxx and data as follows;

rownum w c name
------------------------------------------------------------
1 999 (null) aaa
2 (null) 222 aaa
3 911 (null) bbb
4 922 (null) bbb
5 (null) 111 bbb
6 933 (null) ccc
7 (null) 333 ddd
8 (null) 444 ddd


My Required o/p as follows;

rownum w c name
--------------------------------------------------------------
1 999 (null) aaa
2 (null) 222 aaa
3 911 (null) bbb
4 922 (null) bbb
5 (null) 111 bbb


Requirement Details are:
=========================

I want to display people who has both 'w and c'.... not just c or w...
from the example, name 'aaa' and bbb has both entries under 'w' and 'c' columns -> want to display these records

where as name ccc and ddd has either entries.... -> omit these records



Regards,
Williams

Comments

hm 
-- 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)
Saubhik 
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;
NicloeiW 
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>
KanchDev
Hi Williams,

Please try this and let me know.
select * from xxxx where name in (
select name from xxxx
group by name
having sum(w) >1 and sum(c)>1
)
Cheers
Kanchana
189821 
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
This will do only one table scan.

Urs
728534
Hi,
YOu can do it this way too.
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
Regards,
Bhushan
Aketi Jyuuzou 
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
myHomePage
http://www.geocities.jp/oraclesqlpuzzle/
Mac_Freak_Rahul
off topic : Mate could you please tell me how could i view your geocities page in english . Looks like a very good page but too bad I dont understnad that language.

Regards
Rahul
KanchDev
Hi Rahul,

Try this

http://translate.google.lk/translate?hl=en&sl=ja&tl=en&u=http%3A%2F%2Fwww.geocities.jp%2Foraclesqlpuzzle%2F

Cheers
Kanchana
Mac_Freak_Rahul
Hey ,

Thanks Kanchana :)

Regards
Rahul
1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 18 2010
Added on Oct 18 2010
10 comments
335 views