where not exists

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

radhika - 07 Apr 2005 22:20 GMT

Why the first statement is not working.
I want to use EXCEPT

connect to UDBPERF;

select dept_id from new.dept m except (select 1 from new.dept p where
p.parent_d = m.member_id and m.hirachy level_id = '50');

select dept_id from new.dept m where not exists (select 1 from new.dept
p where p.parent_d = m.member_id and m.hirachy level_id = '50');

cheers.....

Reply to this Message

James Campbell - 08 Apr 2005 02:29 GMT

After the EXCEPT is a different (not nested) subselect from the one before
EXCEPT - hence table references aren't carried over.

James Campbell

Reply to this Message

lelle - 08 Apr 2005 04:10 GMT

> Why the first statement is not working.
> I want to use EXCEPT
[quoted text clipped - 3 lines]
> select dept_id from new.dept m except (select 1 from new.dept p where
> p.parent_d = m.member_id and m.hirachy level_id = '50');

EXCEPT is a setoperator (similar to union and intersect), and exists is
a predicate. Therefore you cannot reference m.member_id

/Lennart

Reply to this Message

--CELKO-- - 11 Apr 2005 22:41 GMT

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

The immediate problem is that EXCEPT [ALL] is defined like UNION [ALL]
and INTERSECT [ALL] to work with completed table expressions on the
same level, so you cannot have containing reference, as you do with
subqueries. However, there are better ways to model hierarchies, so
you might want to get a copy of my book TREES & HIERARCHIES IN SQL.

Reply to this Message

--CELKO-- - 11 Apr 2005 22:41 GMT

Reply to this Message