anybody know how many case expression can be in linked server query?
when i have more then 10 "case" expression witjh linked server, sql query
give this message :
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 8: Incorrect syntax near 'Qry1068'.
Server: Msg 125, Level 15, State 1, Line 1
Case expressions may only be nested to level 10.

any microsoft document or page show the detail about this?

Thanks in advance
Soonyu

Please post your query
Show quoteHide quote

l 2000 sp3 sit in win2003

when we run the quere in server A.. it look okay with case expression more
then 10
but when we run query in server B with linkserver to server A
sql server give this message
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Qry1068'.
Msg 125, Level 15, State 4, Line 1

Case expressions may only be nested to level 10.

select distinct rp.invoice_id,
CASE
WHEN (rp.product_group_code = 2 and c.catelog_id = 1) then 'PG'
WHEN (rp.product_group_code = 2 and c.catelog_id = 3) then 'JB'
WHEN (rp.product_group_code = 9 and c.catelog_id = 1) then 'KL'
WHEN (rp.product_group_code = 9 and c.catelog_id = 2) then 'SG'
WHEN (rp.product_group_code = 9 and c.catelog_id = 3) then 'JB'
WHEN (rp.product_group_code = 8 and c.catelog_id = 1) then 'GEORGETOWN'
WHEN (rp.product_group_code = 3 and c.catelog_id = 2) then 'GENTING'
WHEN (rp.product_group_code not in (2, 3, 8, 9) and c.catelog_id = 1) then
'PERAK'
WHEN (rp.product_group_code not in (2, 3, 8, 9) and c.catelog_id = 2) then
'N9'
WHEN (I.product_code not in (2, 3, 8, 9) and c.catelog_id = 3) then 'KEDAH'
WHEN (I.product_code not in (2, 3, 8, 9) and c.catelog_id = 3) then 'PERIS'
ELSE ''
END
from a.db1.dbo.invoice rp, a.db1.dbo.contract c
WHERE rp.id=c.id

if i take out of one of the case condition, no error and result come out
I just wonder whether this is sql2000 sp3 bug or limitation using case
expression over sql linked server

Regards

Show quoteHide quote

Count Occurances in a column
Who has the highest bid?
Need Help Please...
How to compare strings for equality in Transact SQL
Timeout expired