Re: Recursive SQL help needed

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.