Help with 10g ORA-00918: column ambiguously defined
756977Mar 1 2010 — edited Mar 1 2010I've been researching this error for the last few hours now, can anyone find which column is ambiguously defined for me?
CREATE TABLE ORDERS(
ORDER_NO NUMBER(2),
ITEM_NO VARCHAR2(2),
QTY NUMBER(2)
);
CREATE TABLE INVENTORY(
ITEM_NO VARCHAR2(2),
DESCRIPTION VARCHAR2(10),
PRICE DECIMAL(5,2),
FINISH VARCHAR2(10) );
INVENTORY
ITEM_NO DESCRIPTION PRICE FINISH
B3 BOOKCASE 130 CHERRY
B1 BOOKCASE 120 PINE
B2 BOOKCASE 125 WALNUT
T1 TABLE 200 PINE
T2 TABLE 225 WALNUT
T3 TABLE 250 CHERRY
C1 CHAIR 50 PINE
C2 CHAIR 60 WALNUT
C3 CHAIR 70 CHERRY
S1 SOFA 500 CLOTH
S2 SOFA 550 MICROFIBER
S3 SOFA 750 LEATHER
ORDERS
ORDER_NO ITEM_NO QTY
1 B2 1
1 T2 1
1 C2 6
2 B3 3
3 B1 2
3 T1 1
3 C1 4
4 B2 3
5 B2 2
5 T2 1
5 C2 4
6 B3 4
6 T3 1
6 C3 6
6 S3 1
Here's the SQL I'm trying to run:
SELECT INVENTORY.ITEM_NO,INVENTORY.DESCRIPTION,COUNT(*) AS "ORDER_COUNT",SUM(INVENTORY.PRICE) AS "TOTAL_VALUE"
FROM INVENTORY, ORDERS
WHERE INVENTORY.ITEM_NO = ORDERS.ITEM_NO
GROUP BY ITEM_NO;
I've tried each variable with and without the table.column prefix and still get the same error...
All help appreciated.
Thanks,
Greg