Re: 'XYZ' AS in union query gives ORA-00918
Date: Mon, 21 Jul 2008 14:41:41 +0200
On Mon, 21 Jul 2008 05:13:41 -0700 (PDT), fitzjarrell_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
> 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,
TABLE B contains the data uploaded with sqlldr
VIEW V shows only the different rows:
A.ID, A.NAME, B.STATUS, B.DEPARTMENT
FROM A, B
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
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
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 Received on Mon Jul 21 2008 - 07:41:41 CDT