大佬教程收集整理的这篇文章主要介绍了如何修复错误:SQL 错误 [904] [42000]: ORA-00904: "SPAREBOX": invalid identifier,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
我正在使用 sql 并且想要计算总和如下:
带有列和值的表 LOG0055D:
DELVRY_NO ITM_name @R_863_10586@L_QUANTITY PACKING_STYLE QUANTITY_IN_FulL_CTN
DVR20101900006 CHP-671R 61 30 60
我的食谱:
FulLBox = @R_863_10586@L_QUANTITY/PACKING_STYLE (only get Integer)
SPAREBox = @R_863_10586@L_QUANTITY - QUANTITY_IN_FulL_CTN
=> @R_863_10586@LBox = FulLBox + SPAREBox
我的查询:
SELECT
L55.DELVRY_NO,L55.ITM_name,L55.@R_863_10586@L_QUANTITY,L55.PACKING_STYLE,L55.QUANTITY_IN_FulL_CTN,SUM(L55.@R_863_10586@L_QUANTITY/L55.PACKING_STYLE) AS FulLBox,SUM(L55.@R_863_10586@L_QUANTITY - L55.QUANTITY_IN_FulL_CTN) AS SPAREBox,SUM(FulLBox + SPAREBoX) AS @R_863_10586@LBox
FROM LOG0055D L55
当我运行它时发生错误:
sql Error [904] [42000]: ORA-00904: "SPAREBox": invalID IDentifIEr
sql Error [937] [42000]: ORA-00937: not a single-group group function
如何解决问题?非常感谢
您在查询中有多个错误。一种是在定义它的同一个 SELECT
中对列别名的引用。第二个是缺少GROUP BY
。
所以我推测你真的想要:
SELECT L55.DELVRY_NO,L55.ITM_NAME,SUM(L55.@R_863_10586@L_QUANTITY / L55.PACKING_STYLE) AS FULLBOX,SUM(L55.@R_863_10586@L_QUANTITY - L55.QUANTITY_IN_FULL_CTN) AS SPAREBOX
(SUM(L55.@R_863_10586@L_QUANTITY /L55.PACKING_STYLE) +
SUM(L55.@R_863_10586@L_QUANTITY - L55.QUANTITY_IN_FULL_CTN)
) AS @R_863_10586@LBOX
FROM LOG0055D L55
GROUP BY L55.DELVRY_NO,L55.ITM_NAME;
请注意,我从 L55.@R_863_10586@L_QUANTITY,L55.PACKING_STYLE,L55.QUANTITY_IN_FULL_CTN
中删除了 SELECT
。这些值用于聚合函数中,因此将它们包含在 GROUP BY
中几乎没有意义。
您可能想要使用 WITH
(当然,在末尾添加 GROUP BY
,以避免 ORA-00937
错误)
我尝试根据您发布的内容重现您的表格结构:
create table LOG0055D (DELVRY_NO varchar2(100),ITM_name varchar2(100),@R_863_10586@L_QUANTITY number,PACKING_STYLE number,QUANTITY_IN_FULL_CTN number);
insert into LOG0055D values ('DVR20101900006','CHP-671R',61,30,60);
commit;
SQL> SELEct * from LOG0055D;
DELVRY_NO ITM_NAME @R_863_10586@L_QUANTITY PACKING_STYLE QUANTITY_IN_FULL_CTN
--------------- --------------- -------------- ------------- --------------------
DVR20101900006 CHP-671R 61 30 60
然后我使用 WITH
和 GROUP BY
WITH BOXCOUNT AS (
SELECT round(SUM(@R_863_10586@L_QUANTITY/PACKING_STYLE)) AS FULLBOX,round(SUM(@R_863_10586@L_QUANTITY - QUANTITY_IN_FULL_CTN)) AS SPAREBOX
FROM LOG0055D
)
SELECT
L55.DELVRY_NO,L55.@R_863_10586@L_QUANTITY,L55.QUANTITY_IN_FULL_CTN,BC.FULLBOX,BC.SPAREBOX,SUM(BC.FULLBOX + BC.SPAREBOX) AS @R_863_10586@LBOX
FROM LOG0055D L55,BOXCOUNT BC
GROUP BY
L55.DELVRY_NO,BC.SPAREBOX;
既然你写了,如果我猜对了,你只想要整数,我已经在 ROUND
中的 SUM
中添加了一个 WITH
最终结果如下:
DELVRY_NO ITM_NAME @R_863_10586@L_QUANTITY PACKING_STYLE QUANTITY_IN_FULL_CTN FULLBOX SPAREBOX @R_863_10586@LBOX
--------------- --------------- -------------- ------------- -------------------- ---------- ---------- ----------
DVR20101900006 CHP-671R 61 30 60 2 1 3
,
以后不能在选择中使用别名列(fullbox 和sparebox)。重复用于创建它们的计算以获得 @R_863_10586@lbox
以上是大佬教程为你收集整理的如何修复错误:SQL 错误 [904] [42000]: ORA-00904: "SPAREBOX": invalid identifier全部内容,希望文章能够帮你解决如何修复错误:SQL 错误 [904] [42000]: ORA-00904: "SPAREBOX": invalid identifier所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。