CODEWITHSUNDEEP
oracle-databaseplsql
mimabe
mimabe

Reputation: 37

EXTRACT(month FROM date) issue

I have an issue with the EXTRACT function on Oracle and I have no idea what is wrong with the script below. For some reason if I use the AS keyword with the EXTRACT it throws Invalid Identifier when referenced in the GROUP BY sentence. It works if I reference the column by the automatic name, given without the AS keyword.

WITH counted AS(
  SELECT UNIT,
         STATUS,
         SDESC,
         COUNT(1) AS cnt,
         EXTRACT(month FROM STATUS_DATE) AS statusMonth,
         ROW_NUMBER() OVER(PARTITION BY UNIT ORDER BY COUNT(1) DESC) AS rk
  FROM ATMSTATUS
  WHERE 
    STATUS_DATE BETWEEN '1-OCT-13' AND '31-OCT-13' AND 
    STATUS > 0
  GROUP BY UNIT, STATUS, SDESC, statusMonth
  ORDER BY UNIT, cnt DESC
)
SELECT *
FROM counted
WHERE rk < (10 + 1)

Upvotes: 0

Views: 82

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8113

You can't use aliases in GROUP BY clause, you have to use the whole expression instead:

WITH counted AS(
  SELECT UNIT,
         STATUS,
         SDESC,
         COUNT(1) AS cnt,
         EXTRACT(month FROM STATUS_DATE) AS statusMonth,
         ROW_NUMBER() OVER(PARTITION BY UNIT ORDER BY COUNT(1) DESC) AS rk
  FROM ATMSTATUS
  WHERE 
    STATUS_DATE BETWEEN '1-OCT-13' AND '31-OCT-13' AND 
    STATUS > 0
  GROUP BY UNIT, STATUS, SDESC, EXTRACT(month FROM STATUS_DATE)
  ORDER BY UNIT, cnt DESC
)
SELECT *
FROM counted
WHERE rk < (10 + 1)

Upvotes: 1

Related Questions