Hi Friends,

Here the description of the problem I'm facing:

I have two tables Person and Department. A person can have lof of departments. I'm trying to run the following sql:


SELECT aa.PERSON_ID FROM PERSON aa WHERE aa.PERSON_ID IN
(
SELECT a.PERSON_ID FROM PERSON a, DEPARTMENT b
WHERE a.DEPT_ID = b.DEPT_ID
ORDER BY b.DEPT_NAME
)

The above mentioned query is not executing in Oracle. Is there any constraint with ORDER BY inside sub queries?

It would be really helpful if anyone could figure this out.

Thanks,
Yogi..

Hi,
>> Is there any constraint with ORDER BY inside sub queries?
Yes.
For instance, You cannot specify an order_by_clause in the subquery of set operators.
But, I don't remember having read something similar for the membership_condition. (ie IN / NOT IN )

>> The above mentioned query is not executing in Oracle.
Understandable.
We don't get anything by ordering the set used with IN.
i) id in (2,4,5)
ii) id in (4,5,2)
both are functionally equivalent.
May be, for this reason, order_by_clause is not allowed here.

-----------------------------------
Rate the reply if you find it helpful