sql - ORA-00904 "Invalid identifier” 用于 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

您不能在同一级别的 SQL 中引用列别名，除非在 order by 子句中。

From the documentation (强调):

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.

当您在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'
    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 ;

请注意，您不要在外部选择的 GROUP BY 或 ORDER BY 子句中使用原始表别名，因为它们不再在范围内。