May 1, 2002 at 11:36 am
I'm running a store procedure from my SQL 2000 reporting server which calls my linked SQL 2000 production server. It contains a CASE statment:
Territorycode= (case pb.territorycode
when 1 then 'North/West Vancr'
when 2 then 'Lower Mainland'
when 3 then 'Maple Ridge/Pitt Meadows'
when 4 then 'Squamish/Whistler'
when 5 then 'Pemberton/Hope'
when 6 then 'Fraser Valley'
when 7 then 'Thompson/Okanagan'
when 8 then 'Kootenays'
when 9 then 'Cariboo'
when 10 then 'Prince George'
when 11 then 'North Coast'
when 12 then 'Peace River'
when 13 then 'South Vancouver Island & Other Islands'
when 14 then 'Middle Vancouver Island/Sunshine Coast'
when 15 then 'Northern Vancouver Island'
when 16 then 'Canada/USA'
when 27 then 'White Rock/Twassen'
when 28 then 'Port Coquitlam'
when 31 then 'Waterfront N/W Vancr'
when 34 then 'Coquitlan'
when 35 then 'Cloverdale'
when 36 then 'Delta'
when 38 then 'Langley/Aldergrove'
when 40 then 'Ladner/Delta'
when 42 then 'Richmond'
when 45 then 'Port Moody/Coqutilam'
when 47 then 'Van-East of Cambie/Buraby/New West'
else 'Not defined in query' end)
When run on the production server it runs fine, But when run as a distributed query I get the followin error.
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Qry1058'.
Server: Msg 125, Level 15, State 1, Line 1
Case expressions may only be nested to level 10.
Is there a way to increase the nested level for the CASE expression in a distributed query?
Thanks Greg
May 1, 2002 at 3:47 pm
The error about nested to a level of 10 usually means you did something like this
EX
CASE colx
WHEN 1 THEN CASE coly
WHEN 1 THEN CASE colz
.......Nesting down
WHEN 2 THEN CASE cols
WHEN 1 THEN CASE colb
Can you post both code side for the local and the remote as I don't see Qry1058 referred to here. Need more info to help on this.
Hard to
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 1, 2002 at 9:03 pm
The code is below...Our business user is trying to run this from a server name MIS. It selects the data below from a linked server name POS. There is no Qry1058 just the Store procedure code below, it doesn't join or select anything on the MIS server just selects from the linked POS server.
select distinct pe.name,policynumber, pb.vic,pb.versionnumber,pc.vehiclemake,pc.vehiclemodel,pc.modelyear,Amountdue,Netrate, commissionamount,transactiondate,pd.transactionid,
Territorycode= (case pb.territorycode
when 1 then 'North/West Vancr'
when 2 then 'Lower Mainland'
when 3 then 'Maple Ridge/Pitt Meadows'
when 4 then 'Squamish/Whistler'
when 5 then 'Pemberton/Hope'
when 6 then 'Fraser Valley'
when 7 then 'Thompson/Okanagan'
when 8 then 'Kootenays'
when 9 then 'Cariboo'
when 10 then 'Prince George'
when 11 then 'North Coast'
when 12 then 'Peace River'
when 13 then 'South Vancouver Island & Other Islands'
when 14 then 'Middle Vancouver Island/Sunshine Coast'
when 15 then 'Northern Vancouver Island'
when 16 then 'Canada/USA'
when 27 then 'White Rock/Twassen'
when 28 then 'Port Coquitlam'
when 31 then 'Waterfront N/W Vancr'
when 34 then 'Coquitlan'
when 35 then 'Cloverdale'
when 36 then 'Delta'
when 38 then 'Langley/Aldergrove'
when 40 then 'Ladner/Delta'
when 42 then 'Richmond'
when 45 then 'Port Moody/Coqutilam'
when 47 then 'Van-East of Cambie/Buraby/New West'
else 'Not defined in query' end)
from pos.cover.dbo.vic_lookup_codes pa
inner join pos.cover.dbo.rate_group_vic_except pb
on pa.vic = pb.vic
and pa.versionnumber = pb.versionnumber
inner join pos.cover.dbo.Autocover_headers pc
on pb.vic = pc.vic
and pb. modelyear = pc. modelyear
and pb.territorycode = pc.territorycode
inner join pos.cover.dbo.transactions pd
on pc.transactionid = pd.transactionid
inner join pos.cover.dbo.Branches pe
on pe.branchid = pd.branchid
inner join pos.cover.dbo.Payments Pf
on pf.transactionid = pd.transactionid
where pb.versionnumber = 1
and pd.transactiondate >= '11-17-2001'
and pd.transactiondate <= '01-31-2002'
and statuscode = 1
group by pe.name,pb.territorycode,policynumber,pb.vic,pb.versionnumber, pc.vehiclemake,pc.vehiclemodel,pc.modelyear,Amountdue,Netrate, commissionamount,transactiondate,pd.transactionid,transactioncode
May 2, 2002 at 5:22 am
I tested your case issue and find no problem with it. There has to be something on their end causing it. Do a hand query and post all that happens back here maybe there is some clue. Also you state there is no Qry1058 in the code for the distributed query but from this
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Qry1058'.
had to be somewhere in the query otherwise would not have spit back.
Finally I am not sure that group by clause is buying you anything since you are doing a distinct and no sums, maxs, mins or any agregate function of the kind. Did you mean Order By maybe?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 2, 2002 at 9:03 am
This code was written by our business user, He has ask me(DBA) to trouble shoot the error. I believe his reasoning behind the group by is that if you use Order by without any of the items in the select distinct list you'll receive an error.
I tried to run the procedure above on a different server and got the same error.
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Qry1058'.
Server: Msg 125, Level 15, State 1, Line 1
Case expressions may only be nested to level 10.
When I double click on the error message it just takes me to the select line?
I forgot to mention that the database that the code is calling is in 6.5 compatibilty mode. When I run it from query analyer on the POS server It runs fine. When run on any other machine that has this server linked I get the same error.
Thanks
Greg
Edited by - gregtm on 05/02/2002 09:13:14 AM
May 2, 2002 at 9:31 am
I tried the same query with just 10 levels in the CASE statement and it works fine. When I add one more level to the the CASE statement I get the same error. Do you think this is due to the 6.5 compatibility?
thanks
GREG
May 2, 2002 at 10:17 am
Must be. But I try that and still have no issues. I will test some more tomorrow to see if I can get it to happen. May be something the way your table is built or the view? Not sure. If you can post the DDL of the table and view I can test here under that circumastance.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply