Re: Recursive SQL help needed

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.