SQL UDF over RPGLE procedure

[gwilburn]

I created an RPG procedure in a service pgm to return a decimal value. No input parameters are required. When I call it from an RPG program it works perfectly.

       dcl-proc  tbf_ShiftHrsRem       Export;

         dcl-pi tbf_ShiftHrsRem        zoned(5:2);
         end-pi;


         dcl-c  endofshift             t'17.00.00';
         dcl-c  endofday               t'23.59.59';
         dcl-s  seconds                int(10);
         dcl-s  ouHrs                  zoned(5:2);


           if %time < endofshift;
             seconds = %diff(endofshift:%time:*seconds);
           else;
             seconds = %diff(endofday:%time:*seconds);
           endif;

           ouHrs = seconds/3600;

           return ouHrs;

       end-proc;

I want to use this in an SQL view. So i created a UDF

CREATE FUNCTION ASTHHOBJ.TBF_SHIFTHRSREM ( ) 
    RETURNS DECIMAL(5, 2)   
    LANGUAGE RPGLE 
    SPECIFIC ASTHHOBJ.TBF_SHIFTHRSREM 
    NOT DETERMINISTIC 
    NO SQL 
    CALLED ON NULL INPUT 
    EXTERNAL NAME 'HHPLIB/TBFUTILR4(TBF_SHIFTHRSREM)' 
    PARAMETER STYLE GENERAL ;

When I call this, I receive a decimal data error...
SELECT TBF_SHIFTHRSRM() FROM SYSIBM.SYSDUMMY1

Message ID . . . . . . : CPF503E Severity . . . . . . . : 30
Message type . . . . . : Diagnostic
Date sent . . . . . . : 03/23/18 Time sent . . . . . . : 16:49:35

Message . . . . : User-defined function error on member SYSDUMMY1.
Cause . . . . . : An error occurred while invoking user-defined function
TBF_SHIFTHRSREM in library ASTHHOBJ. The error occurred while invoking the
associated external program or service program TBFUTILR4 in library HHPLIB,
program entry point or external name TBF_SHIFTHRSREM, specific name
TBF_SHIFTHRSREM. The error occurred on member SYSDUMMY1 file SYSDUMMY1 in
library SYSIBM. The error code is 2. The error codes and their meanings
follow:
1 -- The external program or service program returned SQLSTATE 00000. The
text message returned from the program is: .
2 -- The external program failed before it completed.
3 -- The database timed out waiting for the program to return. The timeout

[wegrace]

Try returning numeric instead of decimal

eg. RETURNS numeric(5, 2)

[gwilburn]

Try returning numeric instead of decimal

eg. RETURNS numeric(5, 2)

Thanks! That did it.

[wegrace]

You're welcome.