Problem:
On the S_FIRST_NAME and S_LAST_NAME of table columns TABLE_CONTACT being saved in some cases two spaces between the names, for example:
S_FIRST_NAME = 'JOHAN Stive'
S_FIRST_NAME = 'JOHAN Stive'
The query that is installed on production does not bring the right information, see query 2 and query 3
Query 2 (11 rows)
SELECT TC.OBJID, TC.PHONE, TC.X_CONTACT_ADD2ADDRESS, TC.S_LAST_NAME, TC.S_FIRST_NAME, TC.X_CONTACT_STATUS, TC.X_DOC_TYPE, X_DOC_NUM,
trim(SUBSTR(TS.X_CATEGORY_
CUSTOMER, 1, 8))
FROM SA.TABLE_CONTACT TC, SA.TABLE_CONTACT_ROLE CR, TABLE_SITE TS
WHERE CR.CONTACT_ROLE2CONTACT = TC.OBJID
AND CR.CONTACT_ROLE2SITE = TS.OBJID
AND TC.S_FIRST_NAME LIKE 'JOHAN STIVE%'
AND TC.S_LAST_NAME LIKE 'TOLENTINO RAMIREZ%'
AND trim(SUBSTR(TS.X_CATEGORY_
CUSTOMER, 1, 8)) = 'Postpago'
Query 3 (1 rows)
SELECT TC.OBJID, TC.PHONE, TC.X_CONTACT_ADD2ADDRESS, TC.S_LAST_NAME, TC.S_FIRST_NAME, TC.X_CONTACT_STATUS, TC.X_DOC_TYPE, X_DOC_NUM,
trim(SUBSTR(TS.X_CATEGORY_
CUSTOMER, 1, 8))
FROM SA.TABLE_CONTACT TC, SA.TABLE_CONTACT_ROLE CR, TABLE_SITE TS
WHERE CR.CONTACT_ROLE2CONTACT = TC.OBJID
AND CR.CONTACT_ROLE2SITE = TS.OBJID
AND TC.S_FIRST_NAME LIKE 'JOHAN STIVE%'
AND TC.S_LAST_NAME LIKE 'TOLENTINO RAMIREZ%'
AND trim(SUBSTR(TS.X_CATEGORY_
CUSTOMER, 1, 8)) = 'Postpago'
Solution: The only one I've found is the query 1 with the use of REPLACE, but the problem is using a TABLE ACCESS FULL, there is some other way. The DBA will not allow the use of replace that causes a performance problem
query 1 (12 rows)
SELECT TC.OBJID, TC.PHONE, TC.X_CONTACT_ADD2ADDRESS, TC.S_LAST_NAME, TC.S_FIRST_NAME, TC.X_CONTACT_STATUS, TC.X_DOC_TYPE, X_DOC_NUM,
trim(SUBSTR(TS.X_CATEGORY_
CUSTOMER, 1, 8))
FROM SA.TABLE_CONTACT TC, SA.TABLE_CONTACT_ROLE CR, TABLE_SITE TS
WHERE CR.CONTACT_ROLE2CONTACT = TC.OBJID
AND CR.CONTACT_ROLE2SITE = TS.OBJID
AND REPLACE(TC.S_FIRST_NAME, ' ', ' ') = 'JOHAN STIVE'
AND REPLACE(TC.S_LAST_NAME, ' ', ' ') = 'TOLENTINO RAMIREZ'
AND trim(SUBSTR(TS.X_CATEGORY_
CUSTOMER, 1, 8)) = 'Postpago'
query-1.jpg
query-2.jpg
query-3.jpg
Problema-REPLACE.docx
this is called a function-based index.
you could also try:
AND TC.S_FIRST_NAME LIKE 'JOHAN%STIVE%'
which translates into:
AND TC.S_FIRST_NAME LIKE REPLACE( TRIM('JOHAN STIVE%') , ' ', '%' )
the trim is to avoid that users putting leading spaces would result in a LIKE '%....%'
hope this helps