RE: Idea to use UDF for a running total function ? -- RPG400-L

I think you're going to need to use the SQL scratchpad

scratchpad 
This argument is set by DB2 before calling the UDF. It is only present if
the CREATE FUNCTION statement for the UDF specified the SCRATCHPAD keyword.
This argument is a structure with the following elements: 
An INTEGER containing the length of the scratchpad. 
The actual scratchpad, initialized to all binary 0's by DB2 before the first
call to the UDF. 
The scratchpad can be used by the UDF either as working storage or as
persistent storage, since it is maintained across UDF invocations. 


http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/sqlp/rbafymst257.htm

Example:
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/sqlp/rbafymst260.htm


HTH,
Charles




> -----Original Message-----
> From: Corteville, Thierry [mailto:Thierry.Corteville@xxxxxxxxx]
> Sent: Monday, February 09, 2004 12:15 PM
> To: 'rpg400-l@xxxxxxxxxxxx'
> Subject: Idea to use UDF for a running total function ?
> 
> 
> Hi, 
> i'm testing the use of an UDF to create a running total.
>  
> The idea was to everytime pass keep the running total value 
> in memory of the
> udf-program.
>  
>  
> Everything works fine the first time an sql-statement is launched...
>  
> select nr, testudf(nr)
> from testfile                     
>  
> nr              runn. total
>     1              1,00000 
>     2              3,00000 
>  1000          1.003,00000 
>  1001          2.004,00000 
>  1002          3.006,00000 
> ...
>  
> if i try to launch the same sql a 2nd time .. i receive a Decimal data
> error. 
>  
> Bericht-ID . . . . . . :   MCH1202       Severity . . . . . . 
> . :   40      
> Datum gezonden . . . . :   09/02/04      Tijdstip gezonden  . 
> . :   17:44:44
> Type bericht . . . . . :   Afbreken                           
>               
> CCSID  . . . . . . . . :   65535         Thread . . . . . . . 
> . :   0000003D
>                                                               
>               
> Gezonden door programma  . . . :   TESTUDF                    
>               
>   Van bibliotheek  . . . . . . :     XTCO                     
>               
>   Van module . . . . . . . . . :     TESTUDF                  
>               
>   Van procedure  . . . . . . . :     TESTUDF                  
>               
>   Van instructie . . . . . . . :     40                       
>               
>                                                               
>               
> Gezonden naar programma  . . . :   TESTUDF                    
>               
>   Naar bibliotheek . . . . . . :     XTCO                     
>               
>   Naar module  . . . . . . . . :     TESTUDF                  
>               
>   Naar procedure . . . . . . . :     TESTUDF                  
>               
>   Naar instructie  . . . . . . :     40                       
>               
>  
>  
> If i now launch the same sql-statement the third time,
> the statement returns correctly the asked information
>  
>  
> What can i do to avoid this problem....
>  
> =====================================================
> the udf looks simply like that 
>  
>  create function Testudf
>   (value_in decimal(15, 5))             
>   returns Decimal(15, 5)              
>   external name testudf               
>  returns NULL on NULL input           
>  language RPGLE                       
>  NO sql                               
>  Parameter style SQL                  
>  
> the Udf in RPG look like ... 
>  
> ...*ENTRY Plist 
> ..
> c/free                             
>     Value_return += Value_in;           
>     return;                        
>  /end-free                         
>  
> C     *INZSR        BEGSR        
>  /free                           
>       Value_return = 0;              
>  /end-free                       
> C*
> 
>  
> 
>  
> 
> Thierry Corteville 
> IT- Manager 
> Quadrant CMS ERTA NV 
> * +32 (0) 51 423 255 
> * +32 (0) 41 423 470 
> www.quadrantcms.com 
> 
>  
> 
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries (RPG400-L) 
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>