Dlaczego połączone serwery mają ograniczenie do 10 oddziałów w wyrażeniu CASE?

Dlaczego to CASEwyrażenie:

SELECT CASE column 
        WHEN 'a' THEN '1' 
        WHEN 'b' THEN '2' 
        ... c -> i
        WHEN 'j' THEN '10' 
        WHEN 'k' THEN '11'  
    END [col] 
FROM LinkedServer.database.dbo.table

Wyprodukować ten wynik?

Komunikat o błędzie: Msg 8180, poziom 16, stan 1, wiersz 1
Nie można przygotować oświadczenia.
Msg 125, poziom 15, stan 4, wiersz 1
Wyrażenia wielkości liter można zagnieżdżać tylko na poziomie 10.

Oczywiście nie ma tu zagnieżdżonego CASEwyrażenia, chociaż jest więcej niż 10 „gałęzi”.

Kolejna osobliwość. Ta wbudowana funkcja wartościowana w tabeli powoduje ten sam błąd:

ALTER FUNCTION [dbo].[fn_MyFunction]
(   
     @var varchar(20)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT CASE column 
            WHEN 'a' THEN '1' 
            WHEN 'b' THEN '2' 
            ... c -> i
            WHEN 'j' THEN '10' 
            WHEN 'k' THEN '11'  
        END [col] 
    FROM LinkedServer.database.dbo.table
)

Ale podobny TVF z wieloma stwierdzeniami działa dobrze:

ALTER FUNCTION [dbo].[fn_MyFunction]
(   
    @var varchar(20)
)
RETURNS @result TABLE 
(
    value varchar(max)
)
AS
BEGIN
    INSERT INTO @result
    SELECT CASE column 
            WHEN 'a' THEN '1' 
            WHEN 'b' THEN '2' 
            ... c -> i
            WHEN 'j' THEN '10' 
            WHEN 'k' THEN '11'  
        END [col] 
    FROM LinkedServer.database.dbo.table

RETURN;
END

Oczywiście nie ma tu zagnieżdżonego CASEwyrażenia.

Nie w tekście zapytania, nie. Parser zawsze rozwija CASEwyrażenia do postaci zagnieżdżonej:

SELECT CASE SUBSTRING(p.Name, 1, 1)
        WHEN 'a' THEN '1' 
        WHEN 'b' THEN '2' 
        WHEN 'c' THEN '3' 
        WHEN 'd' THEN '4' 
        WHEN 'e' THEN '5' 
        WHEN 'f' THEN '6' 
        WHEN 'g' THEN '7' 
        WHEN 'h' THEN '8' 
        WHEN 'i' THEN '9' 
        WHEN 'j' THEN '10' 
        WHEN 'k' THEN '11'  
    END
FROM AdventureWorks2012.Production.Product AS p

To zapytanie jest lokalne (brak połączonego serwera), a skalar obliczeniowy definiuje następujące wyrażenie:

Jest to w porządku, gdy jest wykonywane lokalnie, ponieważ parser nie widzi zagnieżdżonej CASEinstrukcji o głębokości ponad 10 poziomów (choć przekazuje ją do późniejszych etapów lokalnej kompilacji zapytań).

Jednak w przypadku serwera połączonego wygenerowany tekst może zostać przesłany do zdalnego serwera w celu kompilacji. W takim przypadku zdalny analizator składni widzi zagnieżdżoną CASEinstrukcję o głębokości większej niż 10 poziomów i pojawia się błąd 8180.

Kolejna osobliwość. Ta wbudowana funkcja wartościowana w tabeli powoduje ten sam błąd

Funkcja in-line jest rozszerzana w miejscu na oryginalny tekst zapytania, więc nie jest zaskoczeniem, że ten sam błąd występuje w przypadku połączonego serwera.

Ale podobny TVF z wieloma stwierdzeniami działa dobrze

Podobne, ale nie takie same. MsTVF obejmuje niejawną konwersję na varchar(max), co ma na celu zapobieżenie CASEwysłaniu wyrażenia na zdalny serwer. Ponieważ CASEjest oceniany lokalnie, analizator składni nigdy nie widzi nadmiernie zagnieżdżonego CASEi nie ma błędu. Jeśli zmienisz definicję tabeli z varchar(max)niejawnego typu CASEwyniku - varchar(2)- wyrażenie zostanie ponownie przekazane za pomocą msTVF i pojawi się błąd.

Ostatecznie błąd pojawia się, gdy nadmiernie zagnieżdżony CASEjest oceniany przez serwer zdalny. Jeśli CASEnie zostanie to ocenione w iteratorze zdalnego zapytania, nie wystąpią żadne błędy. Na przykład następujące obejmuje CONVERTnie, które nie jest zdalnie sterowane, więc nie występuje błąd, nawet jeśli używany jest serwer połączony:

