oracle ORA-00904 group by 子句中标识符的“无效标识符”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24980245/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-00904 "Invalid identifier” for an identifier in a group by clause
提问by mugiwaradz
with compiere database i am trying ro show
使用 compiere 数据库，我正在尝试 ro show
between two dates.
两个日期之间。
column:
柱子：
designation
指定
qty sale (depot) client sales
数量销售（仓库）客户销售
qty-sale sellers selling deliveryman
数量销售卖家销售送货员
-total qty
-总数量
value turnover by product
按产品的价值周转率
why is this query
为什么这个查询
SELECT p.name AS design,
p.M_PRODUCT_CATEGORY_ID,
il.PRICEACTUAL AS price,
bp.C_BPARTNER_ID AS idpartner,
CASE
WHEN i.IsReturnTrx = 'N'
THEN SUM(il.linenetamt)
ELSE SUM(il.linenetamt)*-1
END AS netHT,
CASE
WHEN i.IsReturnTrx = 'N'
THEN SUM((il.linenetamt + (il.linenetamt * t.rate /100)))
ELSE SUM((il.linenetamt + (il.linenetamt * t.rate /100)))*-1
END AS netTTC,
(SELECT il.qtyinvoiced
FROM C_InvoiceLine il
WHERE bp.ISCUSTOMER ='Y'
AND bp.ISACTIVE ='Y'
AND bp.C_BPARTNER_ID= 19999
) AS qtydepot,
(SELECT qtyinvoiced
FROM C_InvoiceLine il
WHERE bp.ISCUSTOMER ='Y'
AND bp.C_BPARTNER_ID= 18888
) AS qtyliv,
org.description AS orgname,
loc2.address1,
loc2.address2,
loc2.address3,
loc2.address4,
loc2.city,
loc2.postal,
oi.phone,
oi.phone2,
oi.fax,
i.DATEINVOICED AS dat
FROM C_InvoiceLine il
INNER JOIN M_PRODUCT p
ON(p.M_PRODUCT_ID = il.M_PRODUCT_ID)
INNER JOIN C_INVOICE i
ON (i.C_INVOICE_ID = il.C_INVOICE_ID)
INNER JOIN C_BPARTNER bp
ON (bp.C_BPARTNER_ID = i.C_BPARTNER_ID)
INNER JOIN AD_Org org
ON (i.AD_Org_ID = org.AD_Org_ID)
INNER JOIN C_Tax t
ON (t.C_Tax_ID = il.C_Tax_ID)
INNER JOIN ad_orginfo oi
ON (org.ad_org_id=oi.ad_org_id)
INNER JOIN c_location loc2
ON (oi.c_location_id=loc2.c_location_id)
--WHERE i.DateInvoiced BETWEEN $P{Date1} AND $P{Date2}
--AND
--i.DocStatus in ('CO','CL')
--AND i.IsSoTrx = 'Y'
--AND p.isstocked='Y'
GROUP BY p.name ,
p.M_PRODUCT_CATEGORY_ID,
il.QTYINVOICED,
il.PRICEACTUAL,
i.DATEINVOICED,
bp.C_BPARTNER_ID,
org.description,
loc2.address1,
loc2.address2,
loc2.address3,
loc2.address4,
loc2.city,
loc2.postal,
oi.phone,
oi.phone2,
oi.fax,
i.IsReturnTrx,
i.dateinvoiced,
qtyliv,
qtydepot
ORDER BY p.name ,
i.dateinvoiced ;
giving me this error:
给我这个错误：
ORA-00904: "QTYLIV" : identificateur non valide
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Erreur à la ligne 75, colonne 3
回答by Alex Poole
You can't refer to a column alias in the same level of SQL, except in the order byclause.
除了在order by子句中，您不能在同一级别的 SQL 中引用列别名。
From the documentation(emphasis added):
从文档（强调添加）：
You can use a column alias, c_alias, to label the immediately preceding expression in the select list so that the column is displayed with a new heading. The alias effectively renames the select list item for the duration of the query. The alias can be used in the
ORDER BYclause, but not other clauses in the query.
您可以使用列别名c_alias来标记选择列表中紧接在前面的表达式，以便以新标题显示该列。别名在查询期间有效地重命名选择列表项。别名可以在
ORDER BY子句中使用，但不能在查询中的其他子句中使用。
When you refer to QTYLIVin the GROUP BYcluase the select list hasn't been evaluated yet and the alias doesn't exist. This is just how the query is parsed and executed.
当您QTYLIV在GROUP BY条款中引用时，尚未评估选择列表并且别名不存在。这就是查询的解析和执行方式。
When you have complicated expressions in the select list it's often simplest to wrap that in an outer select and do the grouping afterwards:
When you have complicated expressions in the select list it's often simplest to wrap that in an outer select and do the grouping afterwards:
SELECT *
FROM (
SELECT p.name AS design,
p.M_PRODUCT_CATEGORY_ID,
il.PRICEACTUAL AS price,
bp.C_BPARTNER_ID AS idpartner,
CASE
...
(SELECT qtyinvoiced
FROM C_InvoiceLine il
WHERE bp.ISCUSTOMER ='Y'
AND bp.C_BPARTNER_ID= 18888
) AS qtyliv,
...
i.DATEINVOICED AS dat
FROM C_InvoiceLine il
INNER JOIN M_PRODUCT p
...
ON (oi.c_location_id=loc2.c_location_id)
--WHERE i.DateInvoiced BETWEEN $P{Date1} AND $P{Date2}
--AND
--i.DocStatus in ('CO','CL')
--AND i.IsSoTrx = 'Y'
--AND p.isstocked='Y'
)
GROUP BY name ,
M_PRODUCT_CATEGORY_ID,
QTYINVOICED,
PRICEACTUAL,
...
qtyliv,
qtydepot
ORDER BY name ,
dateinvoiced ;
Notice that you don't use the original table aliases in the GROUP BYor ORDER BYclauses in the outer select, as those are no longer in scope.
请注意，您不要在外部选择的GROUP BYorORDER BY子句中使用原始表别名，因为它们不再在范围内。
回答by Gordon Linoff
The problem is (at least) in the group byclause. Oracle does not allow column aliases in group by. You can readily fix this with a subquery:
问题是（至少）在group by条款中。Oracle 不允许在group by. 您可以使用子查询轻松解决此问题：
select t.*
from (select . . .
from . . .
where . . .
) t
group by . . .
order by . . . ;
In other words, put everything but the group byand order byin a subquery. Then put those clauses in the outer query.
换句话说，将除group byand 之外的所有内容都放在order by子查询中。然后将这些子句放在外部查询中。
