× 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.



It might seem like this should work,
select ifnull(IPNNEW, IPNNBR, IN_IPNNBR ) into OUT_IPNNBR

But an itch in my mind is telling me it won't since there's no rows at all
in the results set.

You could modify your recursive function ...

But I'd probably just do
RETURN ifnull(OUT_IPNNBR, IN_IPNNBR);

HTH,
Charles

On Thu, Mar 7, 2024 at 6:20 AM Gad Miron <gadmiron@xxxxxxxxx> wrote:

Hello Sages

I need some help with a recursive SQL function, here goes:

With some perseverance ,
(and with a lot of help from Dr. Google, especially here
https://www.itjungle.com/2006/06/12/fhg071206-story02/
Thanks Michael)

I've managed to create a recursive SQL function (see code following)

The function receives an historic "customer number" (IPNNBR), loops
recursively through
a table (IPNDELF) that records changes to "customer number" (IPNNBR -->
IPNNEW)
and returns the most current "customer number".
The A/M number may change several times, for instance, PX123 was changed to
5445 and then 5445 was changed to 00888 .
So that if the input is PX123 the output should be 00888.

If the "customer number" has never been changed (no record in table
IPNDELF)
then the function should return the input "customer number" argument
(IN_IPNNBR).

Now to the issue I need help with :

The function works fine in all cases where a "customer number" has been
changed ,
but in case the "customer number" has never been changed (no record in
IPNDELF),
the function returns null instead of the input argument.

Having stared at and tweaked the code for quite some time
I have not found a solution.

Can any of you guys help me spot the bug?

TIA
Gad

CREATE OR REPLACE FUNCTION IPILIB/GETIPI1 (IN_IPNNBR CHAR(11))
RETURNS CHAR(11) LANGUAGE SQL DETERMINISTIC
BEGIN
DECLARE OUT_IPNNBR CHAR(11) ;
With Recursive IPN_Replaced (Level,IPNNBR, IPNNEW) as
(Select 1 as Level,IPNNBR, IPNNEW
from IPILIBD/IPNDELF
where IPNNBR = IN_IPNNBR
union all
select Level+1 as Level, ir.IPNNBR, ir.IPNNEW
from IPN_Replaced ih, IPILIBD/IPNDELF ir
where ih.IPNNEW = ir.IPNNBR
)
select ifnull(IPNNEW, IPNNBR) into OUT_IPNNBR
from IPN_Replaced
order by Level desc
fetch first 1 row only ;
RETURN OUT_IPNNBR ;
END
--
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@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
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.