Creation of function based index using escape

user12010914

Hello,

I have the following SQL, sometimes performing bad:

SELECT DISTINCT UPPER(A.PROCESSIDCODE), UPPER(A.RULENAME), CHARSET

FROM XIB_DETECT A, XIB_PROCESSIDPROPERTIES B, XIB_RULES C

WHERE ( A.KEY1 = :P1 OR ( :P1 like REPLACE(REPLACE(REPLACE(REPLACE(KEY1,'%', '\%'),'_', '\_'),'?', '_'),'*','%') escape '\' AND A.REGFLAGS1 = 'Y') OR A.KEY1 = '*' AND A.REGFLAGS1 = 'Y')

AND (A.KEY2 = :P2 OR ( :P2 like REPLACE(REPLACE(REPLACE(REPLACE(KEY2,'%', '\%'),'_', '\_'),'?', '_'),'*','%') escape '\' AND A.REGFLAGS2 = 'Y') OR (A.KEY2 IS NULL AND A.REGFLAGS2 IS NULL ) )

AND (A.KEY3 = :P3 OR ( :P3 like REPLACE(REPLACE(REPLACE(REPLACE(KEY3,'%', '\%'),'_', '\_'),'?', '_'),'*','%') escape '\' AND A.REGFLAGS3 = 'Y') OR (A.KEY3 IS NULL AND A.REGFLAGS3 IS NULL ) )

AND (A.KEY4 = :P4 OR ( :P4 like REPLACE(REPLACE(REPLACE(REPLACE(KEY4,'%', '\%'),'_', '\_'),'?', '_'),'*','%') escape '\' AND A.REGFLAGS4 = 'Y') OR (A.KEY4 IS NULL AND A.REGFLAGS4 IS NULL ) )

AND (A.KEY5 = :P5 OR ( :P5 like REPLACE(REPLACE(REPLACE(REPLACE(KEY5,'%', '\%'),'_', '\_'),'?', '_'),'*','%') escape '\' AND A.REGFLAGS5 = 'Y') OR (A.KEY5 IS NULL AND A.REGFLAGS5 IS NULL ) )

AND (A.KEY6 IS NULL OR A.KEY6 = '*' AND REGFLAGS6 = 'Y')

AND (A.KEY7 IS NULL OR A.KEY7 = '*' AND REGFLAGS7 = 'Y')

AND (A.KEY8 IS NULL OR A.KEY8 = '*' AND REGFLAGS8 = 'Y')

AND (A.KEY9 IS NULL OR A.KEY9 = '*' AND REGFLAGS9 = 'Y')

AND (A.KEY10 IS NULL OR A.KEY10 = '*' AND REGFLAGS10 = 'Y')

AND ( ( A.PROCESSIDCODE IS NOT NULL AND UPPER(A.PROCESSIDCODE) = UPPER(B.PROCESSIDCODE) AND A.XLEVEL = B.XLEVEL AND B.ACTIVEFLAG = 'Y' )

OR ( A.RULENAME IS NOT NULL AND UPPER(A.RULENAME) = UPPER(C.RULENAME) AND A.XLEVEL = C.XLEVEL AND C.ACTIVEFLAG = 'Y' ) );

Now I want to create a function based index on the key1 column:

CREATE INDEX xib_detect_ix ON xib_detect (REPLACE(REPLACE(REPLACE(REPLACE(KEY1,'%', '\%'),'_', '\_'),'?', '_'),'*','%') escape '\') TABLESPACE ... ONLINE;

However, this is not working with "escape" '\', throwing: ORA-00907: missing right parenthesis

Any idea how to create an index on this construct with "escape"?

Database version is 10.2.0.3.

Thanks a lot.

Regards

Oliver

AlbertoFaenza

Hi,

Please read:

Post some sample data, explain your business requirements, and your expected output.

Regards.

Alberto

Frank Kulash

Hi,

You can get the "missing right parenthesis" error for many different syntax errors.

In this case, you really are missing a right parenthesis.  Your statement has 5 left '('s, but only 4 right ')'s.  It's easy to see this if you format your code:

CREATE  INDEX xib_detect_ix 
ON  xib_detect ( REPLACE ( REPLACE ( REPLACE ( REPLACE ( KEY1
                                                       , '%'
                                                       , '\%'
                                                       )
                                             , '_'
                                             , '\_'
                                             )
                                   , '?'
                                   , '_'
                                   )
                         , '*'
                         , '%'
                         ) 
                 escape '\'

ESCAPE is an option that you can use with the LIKE operator.  It gives you a mechanism for cancelling the special meaning of symbols like '%'.    You're not using the LIKE operator to create the index.  You're only using REPLACE, and no characters have any special meaning in REPLACE, so there's no way (or reason) to escape them.  Use ESCAPE in queries that use LIKE, when appropriate.