I was working on a rather lengthy query that used a typical CASE WHEN THEN ELSE END sequence, but it kept telling me “Case expressions may only be nested to level 10”, even though I wasn’t nesting or doing something unique with them, just like this:

case
when field1 = ‘data’ then ‘data1’
when field1 = ‘datax’ then ‘data2’
when field1 = ‘datay’ then ‘data3’
when field1 = ‘dataz’ then ‘data4’
when field1 = ‘dataa’ then ‘data5’
when field1 = ‘datab’ then ‘data6’
when field1 = ‘datab’ then ‘data7’
when field1 = ‘datad’ then ‘data8’
when field1 = ‘datae’ then ‘data9’
when field1 = ‘dataf’ then ‘data0’
when field1 = ‘datag’ then ‘data00’
when field1 = ‘datah’ then ‘data000’
else ‘aaa’
end as new_field

But it still would give me the error. After reading around and thinking about the query as a whole, it turns out the reason why it barks about a maximum of 10 statements is because there was a primary call to a linked server. Normally, you can put as many WHEN THEN statements in a CASE, but apparently when there’s a linked server involved, the max is 10. In this one, this CASE statement was one of the selection criteria and the table.field it was looking at was on the same database, but the main FROM DATABASE.TABLE of the query was the linked server, which is what was causing all the problem. Looks like I’ll have to either create a temp table and read from that, or add that table to my SSIS packages each day!