CODEWITHSUNDEEP
sqloracle-database
LegendofLegends
LegendofLegends

Reputation: 149

SQL invalid identifier?

I finished typing out SQL statements but now I'm getting errors codes when I run it on omega. I keep getting the invalid identifier code...

Here's what I get once I ran it in omega.

SQL> 
SQL> --question 14
SQL> 
SQL> SELECT to_char(Avg(ProductPrice),'99,999.99') AS Avg_Price
  2  FROM Product_mys;
SELECT to_char(Avg(ProductPrice),'99,999.99') AS Avg_Price
                   *
ERROR at line 1:
ORA-00904: "PRODUCTPRICE": invalid identifier

These are the sql tables where the data is being pulled from

CREATE TABLE Dept_mys (
 DeptID     Number(3)       NOT NULL,
 DeptName   VARCHAR(20)     NOT NULL,
 PRIMARY KEY    (DeptID) 
 ) ;

CREATE TABLE Commission_mys (
 CommClass  CHAR(1)         NOT NULL,
 CommRate   Number(2,2)     NOT NULL,
 PRIMARY KEY    (CommClass) 
 ) ;

CREATE TABLE Category_mys (
 CatID      Number(3)       NOT NULL,
 catName    VARCHAR(20)     NOT NULL,
 PRIMARY KEY    (CatID) 
 ) ;

CREATE TABLE SalesRep_mys (
 SalesRepID NUMBER(4)       NOT NULL,
 SalesRepFName  VARCHAR(20)     NOT NULL,
 SalesRepLName  VARCHAR(20)     NOT NULL,
 DeptID     NUMBER(3)       NOT NULL,
 CommClass  CHAR(1)         NOT NULL,
 PRIMARY KEY    (SalesRepID),
 FOREIGN KEY    (DeptID)    REFERENCES Dept_mys,
 FOREIGN KEY    (CommClass) REFERENCES Commission_mys 
 ) ;

CREATE TABLE Customer_mys (
 CustID     CHAR(5)         NOT NULL,
 CustFName  VARCHAR(20)     NOT NULL,
 CustLName  VARCHAR(20)     NOT NULL,
 CustPhone  CHAR(10),
 SalesRepID NUMBER(4)       NOT NULL,
 PRIMARY KEY    (CustID),
 FOREIGN KEY    (SalesRepID)    REFERENCES SalesRep_mys
 ) ;

CREATE TABLE Order_mys (
 OrderID    NUMBER(3)       NOT NULL,
 OrderDate  DATE            NOT NULL,
 CustID     CHAR(5)         NOT NULL,
 PRIMARY KEY    (OrderID),
 FOREIGN KEY    (CustID)    REFERENCES Customer_mys
 ) ;

CREATE TABLE Product_mys (
 ProductID  NUMBER(3)       NOT NULL,
 ProductName    VARCHAR(30)     NOT NULL,
 CatID      Number(3)       NOT NULL,
 PRIMARY KEY    (ProductID),
 FOREIGN KEY    (CatID)     REFERENCES Category_mys
 ) ;

CREATE TABLE OrderDetail_mys (
 OrderID    NUMBER(3)       NOT NULL,
 ProductID  NUMBER(3)       NOT NULL,
 ProductQty NUMBER(4)       NOT NULL,
 ProductPrice   NUMBER(6,2)     NOT NULL,
 PRIMARY KEY    (OrderID, ProductID),
 FOREIGN KEY    (OrderID)   REFERENCES Order_mys,
 FOREIGN KEY    (ProductID)     REFERENCES Product_mys
 ) ;

Upvotes: 2

Views: 571

Answers (1)

Hart CO
Hart CO

Reputation: 34774

Your query references Sales_Rep_mys but you create SalesRep_mys. So that's at least some of the problem.

Same with Order_Detail_mys and OrderDetail_mys

Looks like those 2 fixes should do it.

Upvotes: 3

Related Questions