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

问题描述

栏：

>

指定

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 子句。

（增加了强调）：

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子句中的标识符的无效标识符”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！