CODEWITHSUNDEEP
sqloracle-database
Thai Huynh
Thai Huynh

Reputation: 1

ORA-00918: Column ambiguously defined

I wanted to display vendor name and total value of sales amount generated by products that were supplied by vendors 21344 and 24288.

Here is my query:

SELECT v_name,PRODUCT.p_code, SUM(line_units*line_price)
FROM VENDOR, PRODUCT, LINE
WHERE VENDOR.v_code = PRODUCT.v_code
AND PRODUCT.p_code = LINE.p_code
AND VENDOR.v_code = 21344 AND VENDOR.v_code= 24288
GROUP BY v_code;

I don't know if this code is correct, but I am getting the ORA-00918 error. How can i fix it?

Upvotes: 0

Views: 2346

Answers (2)

APC
APC

Reputation: 146219

The meaning of the ORA-00918 error is quite straightforward: there is more than one table with the same column name. In such circumstances we have to use a table alias whenever we refer to the column name to identify which instance we want.

You have been quite rigorous in aliasing the column references in the WHERE clause but you missed the one in the GROUP BY. Here I have aliased it with VENDOR:

SELECT v_name,PRODUCT.p_code, SUM(line_units*line_price)
FROM VENDOR, PRODUCT, LINE
WHERE VENDOR.v_code = PRODUCT.v_code
AND PRODUCT.p_code = LINE.p_code
AND VENDOR.v_code in ( 24288, 21344)
GROUP BY VENDOR.v_code;

Note that I have also fixed the filter on VENDOR.v_code; clearly x = 1 AND x = 2 evaluates to false, so your query would never return any rows.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269673

Here are some useful guidelines when writing queries:

  • Give all tables aliases that are abbreviations of the tables name.
  • Qualify all column names by including the table name.
  • Never use commas in the FROM clause; always use explicit JOIN syntax.
  • Include all non-aggregated columns in the GROUP BY.

If you are learning SQL and not learning these or similar guidelines, then you (unfortunately) do not have good material to be learning from.

If we apply this to your query:

SELECT v.v_name, SUM(l.line_units * l.line_price)
FROM VENDOR v JOIN
     PRODUCT p
     ON v.v_code = p.v_code JOIN
     LINE l
     ON p.p_code = l.p_code
WHERE v.v_code IN (21344, 24288)
GROUP BY v.v_name;

Note that this removes the product code from both the SELECT and the GROUP BY. If you want the detail by product, then include the product code in both places.

Upvotes: 0

Related Questions