Using SQL within UDF SQLRPGLE Program

[400Dog]

Will preface this is my first time trying to create a UDF. I created the function. The function is getting executed from the SQL statement correctly. Then I realized the value I was calculating in the function needed a set of non-workday hours removed from the sum. I thought, why not use another SQL statement inside the function to sum the non-workday hours and subtract from the total hours. If I call the program used in the function from a command line, it works. When the program runs from an interactive sql session, the sql in the function does not work.

Here is the code to create the function. Program ZMR175 is a *PGM RPGLE object, not a service program. Does that matter? Or is it not possible to use SQL in the external program? As far as some of the function parameters, it didn't matter which Deterministic parm I used, same results.

Code: Select all

CREATE OR REPLACE FUNCTION TOTALHOURS (PMIFAC VARCHAR(3), PMIBDATE TIMESTAMP, PMIEDATE TIMESTAMP)
RETURNS DECIMAL(7,2)
EXTERNAL NAME 'QGPL/ZMR175'
SPECIFIC TOTALHOURS
LANGUAGE RPGLE
STATEMENT DETERMINISTIC
READS SQL DATA
PARAMETER STYLE SQL
PROGRAM TYPE MAIN
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION

If I can use this small piece of SQL in the external program, the function perfectly meets my needs; otherwise, I will have to resort to putting it all in an RPG batch program and create a work file. Any feedback is appreciated.

[400Dog]

Found a solution using InLine SQL Function as follows:

Code: Select all

Create or Replace Function NONWORKHRS(
          facility VarChar(3)
         ,pmiBDate Date
         ,pmiEDate Date
       )
       returns Decimal(7, 2)
       language sql
       specific NONWORKHRS
       not deterministic
       not fenced
       reads sql data
       returns null on null input
Set Option DBGVIEW = *SOURCE, OUTPUT = *PRINT
A1: BEGIN ATOMIC
  DECLARE NONWORKDAYHRS DEC(7, 2);
  DECLARE pmiBDate8 Dec(8, 0);
  DECLARE pmiEDate8 Dec(8, 0);
  Set NONWORKDAYHRS = 0;
  Select cast(pmiBdate as Decimal(8,0)) into pmiBDate8 from sysibm.sysdummy1;
  Select cast(pmiEDate as Decimal(8,0)) into pmiEDate8 from sysibm.sysdummy1;
  Select sum(shhours)                         
    Into NONWORKDAYHRS                   
    From LIBDAT.CALHOURS                        
   Where SHFAC = trim(facility)                   
     And SHDATE Between pmiBdate8 and pmiEdate8   
     And SHTYPE in ('H','W');    
  If NONWORKDAYHRS is null
  Then Set NONWORKDAYHRS = 0;
  End If;
                 
  Return NONWORKDAYHRS;
  End;
 

[Scott Klement]

You didn't tell us what problem you're having, only that it "didn't work."