Forum Stats

  • 3,734,274 Users
  • 2,246,935 Discussions
  • 7,857,216 Comments

Discussions

Oracle 12cR2 Character Collection Collation

User_OX9Q3
User_OX9Q3 Member Posts: 22 Green Ribbon
edited Nov 10, 2020 10:34PM in SQL & PL/SQL

I am working with Oracle 12cR2 I have a table in which the columns of type char and varchar2 have a collection of BINARY characters, when using this table with MERGE INTO in the ON clause I get the character error ORA-43915 "could not be determine the collation ", this by the Oracle collation rules, which assigns USING_NLS_COMP and when matching it with my field with BINARY collection are not compatible, my question is is there any way to prevent Oracle from assigning USING_NLS_COMP by default and can be changed by BINARY?

Answers

  • Paulzip
    Paulzip Member Posts: 8,322 Blue Diamond
    edited Nov 10, 2020 6:41PM

    An example of your tables and issue would be more help, but as a side note, you can specify a column level collation and you could do this as part of your merge.

    e.g.

    merge into t2 d
    using (
     select first_name collate binary_ai as first_name , --etc etc   
     from  t1
    ) s
    on (<your_join>)
    when not matched then
     insert (<etc>)
     values (<etc>)
    when matched then
     update set
     <etc>
    ; 
    
  • User_OX9Q3
    User_OX9Q3 Member Posts: 22 Green Ribbon
    edited Nov 11, 2020 12:20AM

    My query is the next:

    MERGE INTO "CUENTA" USING(

    WITH Query1 (CVE_PRESUP, FIELD_1, FIELD_2, FIELD_3)

    AS (

    SELECT A.*

    FROM (

    SELECT CVE_PRESUP, ' ' AS FIELD_1, ' ' AS FIELD_2, ' ' AS FIELD_3

    FROM (

    SELECT * FROM CUENTA2

    )

    ) A

    LEFT OUTER JOIN CUENTA3 ON CUENTA3.CODICLASIF= A.FIELD_1

    ),

    Query2 (FIELD_1, FIELD_2, FIELD_3)

    AS (

    SELECT DISTINCT T_TABLA.FIELD_1, T_TABLA.FIELD_2, T_TABLA.FIELD_3

    FROM CUENTA

    INNER JOIN (

    SELECT FIELD_1, FIELD_2, FIELD_3

    FROM (

    SELECT ' ' AS FIELD_1, ' ' AS FIELD_2, ' ' AS FIELD_3

    FROM Query1

    )

    GROUP BY FIELD_1, FIELD_2, FIELD_3

    ) T_TABLA ON CUENTA.CODICLAS1 = T_TABLA.FIELD_1 AND CUENTA.CODICLAS2 = T_TABLA.FIELD_2 AND CUENTA.CODICLAS3 = T_TABLA.FIELD_3

    )

    SELECT * FROM Query2

    ) Query2 ON (Query2.FIELD_1 = CUENTA.CODICLAS1 AND Query2.FIELD_2 = CUENTA.CODICLAS2 AND Query2.FIELD_3 = CUENTA.CODICLAS3)

    WHEN MATCHED THEN UPDATE SET ESTIMADO1 = 0;

    If I comment the GROUP BY statement, I no longer get the error, I also want to know because if I comment, I no longer know, it gives me the error if in theory I still have the pseudo columns with USING_NLS_COMP.

    I did what you mention of putting the COLLATE operator to BINARY and the error no longer appears but I would like to fix it without applying this last operator

  • Paulzip
    Paulzip Member Posts: 8,322 Blue Diamond

    You'd have to show the table definitions as the collation can be on column or table and there's no way of knowing which one.

  • User_OX9Q3
    User_OX9Q3 Member Posts: 22 Green Ribbon
    edited Nov 11, 2020 5:18AM

    My tables are the next:

    CREATE TABLE CUENTA(CODICLAS1  CHAR(30) COLLATION BINARY,

                                                  CODICLAS2 CHAR(30) COLLATION BINARY,

                                                  CODICLAS3 CHAR(30) COLLATION BINARY,

    ESTIMADO1 NUMBER(1,0));

    CREATE TABLE CUENTA2 (CVE_PRESUP CHAR(30) COLLATION BINARY);

    CREATE TABLE CUENTA3(CODICLASIF CHAR(30) COLLATION BINARY);

  • User_OX9Q3
    User_OX9Q3 Member Posts: 22 Green Ribbon

    ESTIMADO1 is a NUMBER(1,0)

Sign In or Register to comment.