Oracle issues encountered when sorting cross-report column headers-oracle ORA-12704: Character set mismatch, varchar2 conversion to nvarchar2 character missing, and character type after case and else are consistent

When you sort the cross-report column header, you encounter these three questions, and here's a concrete example.

The design of the database is shown in table structure 1:

Figure 1

To get out of the student_name_,s.grade_,s.subject_name_, these three attributes, at the time I was writing SQL statements like this:

Select S.student_name_, S.grade_, S.subject_name_,

Case S.subject_name_

When ' language ' then ' a language '

When ' math ' then ' b math '

When ' English ' then ' C English '

else s.subject_name_

End

From student S

2 error reported after execution:

Figure 2

Later found that the cause of the error is: Subject_name_ is a nvarchar2 type, so, "the Chinese characters are varchar2 type, so to the" "in the Chinese characters are converted to NVARCHAR2 type, so the SQL statement is changed to the following:

Select S.student_name_, S.grade_, S.subject_name_,

Case S.subject_name_

When cast (' Language ' as NVARCHAR2) then cast (' A Language ' asnvarchar2 (10))

When cast (' Math ' as NVARCHAR2) then cast (' B Math ' asnvarchar2 (10))

When cast (' English ' as NVARCHAR2) then cast (' C English ' asnvarchar2 (10))

else s.subject_name_

End

From student S

However, after running the effect is not what I thought! As shown in result 3:

Figure 3

Very strange, "Chinese" and "mathematics" are all normal, English is not normal! Execute statement again

Select cast (' English ' as NVARCHAR2) from student s where s.subject_name_ = ' English '

The result is this, as shown in 4:

Figure 4

That is, varchar2 in the conversion to NVARCHAR2 caused by the loss of characters, when looking for a solution to see this article http://blog.csdn.net/tobeistdo/article/details/ 5610287, only to know that the TO_CHAR function should be used to VARCHAR2 and NVARCHAR2 type conversion. So, it was changed to write this:

Select S.student_name_,s.grade_,s.subject_name_,

Case S.subject_name_

When To_char (' language ') then To_char (' a language ')

When To_char (' math ') then To_char (' B math ')

When To_char (' English ') then To_char (' C English ')

else s.subject_name_

End as Other_name_

From student S

As a result, or an error, 5, or a character set mismatch:

Figure 5

Check a lot of information written by cattle to know: when the use of the case and else after the character type must be the same, but this still does not, then the case after the character type to be the same as when, else after the character type is OK, that is:

Select S.student_name_,s.grade_,s.subject_name_,

Case To_char (s.subject_name_)

When To_char (' language ') then To_char (' a language ')

When To_char (' math ') then To_char (' B math ')

When To_char (' English ') then To_char (' C English ')

else To_char (s.subject_name_)

End as Other_name_

From student S

The final 6 shows:

Figure 6

Top

0

Oracle issues encountered when sorting cross-report column headers-oracle ORA-12704: Character set mismatch, varchar2 conversion to nvarchar2 character missing, and character type after case and else are consistent