Oracle issues encountered in crosstab column header sorting-oracle ORA-12704: Character set mismatch, Varchar2 converted to nvarchar2 character missing, case time else after the character type to be consistent ____oracle

In the sort of Cross report column header, you encounter these three questions, which are detailed below.

The table structure of the database being designed is as shown in Figure 1:

Figure 1

To get out of the student_name_,s.grade_,s.subject_name_, these three attributes, when I was writing this SQL statement:

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

Case S.subject_name_

When ' language ' then ' a language '

When ' mathematics ' then ' B Mathematics '

When ' English ' then ' C English '

else s.subject_name_

End

From student S

After execution the error reported as Figure 2:

Figure 2

Later found that the reason for the error is: Subject_name_ is nvarchar2 type, so, "the Chinese characters are varchar2 type, so to the ' Chinese characters are converted to the 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 Mathematics ' asnvarchar2 (10))

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

else s.subject_name_

End

From student S

However, after the run the effect is not what I think. The result is as shown in Figure 3:

Figure 3

Very strange, "Chinese" and "mathematics" are very normal, English is not normal. To execute the statement again

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

The result is this, as shown in Figure 4:

Figure 4

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

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 (' mathematics ') then To_char (' B Mathematics ')

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, as shown in Figure 5, or a character set mismatch:

Figure 5

Check a lot of cattle to write the information to know: The case of the use of when and else after the character type must be consistent, but this is still not, and then the character type after the case to change to the 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 (' mathematics ') then To_char (' B Mathematics ')

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

else To_char (s.subject_name_)

End as Other_name_

From student S

Finally, as shown in Figure 6:

Figure 6