I don't understand what SQL20148N error is telling me. Can someone explain? Also, how to resolve it without changing much of the code and UDF function?

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

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!

So, can one Hall Of Famer come to my rescue? I'd be very appreciative.

Thanks




SQL20148N.txt
EnudaAsked:
Who is Participating?
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:

Hi Enuda,

This is more a DB2 issue than linux, but here goes.

The error is a silly one, as the parser is enforcing a particular coding style.

CREATE FUNCTION ...
...
  IF (somecondition)
    RETURN;
  END IF;
  SELECT ...;
END

The code above is illegal.  The RETURN statement is not the last line of the function.  You'll need to rewrite the function so that the last line is RETURN, and so that there is only one RETURN statement in the function.


Kent
0
 
momi_sabagConnect With a Mentor Commented:
hi
what happens if you mark out line 56 in your code ? (the first return in the third function) ?
does it compile ?
since you only use that return 0 once in the begining of the code,
i'd just change the way the function work
instead of having

IF WordLength < 1 THEN
RETURN 0;
END IF;

rest of function

why not coding

IF WordLength >= 1 THEN
 all your function comes here
END IF;

return 0

you won't get your error this way, and it's also better coding standards to have only one return statement in your functions


0
All Courses

From novice to tech pro — start learning today.