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
midrange.com
