Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle PIVOT with nvl,ltrim,rtrim where clause doesnt work with NLS_SORT=BINARY_CI

TP0692Mar 11 2019 — edited Mar 13 2019

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

Comments

Post Details

Added on Mar 11 2019
18 comments
922 views