SELECT CASE CONVERT(varchar(max), SUBSTRING(p.Name, 1, 1))
        WHEN 'a' THEN '1' 
        WHEN 'b' THEN '2' 
        WHEN 'c' THEN '3' 
        WHEN 'd' THEN '4' 
        WHEN 'e' THEN '5' 
        WHEN 'f' THEN '6' 
        WHEN 'g' THEN '7' 
        WHEN 'h' THEN '8' 
        WHEN 'i' THEN '9' 
        WHEN 'j' THEN '10' 
        WHEN 'k' THEN '11'  
    END
FROM SQL2K8R2.AdventureWorks.Production.Product AS p

Mam przeczucie, że zapytanie jest przepisywane gdzieś po drodze, aby mieć nieco inną CASEstrukturę, np

CASE WHEN column = 'a' THEN '1' ELSE CASE WHEN column = 'b' THEN '2' ELSE ...

Uważam, że jest to błąd w jakimkolwiek podłączonym dostawcy serwerów, z którego korzystasz (w rzeczywistości być może wszyscy z nich - widziałem to zgłoszone przeciwko kilku). Uważam również, że nie powinieneś wstrzymywać oddechu, czekając na poprawkę, zarówno pod względem funkcjonalności, jak i mylącego komunikatu o błędzie wyjaśniającego zachowanie - to było zgłaszane od dłuższego czasu, dotyczy serwerów połączonych (które nie miały zbyt wiele miłości od SQL Server 2000) i wpływa na znacznie mniej osób niż ten mylący komunikat o błędzie , który musi zostać naprawiony po tym samym okresie użytkowania.

Jak podkreśla Paul , SQL Server rozszerza twoje CASEwyrażenie do zagnieżdżonej odmiany, a serwerowi temu się to nie podoba. Komunikat o błędzie jest mylący, ale tylko dlatego, że podstawowa konwersja wyrażenia nie jest natychmiast widoczna (ani intuicyjna w żaden sposób).

Jednym obejściem (innym niż zmiana funkcji dodana do pytania) byłoby utworzenie widoku lub procedury składowanej na połączonym serwerze i odwołanie się do tego, zamiast przekazywania pełnego zapytania przez dostawcę połączonego serwera.

Innym (zakładając, że twoje zapytanie jest tak uproszczone, a po prostu chcesz, aby współczynnik liczbowy liter az) było:

SELECT [col] = RTRIM(ASCII([column])-96)
FROM LinkedServer.database.dbo.table;

Jeśli absolutnie potrzebujesz tego, aby działało tak, jak jest, sugeruję skontaktowanie się bezpośrednio z pomocą techniczną i otwarcie sprawy, chociaż nie mogę ręczyć za wyniki - mogą one po prostu zapewnić obejścia, do których masz już dostęp na tej stronie.

możesz to obejść przez

SELECT COALESCE(
CASE SUBSTRING(p.Name, 1, 1)
    WHEN 'a' THEN '1' 
    WHEN 'b' THEN '2' 
    WHEN 'c' THEN '3' 
    WHEN 'd' THEN '4' 
    WHEN 'e' THEN '5' 
    WHEN 'f' THEN '6' 
    WHEN 'g' THEN '7' 
    WHEN 'h' THEN '8' 
    WHEN 'i' THEN '9' 
    ELSE NULL
END,
CASE SUBSTRING(p.Name, 1, 1)
    WHEN 'j' THEN '10' 
    WHEN 'k' THEN '11'  
END)
FROM SQL2K8R2.AdventureWorks.Production.Product AS p

Innym obejściem tego problemu jest użycie logiki opartej na zestawie, zastępując CASEwyrażenie lewym złączeniem (lub zewnętrznym zastosowaniem) do tabeli referencyjnej ( refw kodzie poniżej), która może być stała, tymczasowa lub pochodna tabela / CTE. Jeśli jest to potrzebne w wielu zapytaniach i procedurach, wolę mieć to jako stałą tabelę:

SELECT ref.result_column AS [col] 
FROM LinkedServer.database.dbo.table AS t
  LEFT JOIN
    ( VALUES ('a',  '1'),
             ('b',  '2'), 
             ('c',  '3'),
             ---
             ('j', '10'),
             ('k', '11')
    ) AS ref (check_col, result_column) 
    ON ref.check_col = t.column ;

jednym ze sposobów obejścia tego jest włączenie testu do whenklauzuli, tj

case
  when SUBSTRING(p.Name, 1, 1) = 'a' THEN '1'
...