Here's a simple function:
CREATE FUNCTION datawhse.num2cdate( inval INT) RETURNS CHAR
LANGUAGE SQL
BEGIN
RETURN(nullif(substr(CHAR(inval +19000000),1,4)||'-'||substr(CHAR(inval
+19000000),5,2)||'-'||substr(CHAR(inval +19000000),7,2)||' 00:00:00:000',
'1900-01-01 00:00:00:000'));
END
Rather than doing using that whole numeric date to character date conversion
mess above, we can just use this SQL function that encapsulates it. It
compiles, and I can see the function object both via the WRKOBJ CL command
(appears as a *SVRPGM object) and by querying the QSYS2.SYSROUTINES view.
However, when I try to actually use the function, I get this error message:
select datawhse.num2cdate(somedate) from datawhse.testdate
NUM2CDATE in DATAWHSE type *N not found.
Even though I'm specifying the function schema, it's not able to find the
function. What am I missing here? Any suggestions appreciated!
Thx,
Dave
