ORA-00904“group by子句中的标识符的无效标识符” [英] ORA-00904 "Invalid identifier” for an identifier in a group by clause
问题描述
栏：
>指定
qty销售（仓库）客户销售
qty-sale卖家出售送货员
- 总数
为什么是此查询
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 。
ELSE SUM（il.linenetamt）* - 1
END AS netHT，
CASE
WHEN i.IsReturnTrx ='N'
THEN SUM i.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'
和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 .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;
给我这个错误：
ORA-00904：QTYLIV：identificateur non valide
00904. 00000 - ％s：无效标识符
*原因：
*操作：
Erreuràla ligne 75，colonne 3
不能在同一级别的SQL中引用列别名，除了 order by 子句。
从文档（增加了强调）：
您可以使用列别名 c_alias 来标记选择列表中紧邻的前一个表达式，列将显示一个新标题。在查询的持续时间，别名有效地重命名选择列表项。 别名可以在
ORDER BY子句中使用，但不能用于查询中的其他子句。
当您在 GROUP BY 中引用 QTYLIV 列表尚未评估，并且别名不存在。这是查询被解析和执行的方式。
当你在选择列表中有复杂的表达式时，通常最简单的是在外部选择中进行换行，然后进行分组：
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'
和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（'CO'，'CL'）
--AND i.IsSoTrx ='Y'
--AND p
$ $ $ $ $ $ $ $ $ $
qtydepot
ORDER BY name，
dateinvoiced;
请注意，您不要在 GROUP BY中使用原始表别名
或 ORDER BY 子句，因为那些不在范围内。
with compiere database i am trying 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
解决方案 You can't refer to a column alias in the same level of SQL, except in the order by clause.
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 BY clause, but not other clauses in the query.
When you refer to QTYLIV in the GROUP BY cluase the select list hasn't been evaluated yet and the alias doesn't exist. This is just how the query is parsed and executed.
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 BY or ORDER BY clauses in the outer select, as those are no longer in scope.
这篇关于ORA-00904“group by子句中的标识符的无效标识符”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
