In the follwoing query I am comparing Group information if there is "-" in the field value. But this gives me error message:
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.



select batch_num, d.claim_num, h.ins_paynum, h.Ins_group1, d.serv_amt, d.allowed_amt, d.discount, h.prov_lname, d.pct, h.level_
from _4_claim_temp_detail d, _4_claim_temp_header h, _5_ACCOUNT_GROUP_IPA a
where
d.claim_num = h.claim_num and
d.PCT = "F" and
h.INS_PAYNUM = a.account and
patindex("%-%", a.GROUP_ ) > 0 and
h.INS_GROUP1 = substring(a.GROUP_ ,1, (patindex("%-%", a.GROUP_ ) - 1)) + substring(a.Group_,(patindex("%-%", a.GROUP_ )+ 1), datalength(a.Group_))
and
a.IPA_ORDER = 2.0 and
Ltrim(Rtrim(a.IPA)) = 'LPC'

I tested in other queries and patindex works fine with the same string like:
update _4_claim_temp_header
SET _4_claim_temp_header.INS_PAYNUM = g.ACCOUNT,
_4_claim_temp_header.INS_EMPL = substring(g.DESC_, 1, 29)
from _4_claim_temp_header h, _9_GROUP g
where patindex("%-%", g.GROUP_ ) > 0
and h.INS_GROUP1 = substring(g.GROUP_ ,1, (patindex("%-%", g.GROUP_ ) - 1)) + substring(g.Group_,(patindex("%-%", g.GROUP_ )+ 1), datalength(g.Group_))
and h.SERV_BEG > g.EFFECTIVE and
h.SERV_BEG < g.TERM and
(h.level_ <> 9991 OR
h.level_ IS NULL)

thanks
bharat