ORA-00918: column ambiguously defined with CASE column

I had an Oracle query that was returning the error “ORA-00918: column ambiguously defined” when I tried to run it:

SELECT * FROM ( 
  SELECT ID, CASE
    WHEN something THEN something else
    ELSE yet more
  END CASE
FROM ScottsTable
UNION ALL
  SELECT ID, CASE 
    WHEN something THEN something else
    ELSE yet more
  END CASE
FROM ScottsTableHistory
) ORDER BY 1 ASC, 2;

The problem was originating in the first query’s CASE statement. I needed to put an alias on the first CASE statement to avoid the naming duplication that was causing Oracle to hate me. Here is the final, modified query:

SELECT * FROM ( 
  SELECT ID, CASE
    WHEN something THEN something else
    ELSE yet more
  END "MY COLUMN"
FROM ScottsTable
UNION ALL
    SELECT ID, CASE 
    WHEN something THEN something else
  ELSE yet more
END CASE
FROM ScottsTableHistory
) ORDER BY 1 ASC, 2;
Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: