SQL 00918. 00000 - “column ambiguously defined”

Question

So I have the "column ambiguously defined" error in my sql oracle code and I can't figure out why I have the error. I know it means the code can't figure out which of two columns with the same name to use, so I need to use prefixes. But I have prefixes that are all correct. I looked at the other questions on this error message on the site, but can't figure it out.

ORA-00918: kolumn inte entydigt definierad 00918. 00000 - "column ambiguously defined" *Cause:
*Action: Error at Line: 4 Column: 5

SELECT KUND.KNR, KUND.FNAMN, KUND.ENAMN
FROM KUND, ORDERRAD, KUNDORDER, KUNDORDER, ARTIKEL, VARUGRUPP
WHERE KUND.KNR = KUNDORDER.KNR
AND KUNDORDER.ORDNR = ORDERRAD.ORDNR
AND ORDERRAD.ARTNR = ARTIKEL.ARTNR
AND ARTIKEL.VGNR = VARUGRUPP.VGNR
AND VARUGRUPP.VGNAMN = 'skäggvård' OR VARUGRUPP.VGNAMN = 'bondgård';

Well, you are still using KUNDORDER twice without giving them a unique alias, so, obviously it is ambiguous. Also, are you sure that that OR shouldn't be between parentheses?. Lastly, I would start using explicit joins instead of implicit ones — Lamak

wvdz wvdz · Accepted Answer · 2015-03-17T16:06:36

You join the table KUNDORDER twice, so you have to use an alias to help oracle distinguish between them.

But looking at this, most likely you meant to join this table once, and it is a typo.

As a side note: you're using deprecated implicit join notation: it's better to use FROM tablename JOIN table1 ON (...) JOIN table2 ON (...)-notation.

Second side note: it's good practice to always use aliases for your joined tables to improve readability, e.g.: FROM table1 t1 JOIN table2 t2 ON (...)

SQL 00918. 00000 - “column ambiguously defined”

1 Answers