SQL statement with semi join in combination with minus operator cannot be parsed

From: jb <juerg.bircher_at_gmail.com>
Date: 8 Jun 2006 12:09:25 -0700
Message-ID: <1149793765.378199.246530@y43g2000cwc.googlegroups.com>

Hello

I have the following four tables "a", "b", "c" and "d". Table "a" and "b" are joined in combination with a semi join to "c" minus a semi join to "d". The example provided is only theoretical in order to reproduce my issue. I have tested it on Oracle 9.2.0.1.0 and Oracle 10.1.0.2.0 for Windows.

To try it out yourself just create the four tables and execute statement 1, 2 and 3. No data is needed to get error!

create table a (n number);
create table b (n number);
create table c (n number);
create table d (n number);

STATEMENT 1

---

select a.n, b.n from a
join b on
b.n = a.n and
exists (
select c.n from c where c.n = b.n
minus
select d.n from d where d.n = b.n
)

where a.n > 1;

If I execute the statement 1 I get the following error:

FEHLER in Zeile 5:
ORA-00904: Ungültiger Spaltenname

However the following two without the "minus" operator work properly:

STATEMENT 2

---

select a.n, b.n from a
join b on
b.n = a.n and
exists (
select c.n from c where c.n = b.n
)

where a.n > 1;

STATEMENT 3

---

select a.n, b.n from a
join b on
b.n = a.n and
exists (
select d.n from d where d.n = b.n
)

where a.n > 1;

What is wrong with statement 1 that I get ORA-00904 error?

Thanks in advance for your help.

jb Received on Thu Jun 08 2006 - 14:09:25 CDT