时间戳

首页 > 解决方案 > ORA-00904: "FOODSUPPLY"."FOODSUPPLYID": 标识符无效

sql - ORA-00904: "FOODSUPPLY"."FOODSUPPLYID": 标识符无效

问题描述

执行 INNER JOIN 时遇到 ORA-00904 错误。是什么原因以及如何解决？

Error : PL/SQL: ORA-00904: 
"FOODSUPPLY"."FOODSUPPLYID": invalid
     identifier

有多个（F1）Chicken，我只想要 DISTINCT 值

例如：(F1)在(S001)中只能出现一次，但在(S002)中仍然可以出现

SELECT s.shelterID,'('|| fs.foodSupplyID ||') '||fs.foodSupplyName AS foodSupplyDesc
FROM FoodSupply FS,Victim V,Shelter s,Shelter_allocation sa
INNER JOIN Request R ON Request.foodsupplyID = FoodSupply.foodSupplyID
WHERE   r.victimID = v.victimID
    AND fs.foodsupplyID = r.foodSupplyID
    AND r.victimID=sa.victimID
    AND SA.shelterID=s.shelterID
ORDER BY s.shelterID,requestDate;

电流输出： 

shelterID | foodDesc    
S001      | (F1)Chicken  
S001      | (F3)Beef     
S001      | (F1)Chicken  
S002      | (F1)Chicken  
S002      | (F5)Meat    
S002      | (F1)Chicken

期望的输出： 

shelterID | foodDesc       
S001      | (F1)Chicken    
          | (F3)Beef       
S002      | (F1)Chicken  
          | (F5)Meat

标签： sqloracle

解决方案

您可以在下面尝试 - 您错过了别名

但是，最好使用显式连接

 SELECT distinct s.shelterID,'('|| fs.foodSupplyID ||') '||fs.foodSupplyName AS foodSupplyDesc
    FROM FoodSupply FS INNER JOIN Request R ON R.foodsupplyID = FS.foodSupplyID
    Inner join Victim V on R.victimID = V.victimID
    Inner join Shelter_allocation sa on R.victimID=sa.victimID
    inner join Shelter s on sa.shelterID=s.shelterID
    ORDER BY s.shelterID,requestDate

推荐阅读