multi-inner join statement

Asked by At

I'm currently trying to draw multiple tables through several inner-join statements. However, I am continually hit with the following error:

ORA-00904: "PART"."ITEM_CLASS": invalid identifier

I've visited several other pages here but that problem did not coincide with mine.

Here is the SQL query:

SELECT Slsrep_Number, AVG(Commission_Rate) AS AVG_Rate, MAX(Total_Commission) as MAX_Comission 
  FROM Sales_Rep 
 WHERE Sales_Rep.Slsrep_Number = Customer.Slrsrep_Number 
   AND Customer.C_Number = Orders.C_Number 
   AND Orders.Order_Number = Order_Line.Order_Number 
   AND Order_Line.Part_Number = Part.Part_Number 
   AND Part.Item_Class = 'SG';

Here are the tables being used (screenshots):

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

The pictures are listed in the order that I am referencing them in the SQL query.

2 Answers

2
Gordon Linoff On Best Solutions

You need to reference the tables in the FROM clause. You can't just reference them.

You should also use table aliases and proper, explicit, standard JOIN syntax.

So:

SELECT sr.Slsrep_Number,
       AVG(?.Commission_Rate) AS AVG_Rate, 
       MAX(?Total_Commission) as MAX_Comission 
FROM Sales_Rep sr JOIN
     Customer c
     ON sr.Slsrep_Number = c.Slrsrep_Number JOIN
     Orders o
     ON c.C_Number = o.C_Number JOIN
     Order_Line ol
     ON o.Order_Number = ol.Order_Number 
     Part p
     ON ol.Part_Number = p.Part_Number 
WHERE p.Item_Class = 'SG';

The ? is for the alias for the column with the commission.

The query now "looks" right. However, I don't think it is particularly useful. If that is the case, ask another question and provide sample data, desired results, and an explanation of what you want to accomplish.

0
Ahamed On

Customer,Orders,Order_Line and Part Tables are missing in the from clause.Try This.

SELECT Slsrep_Number,
       AVG(Commission_Rate) AS AVG_Rate,
       MAX(Total_Commission) as MAX_Comission
  FROM Sales_Rep,Customer,Orders,Order_Line,Part
 WHERE Sales_Rep.Slsrep_Number = Customer.Slrsrep_Number
   AND Customer.C_Number = Orders.C_Number
   AND Orders.Order_Number = Order_Line.Order_Number
   AND Order_Line.Part_Number = Part.Part_Number
   AND Part.Item_Class = 'SG';