Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Unanswered: Like predicate in UDF

    Hi!

    I am trying to create following UDF in DB2 v7.1:

    CREATE FUNCTION LASTPOLNR(POL CHAR(20))
    RETURNS CHAR(20)

    RETURN

    SELECT A211.POLNR FROM A211 WHERE A211.POLNR LIKE LASTPOLNR.POL || '%';

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

    My question is: Is there any way to use function parameter in like predicate?

    Thank you all!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think if you rewrite the UDF like this, it might work:

    CREATE FUNCTION LASTPOLNR(POL CHAR(20))
    RETURNS CHAR(20)
    language SQL NOT deterministic NO EXTERNAL ACTION READS SQL DATA CALLED ON NULL INPUT
    BEGIN ATOMIC
    DECLARE value char(20)
    DECLARE astring varchar(30);

    SET astring = pol || '%';

    SET (value) = (SELECT A211.POLNR FROM A211 WHERE A211.POLNR LIKE astring);
    RETURN value;
    END @

    HTH

    Andy

Posting Permissions

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