CODEWITHSUNDEEP
sqloracle-database
jgomez61994
jgomez61994

Reputation: 19

SQL Joining Error "Ambiguity"

I'm trying to add a column to a table with "JOIN" Command. But I keep getting an ambiguity error:

ORA-00918: column ambiguously defined

SELECT ORDER_NUM, CUSTOMER_NUM, ORDER_DATE+20 AS PROMOTION_DATE, CUSTOMER.CUSTOMER_NAME
FROM ORDERS, CUSTOMER
WHERE ORDERS.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NAME;

Upvotes: 0

Views: 167

Answers (3)

Ubaid Ashraf
Ubaid Ashraf

Reputation: 875

Seems problem of column name ambiguity. If two table have same column name, just write columns as [tablename].[columnname]. In your case ORDERS.CUSTOMER_NUM if Customer_NUM is column of Orders table.

So it should be like this: 

SELECT ORDERS.ORDER_NUM, ORDERS.CUSTOMER_NUM,ORDERS.ORDER_DATE+20 AS PROMOTION_DATE,CUSTOMER.CUSTOMER_NAME
FROM ORDERS
Inner JOIN CUSTOMER
ON ORDERS.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NAME

Upvotes: 0

Jens
Jens

Reputation: 69440

In the select clause you miss the table alias for column CUSTOMER_NUM.

SELECT ORDER_NUM, ORDERS.CUSTOMER_NUM, ORDER_DATE+20 AS PROMOTION_DATE, CUSTOMER.CUSTOMER_NAME
FROM ORDERS, CUSTOMER
WHERE ORDERS.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NAME;

Upvotes: 0

juergen d
juergen d

Reputation: 204756

If 2 tables have a column with the same name, then you need to tell the DB which one to take. You do this by adding the table name before the column name.

SELECT ORDERS.ORDER_NUM,  
       CUSTOMER.CUSTOMER_NUM,
       ORDERS.ORDER_DATE+20 AS PROMOTION_DATE, 
       CUSTOMER.CUSTOMER_NAME
FROM ORDERS
JOIN CUSTOMER ON ORDERS.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NAME;

Upvotes: 2

Related Questions