Keep getting ORA-00918: column ambiguously defined. Very confused

Question

I keep getting this ORA-00918: column ambiguously defined error and i don't know what i'm doing wrong. Any help would be appreciated Here is my code:

select CUSTOMER.CUS_CODE as CUS_CODE,
CUSTOMER.CUS_AREACODE as CUS_AREACODE,
INVOICE.INV_NUMBER as INV_NUMBER,
SUM(LINE.LINE_UNITS*LINE.LINE_PRICE) as Amount
from LINE LINE,
INVOICE INVOICE,
CUSTOMER CUSTOMER 
where CUSTOMER.CUS_CODE=INVOICE.CUS_CODE
and INVOICE.INV_NUMBER=LINE.INV_NUMBER
group by CUS_AREACODE, CUS_CODE

Learn to use proper, explicit JOIN syntax. Also, I'm not sure what the question is. There are exactly two unqualified column references in the query, so the answer looks obvious to me. — Gordon Linoff
You have (at least) two tables that have a column named CUS_CODE. When you say order by CUS_CODE, which table's CUS_CODE do you mean? — mustaccio
@mustaccio i'm trying to use the CUSTOMER table. if i do group by CUS_AREACODE, CUSTOMER.CUS_CODE it still gives me that error — Rene D. Balli
You better to state where the column comes from, like mustaccio said. And not just CUS_CODE, but all columns. — Prisoner
What's the logic of aliasing a table by the table's original name? — mathguy

Bob Jarvis - Reinstate Monica Bob Jarvis - Reinstate Monica · Accepted Answer · 2016-10-28T02:27:51

I strongly suspect it's the GROUP BY. First you'll need to add all the fields which aren't used in the summation. Second, you should specify the table or alias on all fields in the GROUP BY. Change your statement to

SELECT c.CUS_CODE as CUS_CODE,
       c.CUS_AREACODE as CUS_AREACODE,
       i.INV_NUMBER as INV_NUMBER,
       SUM(l.LINE_UNITS * l.LINE_PRICE) as Amount
  FROM CUSTOMER c
  INNER JOIN INVOICE i
    ON INVOICE.CUS_CODE = CUSTOMER.CUS_CODE
  INNER JOIN LINE l
    ON l.INV_NUMBER = i.INV_NUMBER
 GROUP BY c.CUS_CODE,
          c.CUS_AREACODE,
          i.INV_NUMBER

and see if that doesn't get it. Note that I changed the aliases so that they're short but easily understood, and changed the joins to ANSI-style joins instead of the old implicit joins.

Best of luck.

Keep getting ORA-00918: column ambiguously defined. Very confused

1 Answers