Matches versus substring

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