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!

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