Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2007
    Posts
    72

    Unanswered: what is SQL20148N error telling me. Can someone explain? How can this be resolve?

    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
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by pagwu
    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?
    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.

    (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?
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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