Re: 'XYZ' AS in union query gives ORA-00918
Date: Mon, 21 Jul 2008 06:52:53 -0700 (PDT)
Message-ID: <e12b5de7-5ccf-46b7-93dc-a55a63c363db@s50g2000hsb.googlegroups.com>
On Jul 21, 7:41 am, Thomas Poenicke <poeni..._at_freenet.de> wrote:
> On Mon, 21 Jul 2008 05:13:41 -0700 (PDT), fitzjarr..._at_cox.net wrote:
> > We need to see what you've written for these 'other columns from'
> > parts of the UNION; you should have posted the entire query as
> > written.
>
> > Do so, as we can't know what your issues are until you post that
> > entire query text.
>
> > David Fitzjarrell
>
> Ok, here an symbolic view on the objects:
>
> TABLE A holds the data in the db:
> ( ID NUMBER NOT NULL,
> NAME VARCHAR2(50) NOT NULL,
> STATUS VARCHAR2(10),
> DEPARTMENT VARCHAR2(20)
> )
>
> TABLE B contains the data uploaded with sqlldr
> (
> NAME VARCHAR2(50),
> STATUS VARCHAR2(10),
> DEPARTMENT VARCHAR2(20)
> )
>
> VIEW V shows only the different rows:
> SELECT
> A.ID, A.NAME, B.STATUS, B.DEPARTMENT
> FROM A, B
> WHERE A.NAME=B.NAME
> MINUS
> SELECT
> ID,NAME,STATUS,DEPARTMENT
> FROM A
>
> Now i want to display the changed data like this:
>
> changed row: id, name, status, department
> old row : id, name, status, department
>
> with this view:
> SELECT * FROM V
> UNION
> SELECT * FROM A WHERE ID IN(SELECT ID FROM V)
> ORDER BY 1 -- ordered by id
>
> This view is tested and shows the results as expected.
>
> If i try the following:
>
> SELECT 'UP' AS SRC,ID,NAME,STATUS,DEPARTMENT FROM V
> UNION
> SELECT 'DB' AS SRC,ID,NAME,STATUS,DEPARTMENT FROM A WHERE ID IN(SELECT
> ID FROM V)
> ORDER BY 1
>
> the ORA-00918 occurs.
>
> Maybe there is another way to display the changes, so i'am not afraid
> of any hints and suggestions.
>
> Thanks in advance.
> Thomas
I do not get the error you report:
SQL> create table a
2 ( ID NUMBER NOT NULL,
3 NAME VARCHAR2(50) NOT NULL,
4 STATUS VARCHAR2(10),
5 DEPARTMENT VARCHAR2(20)
6 )
7 /
Table created.
SQL>
SQL> create table b
2 (
3 NAME VARCHAR2(50),
4 STATUS VARCHAR2(10),
5 DEPARTMENT VARCHAR2(20)
6 )
7 /
Table created.
SQL>
SQL> insert into a
2 select empno, ename, 'CURRENT', dname from emp e, dept d where
e.empno <=7900 and d.deptno = e.deptno;
12 rows created.
SQL>
SQL> insert into b
2 select ename, 'CHANGED', dname from emp e, dept d where e.empno
<=7900 and d.deptno = e.deptno;
12 rows created.
SQL> SQL> SQL> create view v as
2 SELECT
3 A.ID, A.NAME, B.STATUS, B.DEPARTMENT 4 FROM A, B
5 WHERE A.NAME=B.NAME
6 MINUS
7 SELECT
8 ID,NAME,STATUS,DEPARTMENT
9 FROM A
10 /
View created.
SQL>
SQL> SELECT * FROM V
2 UNION
3 SELECT * FROM A WHERE ID IN(SELECT ID FROM V)
4 ORDER BY 1;
ID NAME
STATUS DEPARTMENT
---------- --------------------------------------------------
---------- --------------------
7369 SMYTHE
CHANGED RESEARCH
7369 SMYTHE
CURRENT RESEARCH
7499 ALLEN
CHANGED SALES
7499 ALLEN
CURRENT SALES
7521 WARD
CHANGED SALES
7521 WARD
CURRENT SALES
7566 JONES
CHANGED RESEARCH
7566 JONES
CURRENT RESEARCH
7654 MARTIN
CHANGED SALES
7654 MARTIN
CURRENT SALES
7698 BLAKE
CHANGED SALES
ID NAME
STATUS DEPARTMENT
---------- --------------------------------------------------
---------- --------------------
7698 BLAKE
CURRENT SALES
7782 CLARK
CHANGED ACCOUNTING
7782 CLARK
CURRENT ACCOUNTING
7788 SCOTT
CHANGED RESEARCH
7788 SCOTT
CURRENT RESEARCH
7839 KING
CHANGED ACCOUNTING
7839 KING
CURRENT ACCOUNTING
7844 TURNER
CHANGED SALES
7844 TURNER
CURRENT SALES
7876 ADAMS
CHANGED RESEARCH
7876 ADAMS
CURRENT RESEARCH
ID NAME
STATUS DEPARTMENT
---------- --------------------------------------------------
---------- --------------------
7900 JAMES
CHANGED SALES
7900 JAMES
CURRENT SALES 24 rows selected.
SQL>
SQL> SELECT 'UP' AS SRC,ID,NAME,STATUS,DEPARTMENT FROM V
2 UNION
3 SELECT 'DB' AS SRC,ID,NAME,STATUS,DEPARTMENT FROM A WHERE ID
IN(SELECT
4 ID FROM V)
5 ORDER BY 1
6 /
SR ID NAME STATUS DEPARTMENT -- ---------- -------------------------------------------------- ---------- -------------------- DB 7369 SMYTHE CURRENT RESEARCH DB 7499 ALLEN CURRENT SALES DB 7521 WARD CURRENT SALES DB 7566 JONES CURRENT RESEARCH DB 7654 MARTIN CURRENT SALES DB 7698 BLAKE CURRENT SALES DB 7782 CLARK CURRENT ACCOUNTING DB 7788 SCOTT CURRENT RESEARCH DB 7839 KING CURRENT ACCOUNTING DB 7844 TURNER CURRENT SALES DB 7876 ADAMS
CURRENT RESEARCH
SR ID NAME STATUS DEPARTMENT -- ---------- -------------------------------------------------- ---------- -------------------- DB 7900 JAMES CURRENT SALES UP 7369 SMYTHE CHANGED RESEARCH UP 7499 ALLEN CHANGED SALES UP 7521 WARD CHANGED SALES UP 7566 JONES CHANGED RESEARCH UP 7654 MARTIN CHANGED SALES UP 7698 BLAKE CHANGED SALES UP 7782 CLARK CHANGED ACCOUNTING UP 7788 SCOTT CHANGED RESEARCH UP 7839 KING CHANGED ACCOUNTING UP 7844 TURNER
CHANGED SALES
SR ID NAME STATUS DEPARTMENT -- ---------- -------------------------------------------------- ---------- -------------------- UP 7876 ADAMS CHANGED RESEARCH UP 7900 JAMES
CHANGED SALES 24 rows selected.
SQL> Apparently this isn't the same query you're running.
David Fitzjarrell Received on Mon Jul 21 2008 - 08:52:53 CDT