Oracle- 为什么在组合列中显示错误 " ORA-00904: "OUTLET_STATUS": invalid identifier "?
Oracle- 为什么在组合列中显示错误 " ORA-00904: "OUTLET_STATUS": invalid identifier "?
我想在Oracle中将两列合并成一列。我遵循了这个链接:链接。
查询如下:
SELECT T6.ITEM_GROUP_NAME, T7.ZONE_NAME, T5.SR_NAME, T5.SR_ID, T5.MOBILE_NO, T3.ROUTE_NAME, T1.OUTLET_NAME, T1.OUTLET_ADDRESS, T1.PROPRITOR_NAME, T1.MOBILE_NUMBER, T2.STATUS || T8.REASON as OUTLET_STATUS, SUM(((T2.ITEM_CTN*T4.FACTOR)+ T2.ITEM_QTY )*T2.OUT_PRICE )AS AMOUNT FROM T_OUTLET T1, T_ORDER_DETAIL T2, T_ROUTE T3, T_ITEM T4, T_SR_INFO T5, T_ITEM_GROUP T6, T_ZONE T7, T_NON_PRODUCTIVE_SALES T8 WHERE T6.ITEM_GROUP_ID='000200000' and T7.ZONE_ID='Z002' and T5.SR_ID= '174369' and T2.OUTLET_ID=T1.OUTLET_ID AND T3.ROUTE_ID = T2.ROUTE_ID AND T2.STATUS='Y' and T2.ENTRY_DATE= TO_DATE('11/02/2017','dd/mm/yyyy') and T2.ITEM_ID=T4.ITEM_ID and T8.ENTRY_DATE= TO_DATE('11/02/2017','dd/mm/yyyy') and T8.SR_ID = '174369' and T2.OUTLET_ID= T8.OUTLET_ID GROUP BY T6.ITEM_GROUP_NAME, T7.ZONE_NAME, T5.SR_NAME, T5.SR_ID, T5.MOBILE_NO, T2.ENTRY_DATE, T2.OUTLET_ID, T3.ROUTE_NAME, T1.OUTLET_NAME, T1.OUTLET_ADDRESS, T1.PROPRITOR_NAME, T1.MOBILE_NUMBER, OUTLET_STATUS ORDER BY T1.OUTLET_NAME
但是当我执行时,出现错误:
ORA-00904:“OUTLET_STATUS”:无效标识符
问题出在哪里?
Oracle-为什么会显示"ORA-00904: "OUTLET_STATUS": invalid identifier"错误的原因以及解决方法
在查询中,group by
在select
之前进行评估,所以group by
不知道名为OUTLET_STATUS
的列。将group by
中的OUTLET_STATUS
改为T2.STATUS || T8.REASON
:
select . . . group by T6.ITEM_GROUP_NAME, T7.ZONE_NAME, T5.SR_NAME, T5.SR_ID, T5.MOBILE_NO, T2.ENTRY_DATE, T2.OUTLET_ID, T3.ROUTE_NAME, T1.OUTLET_NAME, T1.OUTLET_ADDRESS, T1.PROPRITOR_NAME, T1.MOBILE_NUMBER, T2.status || T8.REASON --在这里修改 order by T1.OUTLET_NAME
这样,你的查询语句变为:
select T6.ITEM_GROUP_NAME, T7.ZONE_NAME, T5.SR_NAME, T5.SR_ID, T5.MOBILE_NO, T3.ROUTE_NAME, T1.OUTLET_NAME, T1.OUTLET_ADDRESS, T1.PROPRITOR_NAME, T1.MOBILE_NUMBER, T2.status || T8.REASON as OUTLET_STATUS, SUM(((T2.ITEM_CTN * T4.FACTOR) + T2.ITEM_QTY) * T2.OUT_PRICE) as AMOUNT from T_OUTLET T1, T_ORDER_DETAIL T2, T_ROUTE T3, T_ITEM T4, T_SR_INFO T5, T_ITEM_GROUP T6, T_ZONE T7, T_NON_PRODUCTIVE_SALES T8 where T6.ITEM_GROUP_ID = '000200000' and T7.ZONE_ID = 'Z002' and T5.SR_ID = '174369' and T2.OUTLET_ID = T1.OUTLET_ID and T3.ROUTE_ID = T2.ROUTE_ID and T2.status = 'Y' and T2.ENTRY_DATE = TO_DATE('11/02/2017', 'dd/mm/yyyy') and T2.ITEM_ID = T4.ITEM_ID and T8.ENTRY_DATE = TO_DATE('11/02/2017', 'dd/mm/yyyy') and T8.SR_ID = '174369' and T2.OUTLET_ID = T8.OUTLET_ID group by T6.ITEM_GROUP_NAME, T7.ZONE_NAME, T5.SR_NAME, T5.SR_ID, T5.MOBILE_NO, T2.ENTRY_DATE, T2.OUTLET_ID, T3.ROUTE_NAME, T1.OUTLET_NAME, T1.OUTLET_ADDRESS, T1.PROPRITOR_NAME, T1.MOBILE_NUMBER, T2.status || T8.REASON --在这里修改 order by T1.OUTLET_NAME