Getting column ambiguously defined when I shouldn't. Why?

Question

I have two tables that return results when I put them together using UNION ALL, like this:

SELECT * FROM TABLE_A
UNION ALL 
SELECT * FROM TABLE_A

But for some reason when I add this code into a WITH clause, like below, it does not work:

WITH            
SQ_Union AS
(
    SELECT * FROM TABLE_A
    UNION ALL 
    SELECT * FROM TABLE_A
)
SELECT 
    *
FROM 
    SQ_Union

When I run that I get the following:

ORA-00918: column ambiguously defined

Both tables have the same columns, name the same, but with differing table names, and data content. Why does this run without the WITH clause, and not run with it?

ther'es only one table `TABLE_A` in your sample and the query works fine. can you check you query and provide the sql that is not working? — are, Jul 11 '17 at 21:41
The error message should point to specific line in the code, please provide that info as well. — , Jul 11 '17 at 22:27

Jeff Holt · Answer 1 · 2017-07-11T23:40:53.310

I created the tables this way on purpose.

create table a (a1 number, b1 number);
create table b (b1 number, a1 number);

insert into a values (1, 2);
insert into b values (3, 4);
commit;

WITH            
   SQ_Union AS (
      SELECT * FROM a
      UNION ALL 
      SELECT * FROM b
   )
SELECT * FROM SQ_Union;

I cannot make this fail on Oracle 12.1.0.2. I suspect you have a problem with code that you haven't shared.

BTW, the above text would be considered an MCVE. When you make an attempt to create one, you'll no doubt find the cause of the problem. To quote one of my favorite characters, "Take it slow".

Getting column ambiguously defined when I shouldn't. Why?

1 Answers1