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?

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.

I'm running V7, CTE's are a V8 thing.....

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.

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....

I think that DB2 will not allow a variable on the right side of the LIKE.

Are you looking for transactions whose MERCHANT_NM is included somewhere within the order's MERCHANT_NM ? Are they different sized fields?

Why don't you just use locate?

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


???

LOCATE seems to work. I had to RTRIM both sides first, without the RTRIM, no go.

Thanks!!!!

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)

CTE on mainframes was introduced only with V8

Cheers
sathyaram