Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2009
    Location
    Munich, Germany
    Posts
    3

    Unanswered: Usage of LIKE with content from another cell

    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

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    it should be
    col LIKE a.vermfunk concat '%' instead of
    LIKE concat(a.vermfunk,'%')
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Is not allowed in DB2 LUW .. Zos i cant say..
    I faced many such codes while converting Sybase to DB2

    Play with locate function
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  6. #6
    Join Date
    Sep 2009
    Location
    Munich, Germany
    Posts
    3
    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

  7. #7
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    <>0
    i guess it will behave %abc% instead of abc%
    =1 will be gud
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  8. #8
    Join Date
    Sep 2009
    Location
    Munich, Germany
    Posts
    3
    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

Posting Permissions

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