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"Jay" <webforum2000@.yahoo.com> wrote in message
news:9594a55e.0404230831.50bcabe0@.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|||
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!
