Why does my nested case statement return a character set mismatch?

Question

I am creating a fairly simple update statement that includes a nested case statement to determine the update value. The column description is the same nvarchar (32) for both fields.

code below

UPDATE TableA t SET t.Last_Col = ( CASE WHEN t.First_Col = 'ItemOne' THEN 'anItemOne' WHEN t.First_Col = 'ItemTwo' THEN CASE WHEN t.Second_Col = 'ItemTwo_A' THEN 'aSecondItem_A' ELSE 'aSecondItem' END ELSE 'NoItem' END ); 

This code works, but when I try to use t.First_Col instead of the string "NoItem", I get a character set mismatch.

 ELSE t.First_Col END ); 

does not work. t.First_Col and t.Last_Col are both nvarchar2 (32), and I am trying to work with an application which, it seems to me, is not needed anyway.

 ELSE CAST(t.First_Col AS NVARCHAR2(32)) END ); 

Any tips and tricks are welcome. As always, thanks in advance.

Answer 1

The type of the case argument is determined by the first sentence in it. In this case, the first sentence is the string varchar, not nvarchar.

Try the following:

 UPDATE TableA t SET t.Last_Col = (CASE WHEN t.First_Col = N'ItemOne' THEN N'anItemOne' WHEN t.First_Col = N'ItemTwo' THEN (CASE WHEN t.Second_Col = N'ItemTwo_A' THEN N'aSecondItem_A' ELSE N'aSecondItem' END) ELSE N'NoItem' END ); 

Answer 2

It appears that the character set mismatch is between the parameters of the returned CASE statement.

IN

 'anItemOne' varchar 'aSecondItem_A' varchar 'aSecondItem' varchar 'NoItem' varchar 

Does not work

 'anItemOne' varchar 'aSecondItem_A' varchar 'aSecondItem' varchar t.First_Col nvarchar 

Fix

Try to make your return options nvarchar (i.e. N'anItemOne' ) and then it will be fine with t.First_Col .

 N'anItemOne' nvarchar N'aSecondItem_A' nvarchar N'aSecondItem' nvarchar t.First_Col nvarchar