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

TP0692 · Mar 11, 2019 6:50AM

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

Cookiemonster76 · Mar 11, 2019 7:15AM

That looks like an oracle bug - in which case you should be going to oracle support.

They may have a patch for it.

BrunoVroman · Mar 11, 2019 7:33AM

Hello 2f4227c0...

-A- What about removing the "ORDER BY" inside the view definition? If you want an ORDER BY when querying the view, very well, but what is the use of adding this line inside the view definition:

ORDER BY al.BILL_YEAR, al.BILL_MONTH

-B- Welcome to you as a new member of the forum, but as you can see many users have a more human-friendly display name... Please take 2 minutes to change yours; have a look at https://community.oracle.com/docs/DOC-1022508

Best regards,

Bruno Vroman.

TP0692 · Mar 11, 2019 8:07AM

A. Yes, I agree the point but is this solution to the problem?

B. Thanks for the link.

Solomon Yakobson · Mar 11, 2019 8:27AM

Post SQL*Plus snippet showing what you did along with complete error message stack. I can't reproduce it:

SQL> SELECT BANNER FROM V$VERSION

2 /

BANNER

--------------------------------------------------------------------------------

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

PL/SQL Release 12.2.0.1.0 - Production

CORE 12.2.0.1.0 Production

TNS for 64-bit Windows: Version 12.2.0.1.0 - Production

NLSRTL Version 12.2.0.1.0 - Production

SQL> CREATE TABLE SALES_INFO(STATE VARCHAR2(10),CITY VARCHAR2(10),BILL_MONTH NUMBER,BILL_YEAR NUMBER

,SALES NUMBER)

2 /

Table created.

SQL> INSERT INTO SALES_INFO VALUES('NY','New York',3,2019,3000)

2 /

1 row created.

SQL> CREATE OR REPLACE VIEW sales_temp AS SELECT state,city,tranYear,JAN_AVG_SALES,FEB_AVG_SALES,MAR

_AVG_SALES

2 FROM (

3 SELECT

4 state as state,

5 city as city,

6 avg(sales) AS avg_sales,

7 BILL_MONTH AS tranMonth,

8 BILL_YEAR AS tranYear

9 FROM sales_info al

10 WHERE sales <> 0

11 GROUP BY al.state, al.city, al.BILL_YEAR, al.BILL_MONTH

12 ORDER BY al.BILL_YEAR, al.BILL_MONTH

13 )

14 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))

15 /

View created.

SQL> ALTER SESSION SET NLS_SORT=BINARY_CI

2 /

Session altered.

SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC

2 /

Session altered.

SQL> SELECT * FROM SALES_TEMP WHERE NVL(STATE,'X') LIKE 'ny'

2 /

STATE CITY TRANYEAR JAN_AVG_SALES FEB_AVG_SALES MAR_AVG_SALES

---------- ---------- ---------- ------------- ------------- -------------

NY New York 2019 3000

SQL>

SY.

BrunoVroman · Mar 11, 2019 9:10AM

Hello TP ;-)

yes, I expect that removing the ORDER BY from the view definition will solve your problem.

Despite the fact that putting an ORDER BY inside a view definition is correct from a syntax point of view:

CREATE VIEW myview AS SELECT ... FROM mytable1, mytable2 WHERE ... ORDER BY a, c DESC;

it doesn't make much sense, especially if then you issue something like

SELECT a, b, c FROM myview WHERE ... ORDER BY b, a;

, does it?

IMHO the "ORDER BY" has to be issued at query time, there is no "order" at the view level (like a table: we have a set of rows but there isn't any "order" in the set).

Best regards,

Bruno.

TP0692 · Mar 11, 2019 9:11AM

Additional Info:

1. I used to fire this query via Spring boot JdbcTemplate and I used ojdbc14.jar and oracle11g

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT STATES FROM SALES_INFO WHERE nvl(STATES,'') LIKE 'BLAH']; nested exception is java.sql.SQLException: ORA-00979: not a GROUP BY expression

at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:419)

at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:443)

at com.gofrugal.core.reportdata.util.DateFunctionsTest.testQuerywithHibernate(DateFunctionsTest.java:62)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:108)

at org.testng.internal.MethodInvocationHelper$1.runTestMethod(MethodInvocationHelper.java:209)

at org.springframework.test.context.testng.AbstractTestNGSpringContextTests.run(AbstractTestNGSpringContextTests.java:175)

at org.testng.internal.MethodInvocationHelper.invokeHookable(MethodInvocationHelper.java:221)

at org.testng.internal.Invoker.invokeMethod(Invoker.java:657)

at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:869)

at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1193)

at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:126)

at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:109)

at org.testng.TestRunner.privateRun(TestRunner.java:744)

at org.testng.TestRunner.run(TestRunner.java:602)

at org.testng.SuiteRunner.runTest(SuiteRunner.java:380)

at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:375)

at org.testng.SuiteRunner.privateRun(SuiteRunner.java:340)

at org.testng.SuiteRunner.run(SuiteRunner.java:289)

at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)

at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86)

at org.testng.TestNG.runSuitesSequentially(TestNG.java:1301)

at org.testng.TestNG.runSuitesLocally(TestNG.java:1226)

at org.testng.TestNG.runSuites(TestNG.java:1144)

at org.testng.TestNG.run(TestNG.java:1115)

at org.testng.IDEARemoteTestNG.run(IDEARemoteTestNG.java:72)

at org.testng.RemoteTestNGStarter.main(RemoteTestNGStarter.java:123)

Caused by: java.sql.SQLException: ORA-00979: not a GROUP BY expression

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)

at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:754)

at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)

at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:804)

at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1051)

at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:845)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1156)

at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1731)

at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1701)

at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)

at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:435)

at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408)

... 29 more

TP0692 · Mar 11, 2019 9:14AM

No Bruno, Still it throws the same error

Cookiemonster76 · Mar 11, 2019 9:16AM

Try and replicate it using sqlplus.

Java just complicates things.

Solomon Yakobson · Mar 11, 2019 9:34AM

And you don't need to group by twice (once explicit group by and second one implicit by pivot) All you need is:

CREATE OR REPLACE VIEW sales_temp AS SELECT state,city,tranYear,JAN_AVG_SALES,FEB_AVG_SALES,MAR_AVG_SALES

FROM (

SELECT

state as state,

city as city,

sales,

BILL_MONTH AS tranMonth,

BILL_YEAR AS tranYear

FROM sales_info al

WHERE sales <> 0

)

PIVOT (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))

/

Also, Oracle treats empty string as NULL, so NVL(STATE,'') does nothing and is same as STATE.

SY.

TP0692 · Mar 11, 2019 9:34AM

As you said when I execute the same in livesql.oracle.com with oracle 19c EE, It works fine but it seems doesn't work in oracle 11g. BTW , thanks for the additional info.

Forum Stats

Quick Links

Categories

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

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

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

Answers