CODEWITHSUNDEEP
sqloracle-databasesql-update
dee
dee

Reputation: 609

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

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.

Upvotes: 6

Views: 8452

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

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

Upvotes: 7

Sam DeHaan
Sam DeHaan

Reputation: 10325

It looks like the character set mismatch is between the options for the CASE statement to return.

Working

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

Not Working

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

Fix

Try making your return options nvarchars (ie N'anItemOne') and then it should be fine with t.First_Col.

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

Upvotes: 1

Related Questions