一则 ORA-00923: FROM keyword not found where expected解决原因：Select , cast( 改成Select a., cast

港控/mmm° 2023-10-08 09:54 40阅读 0赞

原报错SQL语句：

Select *, 
    cast(Case When iCount=0 then 0 else (iYSYWFED*1.0)/iCount end as NUMBER(18,2)) as PerYSYWFED,
    cast(Case When iCountEx=0 then 0 else (iYSYWFEDEx*1.0)/iCountEx end as NUMBER(18,2)) as PerYSYWFEDEx  From (
    Select e.SeqNo2, c.Area,d.TypeName,  
    cast(Sum(case when SUBSTR(BGRQ,1, 4)=to_char(sysdate,'yyyy') then 1 else 0 end) as Int) as iCount, 
    Sum(case when SUBSTR(BGRQ,1, 4)=to_char(sysdate,'yyyy') then YSYWF else 0 end) as iYSYWFED,  
    Sum(case when SUBSTR(BGRQ,1, 4)=cast(to_number( TO_CHAR(sysdate,'yyyy'))-1 as VARCHAR2(4)) then 1 else 0 end) as iCountEx, 
    Sum(case when SUBSTR(BGRQ,1, 4)=cast(to_number( TO_CHAR(sysdate,'yyyy'))-1 as VARCHAR2(4)) then YSYWF else 0 end) as iYSYWFEDEx From BB_CONTENT1 a 
    inner join (BB_CompanyList b inner join K_Company c on b.LoginID=c.LoginID) on a.CompanyGUID=b.GUID  
    inner join BB_Type d on a.TypeID=d.GUID  
    left join K_Area e on c.Area=e.Area2  Where bbstate='报备完成'  Group By e.SeqNo2, c.Area,d.TypeName) a order by SeqNo2,TypeName

改成下面 这样OK

Select a.*, 
    cast(Case When iCount=0 then 0 else (iYSYWFED*1.0)/iCount end as NUMBER(18,2)) as PerYSYWFED,
    cast(Case When iCountEx=0 then 0 else (iYSYWFEDEx*1.0)/iCountEx end as NUMBER(18,2)) as PerYSYWFEDEx  From (
    Select e.SeqNo2, c.Area,d.TypeName,  
    cast(Sum(case when SUBSTR(BGRQ,1, 4)=to_char(sysdate,'yyyy') then 1 else 0 end) as Int) as iCount, 
    Sum(case when SUBSTR(BGRQ,1, 4)=to_char(sysdate,'yyyy') then YSYWF else 0 end) as iYSYWFED,  
    Sum(case when SUBSTR(BGRQ,1, 4)=cast(to_number( TO_CHAR(sysdate,'yyyy'))-1 as VARCHAR2(4)) then 1 else 0 end) as iCountEx, 
    Sum(case when SUBSTR(BGRQ,1, 4)=cast(to_number( TO_CHAR(sysdate,'yyyy'))-1 as VARCHAR2(4)) then YSYWF else 0 end) as iYSYWFEDEx From BB_CONTENT1 a 
    inner join (BB_CompanyList b inner join K_Company c on b.LoginID=c.LoginID) on a.CompanyGUID=b.GUID  
    inner join BB_Type d on a.TypeID=d.GUID  
    left join K_Area e on c.Area=e.Area2  Where bbstate='报备完成'  Group By e.SeqNo2, c.Area,d.TypeName) a order by SeqNo2,TypeName

**Select \*, 改成Select a.\*, ok ,别名问题导致错误**