ORA-00904“无效标识符”，用于group by子句中的标识符

调香师

与compiere数据库，我正在尝试ro show
在两个日期之间。
柱子：
指定
数量销售（仓库）客户销售
数量卖家卖送货员
总数量
产品的营业额
为什么这个查询
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 ;
给我这个错误：
ORA-00904: "QTYLIV" : identificateur non valide
00904. 00000 -  "%s: invalid identifier"
*Cause:
*Action:
Erreur 脿 la ligne 75, colonne 3

解决方案:


            除了在order by子句中，您不能在相同级别的SQL中引用列别名。
在文档中（添加了重点）：

您可以使用列别名 c_alias 来标记选择列表中紧接的前一个表达式，以便以新标题显示该列。别名在查询期间有效地重命名了选择列表项。
别名可以在ORDER BY子句中使用，但不能在query中的其他子句中使用。

当您QTYLIV在GROUP BY集群中引用时，尚未评估选择列表，并且别名不存在。这就是查询的解析和执行方式。
当你在选择列表中复杂的表达式它往往是最简单的包装，在外部选择做事后??分组：
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 ;
注意，不要在外部select的GROUP BYorORDER BY子句中使用原始的表别名，因为它们不再是作用域。

ORA-00904“无效标识符”，用于group by子句中的标识符

浏览过的版块

调香师 LV1