Forum Stats

  • 3,852,536 Users
  • 2,264,112 Discussions
  • 7,905,096 Comments

Discussions

Creation of function based index using escape

user12010914
user12010914 Member Posts: 1 Blue Ribbon
edited Sep 18, 2013 11:28AM in SQL & PL/SQL

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

Answers

  • AlbertoFaenza
    AlbertoFaenza Member Posts: 2,047 Silver Trophy

    Hi,

    Please read:

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

    Regards.

    Alberto

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,488 Red Diamond
    edited Sep 18, 2013 11:31AM

    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.

This discussion has been closed.