Re: 'XYZ' AS in union query gives ORA-00918

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
> 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 Received on Mon Jul 21 2008 - 07:41:41 CDT