'XYZ' AS in union query gives ORA-00918
in [Oracle Help]
|
Prev: Legacy-software
Next: hot sexy puictures
From: Thomas Poenicke on 21 Jul 2008 07:02 Hi, I've created an union query like this: select 'DB' AS SRC, some fields from a table union select 'UP' AS SRC, some fields from a view order by 1 -- <-- id primary key Both - table and view - have the same columns an without the "AS SRC" fields the query is corecctly processed. Why Oracle throws me an 'ORA-00918: column ambiguously defined...' with the additional Column? The sense of the query is to show changed data before applying the changes. The new and old rows are displayed together but without the SRC column only an insider knows, what the source of the row is. Thanks in advance and sorry for my cruel english ;) Thomas
From: fitzjarrell on 21 Jul 2008 08:13 On Jul 21, 6:02 am, Thomas Poenicke <poeni...(a)freenet.de> wrote: > Hi, > > I've created an union query like this: > > select 'DB' AS SRC, some fields from a table > union > select 'UP' AS SRC, some fields from a view > order by 1 -- <-- id primary key > > Both - table and view - have the same columns an without the "AS SRC" > fields the query is corecctly processed. > > Why Oracle throws me an 'ORA-00918: column ambiguously defined...' > with the additional Column? > > The sense of the query is to show changed data before applying the > changes. > The new and old rows are displayed together but without the SRC column > only an insider knows, what the source of the row is. > > Thanks in advance and sorry for my cruel english ;) > Thomas 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
From: Thomas Poenicke on 21 Jul 2008 08:41 On Mon, 21 Jul 2008 05:13:41 -0700 (PDT), fitzjarrell(a)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
From: fitzjarrell on 21 Jul 2008 09:52 On Jul 21, 7:41 am, Thomas Poenicke <poeni...(a)freenet.de> wrote: > On Mon, 21 Jul 2008 05:13:41 -0700 (PDT), fitzjarr...(a)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
From: Thomas Poenicke on 21 Jul 2008 11:13
On Mon, 21 Jul 2008 06:52:53 -0700 (PDT), fitzjarrell(a)cox.net wrote: [...] I think, the mistake lies in the dirty glasses, covering the top line of the view declaration. Here the solution: create or replace view (columns in table and view) as select 'UP' as SRC, these colums from view union select 'DB' as src, those columns from table I just forgot to add "SRC" in the header line. Sorry for the time you spent for nothing. Regards Thomas |