Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Pattern Match in a 'like' clause

    My cursor's Select query has the following like clause in its WHERE clause, but the procedure failed to compile, because of this like clause.

    Code:
    SELECT table1.colid,  col3 	FROM   table1, table3
    	WHERE    table1.colid like 'table3.colid'
    	ORDER BY table1.colid;
    here the colid of both the table are of type varchar(2)

    But DB2 allows only string constants and not a column value in the pattern match of a like clause.
    (ie. the above query works fine if the WHERE clause is, table1.colid like 'aa')

    could any one suggest me on how to use the column value here in the pattern match of the like clause..

    Regards
    Sn

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,415
    Try LOCATE() or SUBSTR() functions

  3. #3
    Join Date
    Apr 2005
    Posts
    127
    Quote Originally Posted by n_i
    Try LOCATE() or SUBSTR() functions

    i dint find SUBSTRING() in DB2 .. and i think LOCATE will not replace the table column as the exact equivalent.. kindly explain me with an example if i am wrong.

    thanks
    Sn

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    might be something like this works:

    SELECT table1.colid, col3
    FROM table1, table3

    WHERE SUBSTR(table3.colid,1,1) in (SUBSTR(table1.colid,1,1),'_','%')
    AND SUBSTR(table3.colid,2,1) in (SUBSTR(table1.colid,2,1),'_','%')

    ORDER BY table1.colid;

  5. #5
    Join Date
    Apr 2005
    Posts
    127
    I tried your suggestion with SUBSTR umayer, but the query fails in execution throwing the following exeception :

    No authorized routine named 'SUBSTR' of type 'FUNCTION' having compatible arguments is found.
    SQL State : 42884


    Pls note that my DB 2 version is 8.1.3 and it runs on Windows 2000

    thanks
    Sn

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    If you just want to match the two COLID columns, and both are VARCHAR(2), what's wrong with
    Code:
    SELECT table1.colid,  col3
    FROM   table1, table3
    WHERE  table1.colid = table3.colid
    ORDER BY table1.colid;
    (or am I misunderstanding your problem)?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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