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

My guess would be that when SQL calls the function, it works, because SQL
converts field values from zoned to packed decimal.
When you call the same function directly, you pass zoned values - hence the
decimal exception.

    Alexei Pytel
always speaking for myself




                      "Peter Dow"
                      <maillist@dowsoft        To:       <RPG400-L@midrange.com>
                      ware.com>                cc:
                      Sent by:                 Subject:  Using SQL UDF in 
SQLRPGLE program gets decimal data
                      rpg400-l-admin@mi         error
                      drange.com


                      09/19/2002 02:08
                      PM
                      Please respond to
                      rpg400-l





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.