oracle - 如何解决 ORA-00904：“CI”。“CATALOG_ITEM_ID”：无效标识符 行错误：2 列：52

Question

我需要创建一个查询，该查询将显示目录中每本书的标题、出版商、ISBN、发行日期、页数以及它是否在任何图书馆中（“是”或“否”），并按标题对结果进行排序。我也不能有任何重复。

我的查询是

 select distinct ci.title, ci.publisher, ci.RELEASE_DATE, b.ISBN, b.pages  from  
 catalog_item ci, book b
 left join physical_item pi on pi.CATALOG_ITEM_ID = ci.CATALOG_ITEM_ID
 left join branch b on b.branch_id = pi.branch_id
 left join library l on b.library_id=l.library_id
 order by ci.TITLE
 ;

我收到以下错误

ORA-00904: "CI"."CATALOG_ITEM_ID": 无效的标识符 00904. 00000 - "%s: 无效的标识符" *原因:
*操作: 行错误: 2 列: 52

我看不出它是如何无效的

我的 DDL 如下：

 CREATE TABLE CUSTOMER (Customer_ID NUMBER PRIMARY KEY, Customer_Firstname 
 VARCHAR2(30), Customer_Lastname VARCHAR2(30), Customer_Street VARCHAR2(30), 
 Customer_City VARCHAR2(30), Customer_State VARCHAR2(20), Customer_Zip 
 VARCHAR2(10));

CREATE TABLE BRANCH (Branch_ID NUMBER PRIMARY KEY, Branch_Name VARCHAR2(30), 
Branch_Phone VARCHAR2 (30), Branch_Address VARCHAR2(30), LIBRARY_ID NUMBER);

CREATE TABLE PHYSICAL_ITEM (Physical_Item_ID NUMBER PRIMARY KEY, Branch_ID NUMBER, 
Catalog_Item_ID NUMBER, Copy_Number NUMBER, Date_Purchased DATE); 

CREATE TABLE CATALOG_ITEM (Catalog_Item_ID NUMBER PRIMARY KEY, Title VARCHAR2 (30), 
Description VARCHAR2(30), Publisher VARCHAR2 (30), Release_Date DATE, Type 
VARCHAR2(30));

CREATE TABLE DVD (Catalog_Item_ID NUMBER, Length VARCHAR2(30));

CREATE TABLE BOOK (Catalog_Item_ID NUMBER, ISBN VARCHAR2(13), Pages NUMBER);

CREATE TABLE TRANSACTION (Transaction_ID NUMBER PRIMARY KEY, Date_checkout DATE, 
Date_Due DATE, Date_Returned DATE, Library_Card_ID NUMBER, Physical_Item_ID NUMBER);

CREATE TABLE LIBRARY (Library_ID NUMBER PRIMARY KEY, Library_Name VARCHAR2 (30), 
Library_Phone VARCHAR2(30), Library_Address VARCHAR2(30));

CREATE TABLE LIBRARY_CARD (Library_Card_ID NUMBER PRIMARY KEY, Library_ID NUMBER, 
Customer_ID NUMBER, Card_Number VARCHAR2(30), PIN VARCHAR2(8), Date_Expire DATE);

 ALTER TABLE BRANCH ADD CONSTRAINT Library_ID_FK FOREIGN KEY (Library_ID) REFERENCES 
 LIBRARY (Library_ID);

 ALTER TABLE PHYSICAL_ITEM ADD CONSTRAINT Branch_ID_FK FOREIGN KEY (Branch_ID) 
 REFERENCES BRANCH (Branch_ID);

 ALTER TABLE PHYSICAL_ITEM ADD CONSTRAINT Catalog_Item_ID_FK FOREIGN KEY 
 (Catalog_Item_ID) REFERENCES CATALOG_ITEM (Catalog_Item_ID);

 ALTER TABLE TRANSACTION ADD CONSTRAINT Library_Card_ID_FK FOREIGN KEY 
(Library_Card_ID) REFERENCES LIBRARY_CARD (Library_Card_ID);  

ALTER TABLE TRANSACTION ADD CONSTRAINT Physical_Item_ID_FK FOREIGN KEY 
(Physical_Item_ID) REFERENCES PHYSICAL_ITEM (Physical_Item_ID); 

ALTER TABLE LIBRARY_CARD ADD CONSTRAINT Library_ID_FK1 FOREIGN KEY (Library_ID) 
REFERENCES LIBRARY (Library_ID);

ALTER TABLE LIBRARY_CARD ADD CONSTRAINT Customer_ID_FK FOREIGN KEY (Customer_ID) 
REFERENCES CUSTOMER (Customer_ID);

ALTER TABLE DVD ADD CONSTRAINT Catalog_Item_ID_FK1 FOREIGN KEY (Catalog_Item_ID) 
REFERENCES CATALOG_ITEM (Catalog_Item_ID); 

ALTER TABLE BOOK add CONSTRAINT Catalog_Item_ID_FK2 FOREIGN KEY (Catalog_Item_ID) 
REFERENCES CATALOG_ITEM (Catalog_Item_ID);

标签： oracle