ORA-00904 "Invalid identifier” for an identifier in a group by clause

Question

with compiere database i am trying ro show

between two dates.

column:

designation

qty sale (depot) client sales

qty-sale sellers selling deliveryman

-total qty

value turnover by product

why is this query

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 ;

giving me this error:

ORA-00904: "QTYLIV" : identificateur non valide
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Erreur à la ligne 75, colonne 3

Answer 1

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

From the documentation (emphasis added):

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.

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.

Answer 2

The problem is (at least) in the group by clause. Oracle does not allow column aliases in group by. You can readily fix this with a subquery:

select t.*
from (select . . .
      from . . .
      where . . .
     ) t
group by . . . 
order by . . . ;

In other words, put everything but the group by and order by in a subquery. Then put those clauses in the outer query.