This discussion is archived
2 Replies Latest reply: Sep 18, 2013 8:31 AM by Frank Kulash RSS

Creation of function based index using escape

user12010914 Newbie
Currently Being Moderated

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

  • 1. Re: Creation of function based index using escape
    AlbertoFaenza Expert
    Currently Being Moderated

    Hi,

     

    Please read:Re: 2. How do I ask a question on the forums?

     

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

     

    Regards.

    Alberto

  • 2. Re: Creation of function based index using escape
    Frank Kulash Guru
    Currently Being Moderated

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points