AW: More Questions on UDF

["BirgittaHauser" <Hauser@xxxxxxxxxxxxxxx>]

Hi,

Just for information:
Character expressions passed as parameters to an UDF are always interpreted
as VARCHAR. CHAR and VARCHAR are different data types for SQL. Because UDFs
can be overloaded and overloading checks not only the number of parameters
but also the data types of the parameters, the UDF cannot be found in the
second example (' ' is interpreted as VARCHAR and not as CHAR).

To convert the parameters to the expected data type either scalar functions
(such as CHAR or VARCHAR) must be used or the expression must be coverted by
using CAST.

Numeric expressions or constants (without decimal points) are always
interpreted as data type integer. 
For SQL Integer, Small Integer and Big Integer are different data types,
while Decimal/Packed and Numeric/Zoned are considered as compatible.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them?  Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von rob@xxxxxxxxx
Gesendet: Wednesday, May 09, 2007 16:01
An: RPG programming on the AS400 / iSeries
Betreff: Re: More Questions on UDF


Did some exhaustive testing.

This:
select 
CVTDATE(cast(DIGITS(12345) as char(6)),
cast(' ' as char(8)), 
cast('*JUL' as char(7)), 
cast('*YMD' as char(7)), 
cast('*NONE' as char(7)), 
' ', 
cast(' ' as char(2))) 
from SYSIBM/SYSDUMMY1 
Generates the error:  CVTDATE in *LIBL type *N not found.

However, this does NOT:
select 
CVTDATE(cast(DIGITS(12345) as char(6)),
cast(' ' as char(8)), 
cast('*JUL' as char(7)), 
cast('*YMD' as char(7)), 
cast('*NONE' as char(7)), 
cast(' ' as char(1)), 
cast(' ' as char(2))) 
from SYSIBM/SYSDUMMY1 

What is the difference?  Extensive usage of CAST.  Notice the only 
difference was changing a simple one space character ' ' to a cast.  You 
will notice this most frequently when testing.  Because testing is when we 
are most likely to use constants, like '*NONE'.  While, in a program we 
will be using host variables that are defined with the proper size.  I 
think that functions have an issue with nonCASTed constants.

Also, I used this source (don't know if the capitalization makes a 
difference):
     h nomain
      *-------------------------------------------*
      *  Execute JDE Server for Date Conversion   *
      *-------------------------------------------*
     D GETDATE         pr             8S 0
     D  FromDate                      6A
     D  ToDate                        8A
     D  FromFormat                    7A
     D  ToFormat                      7A
     D  DateSep                       7A
     D  ErrorFlag                     1A
     D  Century                       2A
     PGETDATE          B                   EXPORT
     D GETDATE         PI             8S 0
     D  FromDate                      6A
     D  ToDate                        8A
     D  FromFormat                    7A
     D  ToFormat                      7A
     D  DateSep                       7A
     D  ErrorFlag                     1A
     D  Century                       2A
     D RetDate         S              8S 0 Inz

     C*
     C                   Z-ADD     *ZEROS        RetDate
     C                   RETURN    RetDate
     P GETDATE         E 

And:
CRTRPGMOD MODULE(ROB/GETDATEMOD) SRCFILE(ROB/QPGMSRC) SRCMBR(GETDATE)
CRTSRVPGM SRVPGM(ROB/GETDATESRV) MODULE(ROB/GETDATEMOD) EXPORT(*ALL) create
function ROB/CVTDATE (CHAR (6), CHAR (8), CHAR (7),
                  CHAR (7),CHAR (7),CHAR (1),CHAR (2)) 
  returns DEC (8,0) 
  language rpgle 
  deterministic 
  no sql 
  returns null on null input 
  allow parallel 
  simple call 
  external name 'ROB/GETDATESRV(GETDATE)' 


Rob Berendt