3

I have two tables with similar data, and I need to query the union of the tables. I have changed the actual table names I'm using to only illustrate the issue:

SELECT
    UORD.ORDER_NUM
FROM
    (SELECT ORDER_ID, ORDER_NUM, PKG_ID
     FROM ORDER
    UNION ALL
    SELECT ORDER_ID, ORDER_NUM, PKG_ID
     FROM AMENDING_ORDER) UORD
    INNER JOIN PLAN ON PLAN.PKG_ID = UORD.PKG_ID;

I get

ORA-00918: column ambiguously defined

error due to the INNER JOIN on PKG_ID.

My actual query has several more joins on Order items, initially I started by using just the first table, e.g. ORDER, which worked fine.

Is there any way this union of tables can be defined in query so it is not ambiguous?

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
yas
  • 3,520
  • 4
  • 25
  • 38
  • Do you get the error with the query you've shown too? Wouldn't have thought that would be ambiguous.. – HoneyBadger Dec 21 '15 at 14:43
  • you should post the actual query you are trying as the one posted doesn't seem to have an issue – Vamsi Prabhala Dec 21 '15 at 14:44
  • What happens if you take away the last join? – Dan Bracuk Dec 21 '15 at 14:46
  • See my answer below, I think I have found the problem. – yas Dec 21 '15 at 14:49
  • 1
    The query as shown can't work, because `order` is a reserved word and `from order` would result in a different error (I _think_ the same is true, for `PLAN` but I am not entirely sure). Please show us your **real** query. –  Dec 21 '15 at 14:51

3 Answers3

0

You may try prefixing column names with table names (aliases) to be more explicit :

SELECT
    UORD.ORDER_NUM
FROM
    (SELECT O.ORDER_ID, O.ORDER_NUM, O.PKG_ID
     FROM ORDER O
    UNION ALL
    SELECT AO.ORDER_ID, AO.ORDER_NUM, AO.PKG_ID
     FROM AMENDING_ORDER AO) UORD
    INNER JOIN PLAN ON PLAN.PKG_ID = UORD.PKG_ID;
Mladen Oršolić
  • 1,352
  • 3
  • 23
  • 43
0

I have experimented further with the data, and it is some specific columns causing the issue.

The data types are are all the same across the tables, but the DBA has allowed the same columns to be NULLABLE in one table and not the other.

I am going to request him to change that, as I'm almost certain it is wrong, and I think it will fix the issue.

yas
  • 3,520
  • 4
  • 25
  • 38
0

It's not relevant here, but to whomever is having the same error and has verified already the scenario above and it's ok, maybe it comes then from selecting twice a column from the same table.

Ivona Octavia Laiu
  • 1