Oracle Database Discussions

One possible solution (there are many others)

with wood as (
select 'F1' fund, 'WM' pricetype, 'A' source from dual UNION ALL
select 'F1' fund, 'WM' pricetype, 'B' source from dual UNION ALL
select 'F1' fund, 'VMS' pricetype, 'A' source from dual UNION ALL
select 'F2' fund, 'WM' pricetype, 'A' source from dual UNION ALL
select 'F2' fund, 'WM' pricetype, 'B' source from dual UNION ALL
select 'F3' fund, 'WM' pricetype, 'A' source from dual UNION ALL
select 'F4' fund, 'VMS' pricetype, 'A' source from dual)
select fund 
from
	(select fund, decode(pricetype,'WM',1,0) type_wm, decode(pricetype,'WM',0,2) type_other
	from wood 
	group by fund, pricetype
	)
group by fund
having sum(type_wm+type_other) = 1;

I didn't use exists since that would require to access the same table twice.

hi sven,\
Thanks for your reply..
But i need to use Exist function for this query
Thanks
Hari

Then what does your teacher say? Presumably he / she gave you notes on how to use EXISTS?

Why ? Did you tried something else to start ?

Nicolas.

i am asking you to help me out.....Please help me

Please, explain why the Sven's query is not good enough for you.
And make effort on your side by trying to write the query first.

Nicolas.

with wood as (
select 'F1' fund, 'WM' pricetype, 'A' source from dual UNION ALL
select 'F1' fund, 'WM' pricetype, 'B' source from dual UNION ALL
select 'F1' fund, 'VMS' pricetype, 'A' source from dual UNION ALL
select 'F2' fund, 'WM' pricetype, 'A' source from dual UNION ALL
select 'F2' fund, 'WM' pricetype, 'B' source from dual UNION ALL
select 'F3' fund, 'WM' pricetype, 'A' source from dual UNION ALL
select 'F4' fund, 'VMS' pricetype, 'A' source from dual)
select *
from wood
where pricetype = 'WM'
and not exists (select 1 from wood wood_inner where wood_inner.fund = wood.fund and pricetype != 'WM');

Well done, you've now completed his homework assignment for him. And when he goes looking for a job, and gets one where he'll have to actually perform such tasks, he will still be clueless, but hey, you can still do his work for him.

Does NOT EXISTS same as EXISTS ?
;-)

Nicolas.

I guess NOT.

Thank you so much..
and thanks JS1 for your joke :(

hi gasparotto,
I am not saying Svens query is not good.. I am trying that in multiple ways.
I was trying that query from my side also..
Hari

What I was trying to explain : it is good when poster show on the forum their effort to solve their problem on their own side before asking for help here.
Especially when there is some inexplicable restriction on the solution to give.

Nicolas.

Thanks gasparotto for your valuable advice..
Will take it sincerely.
What hurted me was , some of them made fun of me.
Thanks
Hari

with wood as (
select 'F1' fund, 'WM' pricetype, 'A' source from dual UNION ALL
select 'F1' fund, 'WM' pricetype, 'B' source from dual UNION ALL
select 'F1' fund, 'VMS' pricetype, 'A' source from dual UNION ALL
select 'F2' fund, 'WM' pricetype, 'A' source from dual UNION ALL
select 'F2' fund, 'WM' pricetype, 'B' source from dual UNION ALL
select 'F3' fund, 'WM' pricetype, 'A' source from dual UNION ALL
select 'F4' fund, 'VMS' pricetype, 'A' source from dual)
select fund
from wood
group by fund
having min(case when pricetype = 'WM' then 1 else 0 end)=1;

FU
--
F2
F3

If we want other ResultSet,we can use below solution.

with wood as (
select 'F1' fund, 'WM' pricetype, 'A' source from dual UNION ALL
select 'F1' fund, 'WM' pricetype, 'B' source from dual UNION ALL
select 'F1' fund, 'VMS' pricetype, 'A' source from dual UNION ALL
select 'F2' fund, 'WM' pricetype, 'A' source from dual UNION ALL
select 'F2' fund, 'WM' pricetype, 'B' source from dual UNION ALL
select 'F3' fund, 'WM' pricetype, 'A' source from dual UNION ALL
select 'F4' fund, 'VMS' pricetype, 'A' source from dual)
select fund,pricetype,source
from (select fund,pricetype,source,
      min(case when pricetype = 'WM' then 1 else 0 end) over(partition by fund) as willOut
      from wood)
where willOut = 1;

FU  PRI  S
--  ---  -
F2  WM   A
F2  WM   B
F3  WM   A

similar threads(OTN)
585154
551338
621506
634915

similar threads(OTN-Japan)
http://otn.oracle.co.jp/forum/thread.jspa?threadID=35002855
http://otn.oracle.co.jp/forum/thread.jspa?threadID=35003244

I don't see anyone making fun of you.

I see people trying to get you to a) explain your reasons for the restriction to use EXISTS, and b) display your attempts at solving the issue at hand.

Your question looks suspiciously like a coursework question, plus you showed no workings of your own, hence the reluctance to hand you the answer on a plate when it looked like you hadn't done anything yourself.

The more you show that you are trying stuff out for yourself, but are stuck on a particular bit, and the more you explain the reasons behind your requirements, then the more people will supply helpful messages. The more you make it look like "do my work for me" then the less likely you'll get helpful suggestions!