Idea to use UDF for a running total function ?

["Corteville, Thierry" <Thierry.Corteville@xxxxxxxxx>]

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