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

Hi Matt,

Yeah, I read those posts too.  I got around this problem by doing pretty
much what you suggested:

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

then a

c/exec SQL
c+ FETCH REC_PTR INTO :ERPLOG,:Hours
c/end-exec

and it works fine, although for the variation where I don't need "Hours",
I'm now doing:

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

both of which look a little odd.  I'll try debugging both and see what that
shows.

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


----- Original Message -----
From: "Tyler, Matt" <mattt@wincofoods.com>
Sent: Friday, September 20, 2002 11:48 AM
Subject: RE: Using SQL UDF in SQLRPGLE program gets decimal data error


> Whether or not you use DEC() or NUM() as the UDF definition I do not
> think should matter.  But, if you look back in recent posts you'll see
> things that the SQL pre-compiler does or does not do that are not
completely
> compatible with RPGLE.  I had one where a procedure parameter was defined
> with CONST and I tried to use the %ADDR().  It worked as a SQLRPGLE but
not
> as RPGLE.
>
> I do know that RPGLE is supposed to convert similar data types to the
actual
> data types used on procedure parameters where the CONST keyword is
> specified.   I know the SQL can do the same.  Can you get away from
> SQLRPGLE? I thought you indicated that it is the only one that causes
issues
> or in your prepared SQL statement have the UDF return in the results set.
> Then you might not need to run the same UDF again in the program.
>
> SELECT *, ELAPSEDHOURS(ELDATE,ELTIMI,ELDATE,ELTIMO) as ELPSHRS
>  FROM ERPLOG
>   WHERE ELDATE BETWEEN 20020601 AND 20020631 AND
> ELAPSEDHOURS(ELDATE,ELTIMI,ELDATE,ELTIMO) < 24
>
> P.S. Have you tried to DEBUG the UDF when calling it from the different
> sources?