Tags I am associated with:

Tags I am associated with: Microsoft SQL Server, IBM Cognos BI and Planning, Deltek Costpoint, Deltek Time & Expense, Magento, Intuit QuickBooks, Visual Basic 6, SSIS, Oracle, SQL development, data warehouse, reporting, eCommerce, accounting, finance, ETL

Wednesday, August 3, 2011

coalesce() function translated to case expression in SQL Server ?

Error message in SQL Server Management Studio:
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.

I just wanted to note this down before I forget... a couple of weeks ago I was deploying a view to a test environment and had to re-point the table source to a linked server and received this message.

the original view was something like this (simplified):
select *
from t
where coalesce(a,b,c,d,e,f,g,h,i,j,k,l) is not null

the above worked when t is on same server.

after pointing t to linked server:
select *
from .dbT.dbo.t
where coalesce(a,b,c,d,e,f,g,h,i,j,k,l) is not null

I got the error msg:
Case expressions may only be nested to level 10.

Apparently, SQL Server translates the coalesce function internally to nested case expressions and ran into some limitations via the linked server interface.

To work around the issue, I rewrote the coalesce function to OR expressions like:
select *
from .dbT.dbo.t
where
(a is not null
or b is not null
or c is not null
or d is not null
.
.
.
or l is not null)

No comments:

Post a Comment