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

Question

与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

Answer 1

除了在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子句中使用原始的表别名，因为它们不再是作用域。