Usage of LIKE with content from another cell

Flamacue · #1 (**permalink**) 09-07-09, 04:58

DB2-Version: v8.1 on z/os

Hi everyone,

I am trying to use the LIKE operator in the following way:

Code:

  SELECT                                                             
      *                             
  FROM                                                               
      GG$TUK6.TVERMITTLER_DEF a,                                     
      GG$TUK6.TDE1ASAB02AREGART b                                     
  WHERE                                                              
      a.ADCODE_ALT = '73320940'                                    
      and b.DE1_VERMFUNK_LIST LIKE concat(a.vermfunk,'%')

I receive the following error:
SQL0132N
A LIKE predicate or POSSTR scalar function is not valid because the first operand is not a string expression or the second operand is not a string.

Both operands are strings, so I don't see where the problem is.

Any ideas? Is this possible with DB2 at all?

Thanks,
Christian

przytula_guy · #2 (**permalink**) 09-07-09, 05:35

it should be
col LIKE a.vermfunk concat '%' instead of
LIKE concat(a.vermfunk,'%')

rahul_s80 · #3 (**permalink**) 09-07-09, 05:53

Is not allowed in DB2 LUW .. Zos i cant say..
I faced many such codes while converting Sybase to DB2

Play with locate function

stolze · #4 (**permalink**) 09-07-09, 06:01

DB2 requires a constant expression. Using the value from another column is not constant. Depending on what you want to achieve, consider the LOCATE() or POSSTR() functions.

Here is the manual where this is explicitly stated: http://publib.boulder.ibm.com/infoce...epredicate.htm

tonkuma · #5 (**permalink**) 09-07-09, 07:25

This is one of the restriction of DB2.
A column name(and an expression(concatenation, etc.) including column name) can't be used for the pattern-expression of LIKE predicate.

Even newest DB2 version(DB2 9.7 for LUW) still has this restriction.

I discussed this issue in the following thread.
Compatibility of LIKE predicate on DB2 V9.7 with Oracle.:
comp.databases.ibm-db2 | Google Groups

For your case:
"and b.DE1_VERMFUNK_LIST LIKE concat(a.vermfunk,'%')"
will be replaced by:
"and LOCATE(a.vermfunk, b.DE1_VERMFUNK_LIST) = 1"
as Rahul Singh wrote.

Flamacue · #6 (**permalink**) 09-07-09, 09:08

I changed the line to:
and LOCATE(a.vermfunk, b.DE1_VERMFUNK_LIST) <> 0

This seems to be doing what I want.

Thanks everyone,
Christian

rahul_s80 · #7 (**permalink**) 09-07-09, 09:32

<>0
i guess it will behave %abc% instead of abc%
=1 will be gud

Flamacue · #8 (**permalink**) 09-07-09, 10:00

Good point, Rahul! But indeed, %abc% was what I really needed. My original example just didn't reflect that. Sorry for the confusion.

Thanks,
Christian

Internet Services

Web Development

Digital Marketing

Consumer Tech