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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
momi_sabagCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.