Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Help with 10g ORA-00918: column ambiguously defined

756977Mar 1 2010 — edited Mar 1 2010
I'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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 29 2010
Added on Mar 1 2010
5 comments
1,640 views