Dzisiaj napisałem zapytanie, które miało na celu wyliczenie sumy różnic pewnych pól. Problem polega na tym, że tabela przechowuje historię zmian obiektu i wszystkie zmiany – niezależnie od typu – są przechowywane jako ntext. Tak jest i trzeba z tym żyć. Zapytanie wyglądało mniej więcej tak:
select SUM(CONVERT(decimal(16,2), t.NewValue) - CONVERT(decimal(16,2), t.OldValue) from Table t
Wersja MS Sql Server to 2008 R2. W oryginale zapytanie jest dużo bardziej skomplikowane i przewiduje wystąpienie wartości null oraz dane pobierane są z większej ilości tabel, ale na potrzeby przykładu powyższa forma spokojnie wystarczy. Po testowym uruchomieniu zapytania napotkałem następujący komunikat:
Explicit conversion from data type ntext to decimal is not allowed.
Ok, jak nie można to nie można, kombinujemy dalej. Przeczesałem internet i dostaję dziesiątki porad w stylu:
CAST(CAST(t.NewValue AS nvarchar) AS decimal) CAST(CAST(t.NewValue AS nvarchar) AS decimal)
ale dostaję inny błąd:
Error converting data type nvarchar to numeric.
Przyglądam się tym swoim wartościom w tabeli … przyglądam … i ………. MAM!!!
Przecież to jest ntext, a kod aplikacji serwerowej zapisuje do bazy decimal z przecinkiem jako separatorem. T-SQL oczekuje decimal z kropką, a więc zmiana kodu na:
CONVERT(decimal(16,2), REPLACE(CAST(th.NewValue AS nvarchar(max)), ',', '.'))
rozwiązuje problem.
Warto również pamiętać o tym, że:
CONVERT(decimal, value)
da nam tylko wartość całkowitą, a dopiero
CONVERT(decimal(16,n), value)
zwróci pełną wartość decymalną.