Home » SQL & PL/SQL » SQL & PL/SQL » issue with join condition while using in the corelated sub query (9i or 10g)
issue with join condition while using in the corelated sub query [message #395750] Thu, 02 April 2009 09:19 Go to next message
mrboopathiraja
Messages: 1
Registered: April 2009
Location: Bangalore
Junior Member
Bees,

I am not able to use the below query since i have used a join condition in the corelated sub qery inline view.But i have a requirement like this . Please help me on this

select * from dept d
where exists (select 1 from (
select deptno
from emp where deptno= d.deptno)
)

Exactly the need is like below

SELECT *
FROM dept d
WHERE EXISTS (SELECT case when a.c_dept>1 then 1+s_sal else 2+s_sal end
FROM
(SELECT count(deptno),deptno c_dept FROM emp WHERE deptno = d.deptno group by deptno) a
,(select sum(sal) s_sal,deptno from emp_sal where deptno=d.deptno group by deptno) b
where a.deptno=d.deptno
)




Thank you
Re: issue with join condition while using in the corelated sub query [message #395753 is a reply to message #395750] Thu, 02 April 2009 09:21 Go to previous message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Exactly the need is like below
So proceed to do so.

Congratulations!

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.

[Updated on: Thu, 02 April 2009 10:03]

Report message to a moderator

Previous Topic: Last Value (merged)
Next Topic: how to find space occupied by logical rowid
Goto Forum:
  


Current Time: Sat Dec 03 01:32:56 CST 2016

Total time taken to generate the page: 0.07991 seconds