Hi all,

I seem to be making progress with my UDF task. But I do have what I think may be a more difficult and perhaps the last hurdle. I am getting a SQL20148N error which states as follows:

SQL20148N Routine "DB2INST1.FNDOUBLEMETAPHONETABLE" with specific name "FNDOUBLEMETAPHONETABLE" must have the RETURN statement as the last SQL statement of the compound body. LINE NUMBER=22.
SQLSTATE=429DB (please see attachment)

Explanation: The RETURN statement must be the last SQL statement of the compound body in an SQL ROW or TABLE function. No other RETURN statement is allowed within the routine body.

A look at the UDF code will show that I have three CREATE function statements and their corrresponding returns. The first two creates are used to evaluate alphabet characters A thru Z. The third create is used to store derived string values in a phonectic search. I am trying to understand what the error message is telling me.My questions:

(1) Is the error message stating that there can only be one create function statement in a ROW or Table function? Or only one RETURN even if there are more than one create UDF function?

(2) For this question, please be kind enough to look at the attached file just to get a picture of what I am trying to accomplish. So, my question is, what are some of the way that I can resolve this error and keep the general pattern of the code logic and process?

If I had all the time in the world, I'd probably change from table function and have the UDF return a character string but that may defeat the purpose of the UDF - phonetic search (better know as DoubleMetaphone) and I'm rather pressed to get this in pronto!

I'd be very appreciative for any all help I can get on this?.

Thanks

I think the wording of the message text is very specific already. It says that a table function (as in your case because you said that the function shall return a table with two columns) must have exactly one RETURN statement, and that must be placed at the end of the compound statement. You have two RETURN statements in the function body (and none of them seems to return a table).

p.s: ROW functions are only applicable to transform functions for structured types.

The real question is what your function shall return. You can't return an integer if the function is to be created with "RETURNS TABLE ( Metaphone1 CHAR(4), Metaphone CHAR(4) )". You never set the output parameters. Also, your table function inserts something in a table named DMP - what is this for?

I suggest that you have a look here on how to work with relations: http://www.ibm.com/developerworks/db...03stolze1.html I parse strings into pieces with pure relational means (and no procedural logic).

p.s: You should use some indentation to make the code readable.