midrange.com code scratchpad
Name:
Peter Dow
Scriptlanguage:
Plain Text
Tabwidth:
4
Date:
11/11/2013 06:59:40 pm
IP:
Logged
Description:
User Defined Table Function sample that only gets called on Open and Fetch.
Code:
  1. UDTF1
  2. =====
  3. 0001.00 H THREAD(*SERIALIZE) BNDDIR('QC2LE')                    
  4. 0002.00                                                         
  5. 0003.00 D UDTF1           pr                  extpgm('UDTF1')   
  6. 0004.00 D  parmIn                       11p 0                   
  7. 0005.00 D  ParmOut                       5p 0                   
  8. 0006.00 D  n_parmIn                      5i 0                   
  9. 0007.00 D  n_parmOut                     5i 0                   
  10. 0008.00 D  SQLSTT                        5a                     
  11. 0009.00 D  Function                    517a   varying const     
  12. 0010.00 D  Specific                    128a   varying const     
  13. 0011.00 D  MsgText                      70a   varying           
  14. 0012.00 D  CallType                      5i 0 const             
  15. 0013.00                                                         
  16. 0014.00 D UDTF1           pi                                    
  17. 0015.00 D  parmIn                       11p 0                   
  18. 0016.00 D  ParmOut                       5p 0                   
  19. 0017.00 D  n_parmIn                      5i 0                   
  20. 0018.00 D  n_parmOut                     5i 0                   
  21. 0019.00 D  SQLSTT                        5a                     
  22. 0020.00 D  Function                    517a   varying const 
  23. 0021.00 D  Specific                    128a   varying const 
  24. 0022.00 D  MsgText                      70a   varying       
  25. 0023.00 D  CallType                      5i 0 const         
  26. 0024.00                                                     
  27. 0025.00 D Counter         s             10i 0               
  28. 0026.00                                                     
  29. 0027.00 D CALL_STARTUP    C                   CONST(-2)     
  30. 0028.00 D CALL_OPEN       C                   CONST(-1)     
  31. 0029.00 D CALL_FETCH      C                   CONST(0)      
  32. 0030.00 D CALL_CLOSE      C                   CONST(1)      
  33. 0031.00 D CALL_FINAL      C                   CONST(2)      
  34. 0032.00                                                     
  35. 0033.00 D PARM_NULL       C                   CONST(-1)     
  36. 0034.00 D PARM_NOTNULL    C                   CONST(0)      
  37. 0035.00                                                     
  38. 0036.00  /free                                              
  39. 0037.00      n_parmIn  = PARM_NULL;                         
  40. 0038.00      n_ParmOut = PARM_NULL;                         
  41. 0039.00                                                     
  42. 0040.00      Counter += 1;                                                     
  43. 0041.00                                                                        
  44. 0042.00      select;                                                           
  45. 0043.00                                                                        
  46. 0044.00      when  CallType = CALL_STARTUP;                                    
  47. 0045.00        dsply ('STARTUP: ' + %editc(CallType:'L') + ' ' + SQLSTT + ' '  
  48. 0046.00                           + %editc(Counter:'L') + ' '                  
  49. 0047.00                           + %editc(parmIn:'L'));                       
  50. 0048.00                                                                        
  51. 0049.00      when  CallType = CALL_OPEN;                                       
  52. 0050.00        dsply ('OPEN...: ' + %editc(CallType:'L') + ' ' + SQLSTT + ' '  
  53. 0051.00                           + %editc(Counter:'L') + ' '                  
  54. 0052.00                           + %editc(parmIn:'L'));                       
  55. 0053.00                                                                        
  56. 0054.00      when  CallType = CALL_FETCH;                                      
  57. 0055.00        dsply ('FETCH..: ' + %editc(CallType:'L') + ' ' + SQLSTT + ' '  
  58. 0056.00                           + %editc(Counter:'L') + ' '                  
  59. 0057.00                           + %editc(parmIn:'L'));                       
  60. 0058.00        parmOut = Counter;                                              
  61. 0059.00        n_parmOut = PARM_NOTNULL;                                       
  62. 0060.00        if Counter>5;                                                  
  63. 0061.00            SQLSTT = '02000';                                          
  64. 0062.00            return;                                                    
  65. 0063.00        endif;                                                         
  66. 0064.00                                                                       
  67. 0065.00      when  CallType = CALL_CLOSE;                                     
  68. 0066.00        dsply ('CLOSE..: ' + %editc(CallType:'L') + ' ' + SQLSTT + ' ' 
  69. 0067.00                           + %editc(Counter:'L') + ' '                 
  70. 0068.00                           + %editc(parmIn:'L'));                      
  71. 0069.00        *inLR = *on;                                                   
  72. 0070.00                                                                       
  73. 0071.00      when  CallType = CALL_FINAL;                                     
  74. 0072.00        dsply ('FINAL..: ' + %editc(CallType:'L') + ' ' + SQLSTT + ' ' 
  75. 0073.00                           + %editc(Counter:'L') + ' '                 
  76. 0074.00                           + %editc(parmIn:'L'));                      
  77. 0075.00                                                                       
  78. 0076.00      other;                                                           
  79. 0077.00        dsply ('OTHER..: ' + %editc(CallType:'L') + ' ' + SQLSTT + ' ' 
  80. 0078.00                           + %editc(Counter:'L') + ' '                 
  81. 0079.00                           + %editc(parmIn:'L'));                      
  82. 0080.00             
  83. 0081.00      endsl; 
  84. 0082.00             
  85. 0083.00      return;
  86. 0084.00  /end-free  
  87.  
  88.  
  89. UDTF1S
  90. =====
  91.   DROP FUNCTION UDTF1( decimal(11,0) );                
  92. CREATE FUNCTION UDTF1( parmIn decimal(11,0) )          
  93.                         RETURNS TABLE                  
  94.                         (                              
  95.                           parmOut     decimal(5,0)     
  96.                         )                              
  97.                   EXTERNAL NAME 'HS#LIBR/UDTF1(UDTF1)' 
  98.                   LANGUAGE RPGLE                       
  99.                   PARAMETER STYLE DB2SQL               
  100.                   NO SQL                               
  101.                   NOT DETERMINISTIC                    
  102.                   NO EXTERNAL ACTION                   
  103.                   NOT FENCED                           
  104.                   NO SCRATCHPAD                        
  105.                   DISALLOW PARALLEL                    
  106.                   CARDINALITY 1;                       
  107.  
© 2004-2019 by midrange.com generated in 0.006s valid xhtml & css