Forum Stats

  • 3,855,365 Users
  • 2,264,500 Discussions
  • 7,905,979 Comments

Discussions

Categories

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

TP0692
TP0692 Member Posts: 14
edited Mar 13, 2019 7:12AM in SQL & PL/SQL

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

Tagged:
BrunoVromanmathguyTP0692
· Share on TwitterShare on Facebook
«1

Answers

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited 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.

    · Share on TwitterShare on Facebook
  • BrunoVroman
    BrunoVroman Member Posts: 1,848 Silver Crown
    edited 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.

    · Share on TwitterShare on Facebook
  • TP0692
    TP0692 Member Posts: 14
    edited Mar 11, 2019 8:07AM

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

    B. Thanks for the link.

    BrunoVroman
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,729 Red Diamond
    edited Mar 11, 2019 8:30AM

    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.

    mathguy
    · Share on TwitterShare on Facebook
  • BrunoVroman
    BrunoVroman Member Posts: 1,848 Silver Crown
    edited Mar 11, 2019 9:12AM

    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.

    · Share on TwitterShare on Facebook
  • TP0692
    TP0692 Member Posts: 14
    edited Mar 11, 2019 9:27AM

    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.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.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

    · Share on TwitterShare on Facebook
  • TP0692
    TP0692 Member Posts: 14
    edited Mar 11, 2019 9:14AM

    No Bruno, Still it throws the same error

    · Share on TwitterShare on Facebook
  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Mar 11, 2019 9:16AM

    Try and replicate it using sqlplus.

    Java just complicates things.

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,729 Red Diamond
    edited 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.

    · Share on TwitterShare on Facebook
  • TP0692
    TP0692 Member Posts: 14
    edited 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.

    · Share on TwitterShare on Facebook