Using Case Statement in linked servers

Question

Select distinct 
    Case 
        When ABC = 'C1' then 'C1'
        When ABC = 'C1C' then 'C1C' 
        When ABC = 'C2' then 'C2' 
        When ABC = 'C3' then 'C3'
        When ABC = 'C3C' then 'C3C' 
        When ABC = 'C3CC' then  'C3CC'
        When ABC = 'C4' then 'C4' 
        When ABC = 'C4C' then  'C4C'
        When ABC = 'C4CC' then 'C4CC' 
        When ABC = 'C6' then  'C6'
        When ABC = 'C7' then  'C7'
        When ABC = 'C8' then  'C8'
        When ABC = 'F' then  'F'
        When ABC = 'F2' then 'F2'
        When ABC = 'V' then 'V'
        When ABC = 'V2' then 'V2'
    End as ABC
from LinkedServer.database.dbo.Tblname

When I execute the above SQL on a regular database it works perfect with no errors but if I use a linked server prefix then I get the below error:

Error message: Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 125, Level 15, State 4, Line 1 Case expressions may only be nested to level 10.

It works fine if I delete some case expressions (usually less than 10 cases) works fine. Any thoughts or work around: One work around I know: Create SP/View/Function on Linked Server with however many case expressions and then call that created SP/View/Function. I'm not looking for this though.

Answer 1

Your case can also be written

Case When ABC IN ('C1','C1C','C2'...) then ABC End as ABC

If you're using a CASE expression with a mountain of built-in translations to descriptions, then you're doing it wrong. You should be creating a lookup table instead and performing a LEFT JOIN. The lookup table can exist on either side of the linked server.

select
    l.description as abc
    ...
from LinkedServer.database.dbo.Tblname t
left join ABCValues l on t.ABC = l.value

Answer 2

For whatever reason, you cannot have more than 10 when in such a case expression.

In your case, it is easy to fix with an in clause:

case when ABC in ('C1', . . . ) then ABC
end

If your SQL code is more complex, then you might be able to use a mapping table and join:

with mapping as (
    select 'C1' as val, 'C1' as newval union all
    . . .
)
select m.newval, . . .
from LinkedServer.database.dbo.Tblname t left outer join
     mapping m
     on t.ABC = m.val