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
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
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