CODEWITHSUNDEEP
sqlrecursiondb2
Joe Jess
Joe Jess

Reputation: 3

DB2 recursive UDF Table

I am having problem writing a recursive table function in DB2.

I have a table of values.... 

With t (Adjustment) as (
VALUES (100)
    , (200)
    , (300)
) select * from t;

I need to pass to a UDF Table function an opening value (say 5000) and a factor (say 0.1)

I need to show a result as follows....

Opening Shift   Adjustment  Closing
(3)     (1)     (2)
==================================================
5000    500     100     5600            
5600    560     200     6360
6360    636     300     7296
  1. Shift = Opening * 0.1
  2. Closing = Opening + Shift + Closing
  3. Opening is the closing of the previous row

My recursive function gets stuck when I tried to bring the brought forward ...(SQL0345N The fullselect of the recursive common table expression)

Appreciate any idea how to do this. I am aware the Stored Procedure can do this but I need to use a UDF so that it can be extended in another UDF.

Upvotes: 0

Views: 406

Answers (1)

CRPence
CRPence

Reputation: 1259

I am unsure if I interpreted the scenario properly, but here is what I did:

To start, I will create a couple variables to mimic input to a function, and then compose a Recursive Common Table Expression (RCTE) to generate the report using data obtained from those variables:

 create variable OpenedWith decimal     default 5000    
 ;
 create variable Factor     decimal(2, 1) default 0.1   
 ;



 with                                                           
   t (adjustment) as ( values(100), (200), (300) )              
 , ordRows (rn, Adjustment ) as                                 
    ( select row_number() over(), Adjustment from t )           
 , addRows (rn, Opening, Shift, Adjustment, closing ) as        
    ( select rn, OpenedWith, OpenedWith*Factor , Adjustment     
           , ( OpenedWith + ( OpenedWith*Factor ) + Adjustment )
      from ordRows                                              
      where rn = 1                                              
    union all                                                   
      select b.rn, a.Closing, a.Closing * Factor , b.Adjustment 
           , ( a.Closing + (a.Closing * Factor) + b.Adjustment )
      from addRows a                                            
      join ordRows b                                            
        on a.rn = ( b.rn - 1 )                                  
    )                                                           
 select int(rn) as rn, int(opening) as opening
      , int(shift) as shift, adjustment       
      , int(closing) as closing               
 from addRows


The following is the report from the above query:

 RN         OPENING           SHIFT      ADJUSTMENT         CLOSING
  1           5,000             500             100           5,600
  2           5,600             560             200           6,360
  3           6,360             636             300           7,296

And now to modify the above scripted variable creations and query into a User Defined Table Function (UDTF) that operates against the data in TABLE named T:

 create function shifted_vals  
  ( OpenedWith decimal( 5 )    
  , Factor     decimal( 3, 2)  
  )                            
  returns table                
  ( Opening      int           
  , Shift        int           
  , Adjustment   int           
  , Closing      int           
  )                            
 return                        
 with                          
   ordRows (rn, Adjustment ) as
    ( select row_number() over(), Adjustment from t )           
 , addRows (rn, Opening, Shift, Adjustment, closing ) as        
    ( select rn, OpenedWith, OpenedWith*Factor , Adjustment     
           , ( OpenedWith + ( OpenedWith*Factor ) + Adjustment )
      from ordRows                                              
      where rn = 1                                              
    union all                                                   
      select b.rn, a.Closing, a.Closing * Factor , b.Adjustment 
           , ( a.Closing + (a.Closing * Factor) + b.Adjustment )
      from addRows a                                            
      join ordRows b                                            
        on a.rn = ( b.rn - 1 )                                  
    )                                                           
 select opening, shift, adjustment, closing                     
 from addRows                                                   
 order by rn


Now invoke the UDTF with the noted opening value and factor as arguments; i.e. no longer depending on created variables, instead values obtained via the input parameters:

select t.*                              
from table(shifted_vals(5000, 0.1)) as t
; -- results as report, follows:
  OPENING           SHIFT      ADJUSTMENT         CLOSING
    5,000             500             100           5,600
    5,600             560             200           6,360
    6,360             636             300           7,296

Upvotes: 1

Related Questions