Re: Recursive SQL help needed

THANKS Charles

RETURN ifnull(OUT_IPNNBR, IN_IPNNBR) did the trick
(I should have thought of it myself)

The compiler do not like the DECLARE OUT_IPNNBR CHAR(11) default(IN_IPNNBR)
statement.

The Function works great !
I may try to create a view (or index ?) so RPG programs can "enjoy" the
same functionality.

thanks again
Gad

date: Thu, 7 Mar 2024 08:42:25 -0700
from: Charles Wilt <charles.wilt@xxxxxxxxx>
subject: Re: Recursive SQL help needed

Another quick and easy fix
DECLARE OUT_IPNNBR CHAR(11) default(IN_IPNNBR);

Charles

On Thu, Mar 7, 2024 at 8:41?AM Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

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.