Using SQL UDF in SQLRPGLE program gets decimal data error

["Peter Dow" <maillist@xxxxxxxxxxxxxxx>]

Hi All,

I'm getting a decimal data error in an SQLRPGLE program that does a PREPARE:

c/exec SQL
c+ PREPARE SQLstmt FROM :@SELECT
c/end-exec

and in my problem situation, @SELECT is

SELECT * FROM ERPLOG WHERE ELDATE BETWEEN 20020601 AND 20020631 AND
ELAPSEDHOURS(ELDATE,ELTIMI,ELDATE,ELTIMO) < 24

Further down in the program, it gets the decimal data error on the
following:

c                   eval      Hours = ElapsedHours(
c                                        ELDATE: ELTIMI:
c                                        ELDATE: ELTIMO)

where Hours is

D Hours           s             10i 0

Note that this is *after* it has successfully selected the record using the
exact same UDF.  The values of the fields as seen in the dump are:

ELDATE              ZONED(8,0)           20020601.
'F2F0F0F2F0F6F0F1'X
ELTIMI              ZONED(4,0)           1847.            'F1F8F4F7'X
ELTIMO              ZONED(4,0)           2100.            'F2F1F0F0'X

The UDF is in a service program and looks like this:

P ElapsedHours    B                   export
D ElapsedHours    PI            10i 0
D   FromDate                          like(ELDATE) const
D   FromTime                          like(ELTIMI) const
D   ThruDate                          like(ELDATE) const
D   ThruTime                          like(ELTIMO) const
D FromTS          s               z
D Hours           s             10i 0
D ThruTS          s               z
 ****** The following stmt is where it gets the decimal data error *******
c                   eval      FromTS = TimeStamp(FromDate: FromTime)
c                   eval      ThruTS = TimeStamp(ThruDate: ThruTime)
c                   if        FromDate = ThruDate and
c                             ThruTime < FromTime
c                   adddur    24:*H         ThruTS
c                   endif
c     ThruTS        subdur    FromTS        Hours:*H
c                   return    Hours
P                 E

and the UDF was created with the following SQL:

CREATE FUNCTION ElapsedHours (DEC(8,0), DEC(4,0), DEC(8,0), DEC(4,0))
   RETURNS INTEGER
   RETURNS NULL ON NULL INPUT
   PARAMETER STYLE GENERAL
   DETERMINISTIC
   NO EXTERNAL ACTION
   NO SQL
   DISALLOW PARALLEL
   LANGUAGE RPGLE
   EXTERNAL NAME 'HS#LIBR/ER0100S(ELAPSEDHOURS)'

If I run the SQL SELECT shown above interactively, the UDF works just fine.

The TimeStamp procedure is in the same service program and looks like this:

P TimeStamp       B                   export

D TimeStamp       PI              z
D   Date                         8s 0 const
D   Time                         4s 0 const

D DateTime        s               z
D                 ds
D  DateCCYY                      4s 0
D  DateMM                        2s 0
D  DateDD                        2s 0
D DateDS                  1      8s 0
D                 ds
D  TimeHH                        2s 0
D  TimeMM                        2s 0
D TimeDS                  1      4s 0
D                 ds
D TempDateTime                  26a   inz('CCYY-MM-DD-HH.NN.00.000000')
D  TempCCYY                      4s 0 overlay(TempDateTime:1)
D  TempMM                        2s 0 overlay(TempDateTime:6)
D  TempDD                        2s 0 overlay(TempDateTime:9)
D  TempHH                        2s 0 overlay(TempDateTime:12)
D  TempNN                        2s 0 overlay(TempDateTime:15)

c                   eval      DateDS = Date
c                   eval      TempCCYY = DateCCYY
c                   eval      TempMM   = DateMM
c                   eval      TempDD   = DateDD

c                   eval      TimeDS = Time
c                   eval      TempHH   = TimeHH
c                   eval      TempNN   = TimeMM

c                   test(ze)                TempDateTime
c                   if        %error
c                   return    *hival
c                   else
c     *iso          move      TempDateTime  DateTime
c                   return    DateTime
c                   endif

P                 E

All this is on a VRM450 machine.  What am I missing?

tia,
Peter Dow
Dow Software Services, Inc.
909 793-9050 voice
909 522-3214 cellular
909 793-4480 fax