Hello,

If I do :

(select 0, c.*
from table1 c
minus
select distinct 0, a.*
from table1 a,table2 b
where ... )
union
select distinct 1, a.*
from table1 a, table2 b
where ...

it works fine.

But if I want to add "order by" and any column from table1 I get
"ORA-00904 invalid column name"

I don't understand :-(

Thanks in advance for any idea...

"Christian" <c> wrote

1) post the statement that causes the error
2) you probably need to order by column position, not column name, since
you're using set operators

++ mcs

Mark C. Stock wrote:

Simply "order by table1.col1" causes this error, or any other column of
table1.
But I can't use column position because it could change.
I 've put a.* or c.* because there are too many columns...

Try using the whole thing as a subquery and wrapping one more query
around it, with the order by clause in the outer query, and just using
the column name in the order by clause without prefacing it with a
table name, as demonstrated below.

scott@ORA92> create table table1 as select * from dept
2 /

Table created.

scott@ORA92> create table table2 as select * from dept where deptno =
10
2 /

Table created.

scott@ORA92> select *
2 from ((select 0, c.*
3 from table1 c
4 minus
5 select 0, a.*
6 from table1 a, table2 b
7 where a.deptno = b.deptno)
8 union
9 select 1, a.*
10 from table1 a, table2 b
11 where a.loc = b.loc)
12 order by dname
13 /

0 DEPTNO DNAME LOC
---------- ---------- -------------- -------------
1 10 ACCOUNTING NEW YORK
0 40 OPERATIONS BOSTON
0 20 RESEARCH DALLAS
0 30 SALES CHICAGO

scott@ORA92>

Barbara Boehmer wrote:
Ah, yes.
I can't test for now, but, according to your sample, it should work.
Many thanks !