Re: Invalid column name

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 20 May 2005 23:17:19 -0700
Message-ID: <1116656239.593286.279910@o13g2000cwo.googlegroups.com>

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_at_ORA92> create table table1 as select * from dept   2 /

Table created.

scott_at_ORA92> create table table2 as select * from dept where deptno = 10
  2 /

Table created.

scott_at_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_at_ORA92> Received on Sat May 21 2005 - 01:17:19 CDT