Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2004
    Posts
    11

    Unanswered: Need help with LIKE/CONTAINS

    Runing DB2, Mainframe, V7

    SELECT
    T.MERCH_NM
    , ORD.ORDER_NM
    FROM
    (SELECT
    ACCT_NBR
    , UCASE('''' || '%' || RTRIM(O.MERCH_NM) || '%' || '''')
    AS "ORDER_NM"
    FROM
    ORDER O
    WHERE
    O.ACCT_NBR = 'ABC' )
    AS ORD
    , TRANSACTION T
    WHERE
    T.ACCT_NBR = ORD.ACCT_NBR
    AND UCASE(T.MERCH_NM) LIKE ORD.ORDER_NM

    DB2 is having an issue with the LIKE statement.

    I want to say T.MERCH_NM LIKE '%O.MERCH_NM%', but use the value of O.MERCH_NM, not the string "O.MERCH_NM".

    You cannot do nested statements with LIKE (why not IBM....?).

    Is there another way to do a contains?? Any thoughts?

  2. #2
    Join Date
    Aug 2004
    Posts
    33
    You could try using a common table expression instead of a nested querry.

    With
    ORD (ACCT_NBR, ORDER_NM) AS
    (SELECT ACCT_NBR , UCASE('''' || '%' || RTRIM(O.MERCH_NM) || '%' || '''')
    AS "ORDER_NM"
    FROM ORDER O
    WHERE
    O.ACCT_NBR = 'ABC' )

    SELECT
    T.MERCH_NM
    , ORD.ORDER_NM
    FROM ORD, TRANSACTION T
    WHERE
    T.ACCT_NBR = ORD.ACCT_NBR
    AND UCASE(T.MERCH_NM) LIKE ORDER_NM

    I hope this works for you.

  3. #3
    Join Date
    Jun 2004
    Posts
    11
    I'm running V7, CTE's are a V8 thing.....

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    Try something like this:

    SELECT
    T.MERCH_NM
    , ORD.ORDER_NM
    FROM
    (SELECT
    ACCT_NBR
    , UCASE(RTRIM(O.MERCH_NM))
    AS "ORDER_NM"
    FROM
    ORDER O
    WHERE
    O.ACCT_NBR = 'ABC' )
    AS ORD
    , TRANSACTION T
    WHERE
    T.ACCT_NBR = ORD.ACCT_NBR
    AND UCASE(T.MERCH_NM) LIKE '%' || ORD.ORDER_NM || '%'

    not quite sure how to concatenate the wildcard with the column value so you may have to play with the syntax.

  5. #5
    Join Date
    Jun 2004
    Posts
    11
    Nope again....

    I even tried to create a GLOBAL TEMPORARY, load it with the value, confirmed that it was a correct value in the temp, then joining TRANSACTION and the GLOBAL TEMP. The LIKE still wants a literal or a host variable or ??.

    Feel free to send more ideas....

  6. #6
    Join Date
    Jun 2004
    Posts
    11
    I think that DB2 will not allow a variable on the right side of the LIKE.

  7. #7
    Join Date
    Aug 2004
    Posts
    330
    Are you looking for transactions whose MERCHANT_NM is included somewhere within the order's MERCHANT_NM ? Are they different sized fields?

  8. #8
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Why don't you just use locate?

    AND LOCATE(UPPER(O.MERCH_NUM), UPPER(T.MERCH_NUM)) != 0


    ???

  9. #9
    Join Date
    Jun 2004
    Posts
    11
    LOCATE seems to work. I had to RTRIM both sides first, without the RTRIM, no go.

    Thanks!!!!

  10. #10
    Join Date
    Aug 2004
    Posts
    33

    Exclamation

    Common table expressions can be used in DB2 ver 7. We are currently using them, all though I am on DB2 UDB for Windows.

  11. #11
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Quote Originally Posted by RonD
    Common table expressions can be used in DB2 ver 7. We are currently using them, all though I am on DB2 UDB for Windows.
    I concur (and I'm on AIX)

  12. #12
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    CTE on mainframes was introduced only with V8

    Cheers
    sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •