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

oracle, sql, sql-update

Solution

The type of a case statement is determined by the first clause in it. In this case, the first clause is a varchar string rather than an nvarchar.

Try this:

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 );

Problem

I've been building a fairly simple update statement that includes a nested case statement to determine the update value. 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 ); ``` That code works but when I try to use t.First_Col in place of string 'NoItem' I get the character set mismatch. ``` ELSE t.First_Col END ); ``` doesn't work. t.First_Col and t.Last_Col are both nvarchar2(32) and I've been trying to work with a cast which I think shouldn't be needed anyway. ``` ELSE CAST(t.First_Col AS NVARCHAR2(32)) END ); ``` Any hints or advice are greatly appreciated. As always thanks in advance.

Original source