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!

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

522770
Hi Sascha,

DB XML has no current plans to support XInclude. Could you use an external XInclude processor to assemble your documents before they are put into DB XML?

John
473555
Could you use an external XInclude processor to assemble your documents before they are put into DB XML?
That would defeat the purpose I had in mind - storing a document once and then referencing it from multiple other documents. As I said I think that's pretty easy to implement using XQuery.

I just wanted to ask if XInclude was somewhere on the horizon.
Gmfeinberg-Oracle
Sascha,

The upcoming 2.3 release has support for classes that can be used
to "pull" result content as a stream of events, which could
allow your application to efficiently recognize, and use Xinclude
attributes.

Regards,

George
473555
Hi,
The upcoming 2.3 release has support for classes that
can be used to "pull" result content as a stream of events.
Sounds great - I think I already have some ideas of how to use that. :-)
1 - 4

Post Details

Added on Mar 11 2019
18 comments
933 views