not like predicate

select
rpt.id as rpt_id, dtl.id as dtl_id,
rpt.cd as rpt_li, dtl.cd as dtl_li,
rpt.cd_code as rpt_code,dtl.cd_code as dtl_code,
rpt.cd_summ as rpt_summ,dtl.cd_ind as dtl_ind

from udbadm.tab_dtl dtl,udbadm.tab_rpt rpt
where rpt.id = dtl.id
and rpt.dt = dtl.dt
and rpt.cd = dtl.cd
and rpt.cd_code = dtl.cd_code
and dtl.id ='00244407'
and rpt.cd_summ not like '''%'||dtl.cd_ind||'%''';

rpt.cd_summ not like '''%'||dtl.cd_ind||'%'''
The above statement will be
1st iteration) ABC,CDFG,TER not like '%TER%'
2nd iteration)ABC,CDFG,TER not like '%ABC%'
3rd iteration)ABC,CDFG,TER not like '%CDFG%'

But that statement gives this error:
[IBM][CLI Driver][DB2/6000] SQL0132N A like or posstr scalar function is not valid because the first operation is not a string expression or the second is not a string.

Please help me put with this. Thanks a lot in advance.

I tired to cast the columnn names but didn't work.

-MS

In order to put a single quote in an SQL statement, you might want to use the hexadecimal notation X'27'.

If you did a select like the following, you should see the string you're looking for:

SELECT X'27' || '%' || cd_ind || '%' || X'27'
FROM udbadm.tab_dtl

This solves one problem, but gives you another. The DB2 documentation for the LIKE predicate says that the left-hand side of the LIKE can be:

# a constant
# a special register
# a host variable (including a locator variable or a file reference variable)
# a scalar function
# a large object locator
# a column name
# an expression concatenating any of the above

But to the right of the LIKE you can only have:

# a constant
# a special register
# a host variable
# a scalar function whose operands are any of the above
# an expression concatenating any of the above

Do you notice what's missing from the second list? I'll give you a hint. It's what you're trying to use: A column name. I've never had to use a query like you're trying to do so I'm not sure how to solve it. Maybe using a SCALAR function of some kind will cast the column into something that LIKE will accept. If you get it to work, let us know for future reference.

I had a situation like that where a policy number was stored
on one table, and on another table it was stored with an extra digit (some type of suffix code...)

They corrected the problem, but was able to join using this method:

AND LTRIM(RTRIM(A.POL_NBR_TXT))= SUBSTR(E.BNFT_NBR_TXT,1,LENGTH(RTRIM(LTRIM(A.POL_NBR_TXT))))

not sure that this would work in all instances...

fv