Could you use the replace command to get rid of the hyphen instead of trying to parse around it?

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
h.ins_group1 = REPLACE(a.group_, '-', '') and
a.IPA_ORDER = 2.0 and
Ltrim(Rtrim(a.IPA)) = 'LPC'

Am I reading your query wrong?

-Mike


>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