ORA-00904 group by 子句中标识符的“无效标识符" [英] ORA-00904 "Invalid identifier” for an identifier in a group by clause

问题描述

使用 compiere 数据库，我正在尝试 ro show

with compiere database i am trying ro show

在两个日期之间.

列:

指定

数量销售(仓库)客户销售

qty sale (depot) client sales

卖快递的卖家数量

-总数量

产品营业额

为什么是这个查询

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

推荐答案

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

You can't refer to a column alias in the same level of SQL, except in the order by clause.

来自文档(强调添加):

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

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 时，选择列表还没有被评估并且别名不存在.这就是查询的解析和执行方式.

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.

当您在SELECT列表中具有复杂的表达式时，它通常最简单地包装在外部选择中并之后进行分组:

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 ;

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

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屋！