Hi all
IDS V7.31, HP-UX10.20
The following query is failing when the substring method is used, but
works when the MATCHES equivalent is coded in. I've seen this on SE
before, caused by a duff index, but I've done oncheck -cI on the
tables involved in this query, finding no errors.
Any clues?
(both dbaccess and isql produce the same results; the sqexplain plans
for both methods are identical))

SELECT cl.clmainid_keynme, cl.personal_sex,
cl.personal_dob, cl.address_add1, cl.address_add2,
cl.address_add3, cl.address_add4, cl.address_city,
cl.address_cntry, cl.address_postcd, cl.clmainid_ref,
c.policy_number, cl.status_code, c.status_code ,
c.polst_id_code
FROM contract c, clientmain cl
WHERE c.addressee_ref = 430374
AND c.addressee_ref = cl.clmainid_ref
AND c.master_id_ref < 10
AND c.status_code IN ('active','not_active')
AND cl.master_id_ref < 10
AND cl.status_code IN ('active','not_active')
AND c.plan__id_code MATCHES 'PRP*'
AND c.polst_id_code MATCHES 'IF*' <----- THIS WORKS (1 row
returned)
--- AND c.polst_id_code[1,2] = "IF" <----- THIS RETURNS NO ROWS
--
Malc_p

Hi,

You use both:
AND c.polst_id_code MATCHES 'IF*'
AND c.polst_id_code[1,2] = "IF"
at the same time ??
Or first one , after other ?

Please, try to use:
AND c.polst_id_code LIKE 'IF%'
to see if the result is the same as "matches" ....

Try to:
AND c.polst_id_code[1] = "I"
to see if the same row is returned.

I have never listen about 'duff index', what you wanna mean ??

Tip: Try to update/upgrade your operational system

Best regards. R. Ferronato

malc_p@btinternet.com (Malc P) wrote in message news:<8c28402a.0401230258.4def328d@posting.google. com>...
> Hi all
> IDS V7.31, HP-UX10.20
> The following query is failing when the substring method is used, but
> works when the MATCHES equivalent is coded in. I've seen this on SE
> before, caused by a duff index, but I've done oncheck -cI on the
> tables involved in this query, finding no errors.
> Any clues?
> (both dbaccess and isql produce the same results; the sqexplain plans
> for both methods are identical))
>
> SELECT cl.clmainid_keynme, cl.personal_sex,
> cl.personal_dob, cl.address_add1, cl.address_add2,
> cl.address_add3, cl.address_add4, cl.address_city,
> cl.address_cntry, cl.address_postcd, cl.clmainid_ref,
> c.policy_number, cl.status_code, c.status_code ,
> c.polst_id_code
> FROM contract c, clientmain cl
> WHERE c.addressee_ref = 430374
> AND c.addressee_ref = cl.clmainid_ref
> AND c.master_id_ref < 10
> AND c.status_code IN ('active','not_active')
> AND cl.master_id_ref < 10
> AND cl.status_code IN ('active','not_active')
> AND c.plan__id_code MATCHES 'PRP*'
> AND c.polst_id_code MATCHES 'IF*' <----- THIS WORKS (1 row
> returned)
> --- AND c.polst_id_code[1,2] = "IF" <----- THIS RETURNS NO ROWS