Forum Stats

  • 3,752,658 Users
  • 2,250,533 Discussions
  • 7,867,907 Comments

Discussions

Error "ORA-12704: character set mismatch" in CASE Statement

Edisson Gabriel López
Edisson Gabriel López Member Posts: 9 Green Ribbon

Greetings,

I have the following problem when using ELSE and I place the field that I want to recover, I get the following error: 

But if I comment on the ELSE, the sentence works:

Investigating use NVARCHAR2 (50) or ELSE N'XXXXX ', but without any successful results.

I look forward to your comments, thank you

Tagged:

Best Answer

  • mathguy
    mathguy Member Posts: 10,010 Gold Crown
    edited Jul 24, 2021 2:54AM Accepted Answer

    Investigating use NVARCHAR2 (50) or ELSE N'XXXXX ', but without any successful results.


    I have no idea what you mean by that.

    The first expression is a hard-coded '999', which is interpreted to be in the database character set. So we don't need to ask you about that.

    The expression in the ELSE clause is a column name. To get the error you reported, most likely that column is of NVARCHAR2 data type, which uses the national character set. Is that what you were trying to say with "investigating use NVARCHAR2(50)"?

    If indeed the column is in the NVARCHAR2 data type, the solution is to force the first expression (the hard-coded value) to also be in the national character set. You would write that as N'999'. Is that what you were trying to say "investigating use of ELSE N'XXXX'"? But if that's what you meant, what does have to do with the ELSE part of the CASE expression? The hard-coded value is the first expression, not the one in the ELSE clause.

    Try this:

    ...
    case when <something> then N'999' else y.pdnxtr end as pdnxtr
    ...
    

Answers

  • mathguy
    mathguy Member Posts: 10,010 Gold Crown
    edited Jul 24, 2021 2:54AM Accepted Answer

    Investigating use NVARCHAR2 (50) or ELSE N'XXXXX ', but without any successful results.


    I have no idea what you mean by that.

    The first expression is a hard-coded '999', which is interpreted to be in the database character set. So we don't need to ask you about that.

    The expression in the ELSE clause is a column name. To get the error you reported, most likely that column is of NVARCHAR2 data type, which uses the national character set. Is that what you were trying to say with "investigating use NVARCHAR2(50)"?

    If indeed the column is in the NVARCHAR2 data type, the solution is to force the first expression (the hard-coded value) to also be in the national character set. You would write that as N'999'. Is that what you were trying to say "investigating use of ELSE N'XXXX'"? But if that's what you meant, what does have to do with the ELSE part of the CASE expression? The hard-coded value is the first expression, not the one in the ELSE clause.

    Try this:

    ...
    case when <something> then N'999' else y.pdnxtr end as pdnxtr
    ...
    
  • Edisson Gabriel López
    Edisson Gabriel López Member Posts: 9 Green Ribbon
    edited Jul 24, 2021 3:09AM

    If it works, thanks.


    The problem was that it was placing as follows:

    ...
    1. case when y.pdan8 <> x.ibvend then '999' else cast(y.pdnxtr as NVARCHAR2(50)) end as pdnxtr
    2. case when y.pdan8 <> x.ibvend then '999' else N y.pdnxtr end as pdnxtr
    ...
    

    But with these two he was on another path. Very grateful to your solution it works perfect


    mathguy

  • mathguy
    mathguy Member Posts: 10,010 Gold Crown

    That is what I suspected.

    In the first branch of CASE, the expression returned is in the database characterset. In ELSE it's in the national character set.

    Your attempt was to convert the ELSE expression to the national character set - in which it was already. That's why your attempt failed; it still left the first expression in the db character set and the ELSE expression in the national character set.

    To solve the problem, you can either change the first branch expression to the national character set (that is what N does: '999' is in the database character set, N'999' is in the national character set), or you can convert the ELSE expression (the value coming from the table) to the database character set. But, presumably, the column was declared NVARCHAR2 for a reason. If so, then it is best not to attempt to convert it to VARCHAR2: if that was possible, then why is the column NVARCHAR2 in the first place? But there are no problems using N'999' instead of '999'.

    Please note also that the N is not an operator to convert to the national character set (as you were trying to use it in your second attempt). Trying to use it that way will (or did) result in an error. Instead, N is just a syntactical marker for literals (hard-coded constant values) in the national character set. You can use it right before a hard-coded string in single quotes, but not in front of a column name (or any other kind of expression, except string literals).