error with linked server

[Jay]

Hi
when i try to run a query using linked servers, i get the following
error.

Server: Msg 125, Level 15, State 1, Line 1
Case expressions may only be nested to level 10.

I do have more than 10 case statements, it works fine when it is less
than 10. can anyone tell me if there is a way to have more than 10
case statements. thanks alot.

Jay

my query
Select category, val, Sum(QTY) As QTY , yr
From
(
Select val, QTY2 As QTY,
KEEP = Case
When code = '004' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '005' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '003' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '017' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '007' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '008' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '009' And ((YR > 2003) Or (YR = 2003 And MON > 11))
Then 'N'
When CODE = '010' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '038' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '032' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '030' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '018' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
Else 'Y' End
From
amf a Join linkedserver.source.dbo.table2 b On a.COM = b.COM
Where CATEGORY In ('1') And CODE In ('001','003','004','005')
And b.YR Between 2003 And 2004 And b.MON <= 1
) x
Where KEEP = 'Y'
Group By CATEGORY, YR

[Simon Hayes]

"Jay" <we**********@yahoo.com> wrote in message
news:95**************************@posting.google.c om...

Hi
when i try to run a query using linked servers, i get the following
error.

Server: Msg 125, Level 15, State 1, Line 1
Case expressions may only be nested to level 10.

I do have more than 10 case statements, it works fine when it is less
than 10. can anyone tell me if there is a way to have more than 10
case statements. thanks alot.

Jay

my query
Select category, val, Sum(QTY) As QTY , yr
From
(
Select val, QTY2 As QTY,
KEEP = Case
When code = '004' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '005' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '003' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '017' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '007' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '008' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '009' And ((YR > 2003) Or (YR = 2003 And MON > 11))
Then 'N'
When CODE = '010' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '038' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '032' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '030' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '018' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
Else 'Y' End
From
amf a Join linkedserver.source.dbo.table2 b On a.COM = b.COM
Where CATEGORY In ('1') And CODE In ('001','003','004','005')
And b.YR Between 2003 And 2004 And b.MON <= 1
) x
Where KEEP = 'Y'
Group By CATEGORY, YR

Since most of your conditions are the same, have you tried something like
this?

Select category, val, Sum(QTY) As QTY , yr
From
(
Select val, QTY2 As QTY,
KEEP = Case
When code in ('004', '005, '003', '017', /* etc. */)
And ((YR > 2003) Or (YR = 2003 And MON > 12))
Else 'Y' End
From
amf a Join linkedserver.source.dbo.table2 b On a.COM = b.COM
Where CATEGORY In ('1') And CODE In ('001','003','004','005')
And b.YR Between 2003 And 2004 And b.MON <= 1
) x

Simon

[jay k]

hi
thanks for the suggestion. I have a problem, this is one of the query
where it is all the same, in a few others it varies a lot. i want to
know if the limitaion exists in sql using linked servers ( since it
works fine if i dont use linked servers and have them in the same
server). i want to get aroud this, so that i dont have to change all my
existing queries, and would hamper my using linked server. thanks.. any
suggestion?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!