× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



yeah, I would strip the function to basics, eliminate the regexp, get rid
of the fenced and parallel stuff. Get something that works and then work
your way back to coding the function the way you want, looking for the
error to reappear.

CREATE OR REPLACE FUNCTION getMiddleName (
inFullName char(256))
RETURNS char(30)
language sql
BEGIN

declare fx int default 0 ;
declare ix int default 0 ;
declare vSqlCode decimal(5,0) ;
declare sqlCode int ;
declare wk varchar(256) default '' ;
declare vFirstName varchar(256) default '' ;
declare vMiddleName varchar(256) default '' ;
declare vLastName varchar(256) default '' ;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET VSQLCODE = SQLCODE ;

set wk = trim(inFullName) ;
set fx = locate(' ', wk ) ;
if fx > 0 then
set vFirstName = substr(wk,1,fx-1) ;
set wk = trim(substr(wk,fx)) ;
else
set vFirstName = wk ;
set wk = '' ;
end if;

/* something after first name. check for middle name or last name. */
if wk <> '' then
set fx = locate(' ', wk ) ;
if fx > 0 then
set vMiddleName = substr(wk,1,fx-1) ;
set vLastName = trim(substr(wk,fx)) ;
else
set vLastName = wk ;
end if;
end if ;

return vMiddleName ;

END


On Fri, Mar 27, 2020 at 9:13 AM Jay Vaughn <jeffersonvaughn@xxxxxxxxx>
wrote:

so i have a udf that takes a string and parses out firs middle last name...

it uses substring, regexp_count, and locate....

ddl is...

language sql
allow parallel
not fenced
returns null on null input
MODIFIES sql data

when i call it from a loop feeding it source strings it will work for some
calls then just stop working. the job log shows...

Comparison of selection operands may never be equal.
Comparison of selection operands may never be equal.
Tried to refer to all or part of an object that no longer exists.
Directory created.
Function error X'1720' in machine instruction. Internal dump identifier
(ID) 01000E00.
Failure for device or member MCH0007 file SYSDUMMY1 in library SYSIBM.
Resource limit exceeded.
User-defined function error on member QSQPTABL.
User-defined function error on member QSQPTABL.
Resource limit exceeded.


hoping i'm simply missing something in my ddl.

does anything pop out? I can provide the code if necessary...

tia

jay
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.