IBM Sterling Transformation Extender

 View Only

  • 1.  Using DBQUERY with Oracle LIKE condition

    Posted Wed April 05, 2006 06:59 AM

    Originally posted by: SystemAdmin


    I need to be able to search for a user entered substring in a char field in Oracle. I'm trying to use LIKE condition. The condition uses % for patterns.

    In the example below I'm searching for %X% , i.e. any value containing X

    dbquery(
    "select dummy from dual where dummy like '%X%'",
    "-DBTYPE ORACLE -CONNECT c -USER scott -PASSWORD tiger -TRACE"
    )

    The problem is that DSTX is trying to substitute %X% as a resource registry variable.

    What's the workaround? Escape characters?

    Thanks,
    Edward
    #IBM-Websphere-Transformation-Extender
    #IBMSterlingTransformationExtender
    #DataExchange


  • 2.  Re: Using DBQUERY with Oracle LIKE condition

    Posted Wed April 05, 2006 07:13 AM

    Originally posted by: SystemAdmin


    I have used the SQL REPLACE to get round this problem.

    dbquery(
    "select dummy from dual where dummy like REPLACE('?X?','?','%')",
    "-DBTYPE ORACLE -CONNECT c -USER scott -PASSWORD tiger -TRACE"
    )
    #IBM-Websphere-Transformation-Extender
    #IBMSterlingTransformationExtender
    #DataExchange


  • 3.  RE: Re: Using DBQUERY with Oracle LIKE condition

    Posted Tue October 15, 2024 11:39 PM
    Edited by NeverXP YANG Tue October 15, 2024 11:52 PM

    Hi, this method doesn't solve my problem, can anyone help me?

    My query:

    SELECT ROWNUM RN, USTRD RMT FROM MY_TEST WHERE (USTRD NOT LIKE REPLACE('/ID?', '?', '%') AND USTRD NOT LIKE REPLACE('/AMT?', '?', '%'))

    What I got in DBL:

    SELECT ROWNUM RN, USTRD RMT FROM MY_TEST WHERE (USTRD NOT LIKE REPLACE('/ID?', '?', ''))

    Second condition was removed.



    ------------------------------
    NeverXP YANG
    ------------------------------

    Original Message


  • 4.  RE: Re: Using DBQUERY with Oracle LIKE condition

    Posted Wed October 16, 2024 12:30 AM

    Referring to another post, I solved this problem by creating a resource registry called PercentSign.



    ------------------------------
    NeverXP YANG
    ------------------------------

    Original Message


  • 5.  Re: Using DBQUERY with Oracle LIKE condition

    Posted Wed April 05, 2006 07:15 AM

    Originally posted by: SystemAdmin


    Once you get to Q/A stage, don't forget to remove the -Trace.
    #IBMSterlingTransformationExtender
    #DataExchange
    #IBM-Websphere-Transformation-Extender


  • 6.  Re: Using DBQUERY with Oracle LIKE condition

    Posted Wed April 05, 2006 07:44 AM

    Originally posted by: SystemAdmin


    Thanks JanH. Your replace() solution works like a charm.

    Too bad IBM DSTX TX team cannot add an escape character to change the meaning of %

    -TRACE was there to see what DSTX does to my SELECT statement.

    -Edward
    #DataExchange
    #IBM-Websphere-Transformation-Extender
    #IBMSterlingTransformationExtender


  • 7.  Re: Using DBQUERY with Oracle LIKE condition

    Posted Wed April 05, 2006 07:53 AM

    Originally posted by: SystemAdmin


    Unfortunately, there is a limited choice in characters, and someone else always seems to be using them :-)
    #IBM-Websphere-Transformation-Extender
    #IBMSterlingTransformationExtender
    #DataExchange