Why does the below query returns :

ORA-00904: "QUANTITY": invalid identifier 00904. 00000 - "%s: invalid identifier"

SELECT 
t1.TITLE as IDCODE,
min(t1.BUYDATE) as VALUEDATE,
'TEST' as ITEMNAME,
t1.Quantity AS QUANTITY
FROM Tab1 t1
WHERE t1.QTT_Type = 1 
GROUP BY "IDCODE",EXTRACT(MONTH FROM "VALUEDATE"),"ITEMNAME","QUANTITY"
ORDER BY "VALUEDATE"

CodePudding user response：

It means that there's no such column.

You can't use column alias in a GROUP BY clause - must be "real" column name or the whole expression. Also, generally speaking, you should avoid double quotes when working with Oracle.

Guessing table contents (as you didn't post it):

SQL> WITH
  2     tab1 (title,
  3           buydate,
  4           quantity,
  5           valuedate,
  6           qtt_type)
  7     AS
  8        (SELECT 'Test', SYSDATE, 100, SYSDATE, 1 FROM DUAL)

Query:

  9    SELECT t1.TITLE AS IDCODE,
 10           MIN (t1.BUYDATE) AS VALUEDATE,
 11           'TEST' AS ITEMNAME,
 12           t1.Quantity AS QUANTITY
 13      FROM Tab1 t1
 14     WHERE t1.QTT_Type = 1
 15  GROUP BY t1.title, --"IDCODE",               --> not alias, but real column name
 16           EXTRACT (MONTH FROM "VALUEDATE"),
 17           --         "ITEMNAME",              --> there's no such column in a table, and
 18           "QUANTITY"                          --  there's no use of grouping by a constant
 19  ORDER BY "VALUEDATE";

IDCO VALUEDAT ITEM   QUANTITY
---- -------- ---- ----------
Test 22.12.22 TEST        100

SQL>

As I said, avoid double quotes; I'd go with this instead:

  9    SELECT t1.title AS idcode,
 10           MIN (t1.buydate) AS valuedate,
 11           'TEST' AS itemname,
 12           t1.quantity AS quantity
 13      FROM tab1 t1
 14     WHERE t1.qtt_type = 1
 15  GROUP BY t1.title,
 16           EXTRACT (MONTH FROM valuedate),
 17           quantity
 18  ORDER BY valuedate;

IDCO VALUEDAT ITEM   QUANTITY
---- -------- ---- ----------
Test 22.12.22 TEST        100

SQL>