RE: Using SQL UDF in SQLRPGLE program gets decimal data error

How about removing the statement "RETURNS NULL ON NULL INPUT".

Maybe ELAPSEDHOURS() is returning a nulled result which in RPGIV actually
has data (unpredictable data but data none the less).  All null capable DB
columns do actually contain data and you can reference that data but you
might get a data decimal error.  You can only test the null status of a DB
column.  Any program created field will not have the null indicator.

I hope I made sense here.

Thank you,
Matt Tyler
Mattt@wincofoods.com

-----Original Message-----
From: Peter Dow [mailto:maillist@dowsoftware.com]
Sent: Thursday, September 19, 2002 13:08
To: RPG400-L@midrange.com
Subject: Using SQL UDF in SQLRPGLE program gets decimal data error

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



_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.