I got the exception ORA-00979: not a GROUP BY expression. Please provide the solution, I am using oracle11g
Example:
CREATE VIEW sales_temp AS SELECT state,city,tranYear,JAN_AVG_SALES,...,
FROM (
SELECT
state as state,
city as city,
avg(sales) AS avg_sales
BILL_MONTH AS tranMonth,
BILL_YEAR AS tranYear
FROM sales_info al
WHERE sales <> 0
GROUP BY al.state, al.city, al.BILL_YEAR, al.BILL_MONTH
ORDER BY al.BILL_YEAR, al.BILL_MONTH
)
PIVOT (SUM(AVG_SALES) AS AVG_SALES FOR(tranmonth) IN (1 as JAN,2 as FEB,3 as MAR,4 as APR,5 as MAY,6 AS JUNE,7 as JULY,8 as AUG,9 as SEP,10 as OCT, 11 as NOV,12 as DEC))
When I querying the above view
SELECT state FROM sales_temp WHERE NVL(state,'') LIKE 'BLAH'
with ALTER SESSION SET NLS_SORT=BINARY_CI
It throws ORA-00979: not a GROUP BY expression When I turn off this it executes. But I need both features. How could I achieve it. Please provide the solution. Thanks in advance