Forum Stats

  • 3,855,261 Users
  • 2,264,491 Discussions
  • 7,905,951 Comments

Discussions

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

2»

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,729 Red Diamond
    edited Mar 11, 2019 9:39AM
    TP0692 wrote:doesn't work in oracle 11g. 

    Works for me:

    SQL> SELECT BANNER FROM V$VERSION

      2  /

    BANNER

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

    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

    PL/SQL Release 11.2.0.3.0 - Production

    CORE    11.2.0.3.0      Production

    TNS for 64-bit Windows: Version 11.2.0.3.0 - Production

    NLSRTL Version 11.2.0.3.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> INSERT INTO SALES_INFO VALUES('NY','New York',3,2018,2000)

      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        2018                                      2000

    NY        New York        2019                                      3000

    SQL>

    So post similar SQL*Plus snippet showing it doesn't work for you.

    SY.

  • TP0692
    TP0692 Member Posts: 14
    edited Mar 11, 2019 10:19AM

    I got the scenario where the issue came, When I add two columns in PIVOT

    sql> CREATE TABLE SALES_INFO(STATE VARCHAR2(10),CITY VARCHAR2(10),BILL_MONTH NUMBER,BILL_YEAR NUMBER,SALES NUMBER,qty NUMBER)

    [2019-03-11 19:48:12] completed in 48ms

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

    [2019-03-11 19:48:16] 1 row affected in 111ms

    sql> INSERT INTO SALES_INFO VALUES('NY','New York',3,2018,2000,20)

    [2019-03-11 19:48:18] 1 row affected in 18ms

    sql> CREATE OR REPLACE VIEW sales_temp AS SELECT state,city,tranYear,JAN_AVG_SALES,JAN_AVG_QTY,FEB_AVG_SALES,FEB_AVG_QTY,MAR_AVG_SALES,MAR_AVG_QTY

        FROM (

        SELECT

        state as state,

        city as city,

        avg(sales) AS avg_sales,

        avg(qty) AS avg_qty,

        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, SUM(AVG_QTY) AS AVG_QTY 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))

    [2019-03-11 19:48:28] completed in 46ms

    sql> ALTER SESSION SET NLS_SORT=BINARY_CI

    [2019-03-11 19:48:43] completed in 15ms

    sql> ALTER SESSION SET NLS_COMP=LINGUISTIC

    [2019-03-11 19:48:47] completed in 19ms

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

    [2019-03-11 19:48:53] [42000][979] ORA-00979: not a GROUP BY expression

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Mar 11, 2019 10:28AM

    You know you've got nls_comp set to linguistic in your example there?

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,729 Red Diamond
    edited Mar 11, 2019 3:04PM

    You still didn't show exact version. It appears you are on 11.2.0.1 or older

    SQL> SELECT BANNER FROM V$VERSION

      2  /

    BANNER

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

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

    PL/SQL Release 11.2.0.1.0 - Production

    CORE    11.2.0.1.0      Production

    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production

    NLSRTL Version 11.2.0.1.0 - Production

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

    ,SALES NUMBER,qty NUMBER)

      2  /

    Table created.

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

      2  /

    1 row created.

    SQL> INSERT INTO SALES_INFO VALUES('NY','New York',3,2018,2000,20)

      2  /

    1 row created.

    SQL> CREATE OR REPLACE VIEW sales_temp AS SELECT state,city,tranYear,JAN_AVG_SALES,JAN_AVG_QTY,FEB_A

    VG_SALES,FEB_AVG_QTY,MAR_AVG_SALES,MAR_AVG_QTY

      2      FROM (

      3      SELECT

      4      state as state,

      5      city as city,

      6      avg(sales) AS avg_sales,

      7      avg(qty) AS avg_qty,

      8      BILL_MONTH  AS tranMonth,

      9      BILL_YEAR  AS tranYear

    10      FROM sales_info al

    11      WHERE  sales <> 0

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

    13      ORDER BY al.BILL_YEAR, al.BILL_MONTH

    14      )

    15      PIVOT (SUM(AVG_SALES) AS AVG_SALES, SUM(AVG_QTY) AS AVG_QTY FOR(tranmonth) IN (1 as JAN,2 a

    s FEB,3 as MAR,4 as APR,5

    16  as MAY,6 AS JUNE,7 as JULY,8 as AUG,9 as SEP,10 as OCT, 11 as NOV,12 as DEC))

    17  /

    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  /

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

    *

    ERROR at line 1:

    ORA-00979: not a GROUP BY expression

    SQL>

    Use NLS_SORT instead of setting session NLS_SORT & NLS_COMP:

    SQL> SELECT BANNER FROM V$VERSION

      2  /

    BANNER

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

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

    PL/SQL Release 11.2.0.1.0 - Production

    CORE    11.2.0.1.0      Production

    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production

    NLSRTL Version 11.2.0.1.0 - Production

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

      2  /

    STATE      CITY        TRANYEAR JAN_AVG_SALES JAN_AVG_QTY FEB_AVG_SALES FEB_AVG_QTY MAR_AVG_SALES MAR_AVG_QTY

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

    NY        New York        2018                                                              2000          20

    NY        New York        2019                                                              3000          10

    SQL>

    And, as I already suggested, to get rid of that unnecessary GROUP BY:

    SQL> SELECT BANNER FROM V$VERSION

      2  /

    BANNER

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

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

    PL/SQL Release 11.2.0.1.0 - Production

    CORE    11.2.0.1.0      Production

    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production

    NLSRTL Version 11.2.0.1.0 - Production

    SQL> CREATE OR REPLACE VIEW sales_temp AS SELECT state,city,tranYear,JAN_AVG_SALES,JAN_AVG_QTY,FEB_AVG_SALES,FEB_AVG_QTY,MAR_AVG_SALE

    S,MAR_AVG_QTY

      2      FROM (

      3      SELECT

      4      state as state,

      5      city as city,

      6      sales,

      7      qty,

      8      BILL_MONTH  AS tranMonth,

      9      BILL_YEAR  AS tranYear

    10      FROM sales_info al

    11      WHERE  sales <> 0

    12      )

    13      PIVOT (AVG(SALES) AS AVG_SALES, AVG(QTY) AS AVG_QTY FOR(tranmonth) IN (1 as JAN,2 as FEB,3 as MAR,4 as APR,5

    14  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> SELECT * FROM SALES_TEMP WHERE NLSSORT(NVL(STATE,'X'),'NLS_SORT = BINARY_CI') LIKE NLSSORT('ny','NLS_SORT = BINARY_CI')

      2  /

    STATE      CITY        TRANYEAR JAN_AVG_SALES JAN_AVG_QTY FEB_AVG_SALES FEB_AVG_QTY MAR_AVG_SALES MAR_AVG_QTY

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

    NY        New York        2018                                                              2000          20

    NY        New York        2019                                                              3000          10

    SQL>

    SY.

    TP0692TP0692
  • TP0692
    TP0692 Member Posts: 14
    edited Mar 12, 2019 12:36AM

    Thanks for the detailed information. What's the difference between the usage? I need to query my entire application with case insensitive. If I remove the alter session for the NLS_COMP, How could I manage on each dynamic query? Do I have any option for it?

  • TP0692
    TP0692 Member Posts: 14
    edited Mar 12, 2019 3:40AM

    NLS_SORT=Binary_CI work properly with NLS_COMP = linguistics , so i thought it might be self explanatory

  • TP0692
    TP0692 Member Posts: 14
    edited Mar 13, 2019 2:10AM

    HI SY,

    For the above example, If I want to achieve contains search, it doesn't work. Kindly help us here.

    SELECT * FROM SALES_TEMP WHERE NLSSORT(NVL(STATE,'X'),'NLS_SORT = BINARY_CI') LIKE NLSSORT('n%','NLS_SORT = BINARY_CI')

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,729 Red Diamond
    edited Mar 13, 2019 7:12AM

    Yes, NLSSORT precludes from using wildcards. I think it would be simpler if you just use, say, UPPER for state and city in the view:

    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     UPPER(state) as state,

      5     UPPER(city) as city,

      6     sales,

      7     BILL_MONTH  AS tranMonth,

      8     BILL_YEAR  AS tranYear

      9     FROM sales_info al

    10     WHERE   sales <> 0

    11     )

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

    13  /

    View created.

    SQL> SELECT  *

      2    FROM  SALES_INFO

      3  /

    STATE CITY       BILL_MONTH  BILL_YEAR      SALES        QTY

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

    NY    New York            3       2019       3000         10

    NY    New York            3       2018       2000         20

    ny    New York            3       2019      13000        110

    Ny    New York            3       2018      12000        120

    SQL> SELECT  *

      2    FROM  SALES_TEMP

      3    WHERE NVL(STATE,'X') LIKE UPPER('n%')

      4  /

    STATE CITY         TRANYEAR JAN_AVG_SALES FEB_AVG_SALES MAR_AVG_SALES

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

    NY    NEW YORK         2018                                      7000

    NY    NEW YORK         2019                                      8000

    SQL> SELECT  *

      2    FROM  SALES_TEMP

      3    WHERE NVL(STATE,'X') LIKE UPPER('n%')

      4      AND NVL(CITY,'X') LIKE  UPPER('n%')

      5  /

    STATE CITY         TRANYEAR JAN_AVG_SALES FEB_AVG_SALES MAR_AVG_SALES

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

    NY    NEW YORK         2018                                      7000

    NY    NEW YORK         2019                                      8000

    SQL>

    SY.