We have legacy tables storing dates as integer fields... ie 2012001. I have built a UDF that parses, joins, and casts the segments to a DATE type. This works fine until I hit about 800 rows and I get a non-specific SQL error. If I return a CHAR(10) with the formatted date, I get no error. Only when I RETURN type DATE and CAST the CHAR to DATE within my UDF does the error occur. I'm not a DB2/400 buff and I need some help please. Thanks.
Here's the UDF:
CREATE FUNCTION REPORTS.GETDATE_MMDDYYYY (
CRAPDATE NUMERIC(8, 0) )
RETURNS DATE
LANGUAGE SQL
SPECIFIC REPORTS.GETDATE_MMDDYYYY
DETERMINISTIC
READS SQL DATA
RETURNS NULL ON NULL INPUT
DISALLOW PARALLEL
NOT FENCED
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN
DECLARE STRINGDATE CHAR ( 8 ) ;
DECLARE LEN INT ;
DECLARE M CHAR ( 2 ) ;
DECLARE D CHAR ( 2 ) ;
DECLARE YR CHAR ( 4 ) ;
--ensure the data is 8 chars minimum
SET STRINGDATE = TRIM ( CRAPDATE ) ;
SET LEN = LENGTH ( TRIM ( STRINGDATE ) ) ;
SET STRINGDATE = CONCAT ( REPEAT ( '0' , 8 - LEN ) , STRINGDATE ) ;
--extract month
SET M = LEFT ( STRINGDATE , 2 ) ;
IF M = '00' THEN
SET M = '01' ;
END IF ;
--
correct any invalid month value
IF INT ( M ) > 12 THEN
SET M = '12' ;
END IF ;
--extract day
SET D = SUBSTRING ( STRINGDATE , 3 , 2 ) ;
IF D = '00' THEN
SET D = '01' ;
END IF ;
--correct any invalid day values for months (ie too many days in a month)
IF M = '02' THEN --Feb cannot have more than 28 days
IF INT ( D ) > 28 THEN
SET M = '27'
;
END IF ;
ELSE
IF INT ( D ) > 31 THEN
SET D = '31' ; --reduce days > 32 down to 31 (correcting invalid data)
END IF ;
IF INT ( D ) = 31 THEN
CASE M
WHEN '04' --april
THEN SET D = 30 ;
WHEN '06'
--june
THEN SET D = 30 ;
WHEN '09' --sept
THEN SET D = 30 ;
WHEN '11' --nov
THEN SET D = 30 ;
ELSE
SET D = D ;
END CASE ;
END IF ;
END IF ;
--extract year
SET YR = RIGHT ( STRINGDATE , 4 ) ;
IF YR = '0000' THEN
SET YR = '1901' ;
END IF ;
RETURN DATE(YR || '-' || M || '-' || D) ;
END ;
Carlosdl,
Thank you for your response. Could you point me in a direction to add logging? I also do not know how to run in debug mode... although I've spent some time trying to figure this out on my own. I found the Java graphical debugger, but instructions on how to launch seemed very diluted and not specific enough for me to figure out.
Thanks again.
Ok, I figured it out. Thanks for your tip about logging. Although quite an obvious troubleshooting step, I missed it completely while being new to the AS/400 DB2 realm. Anyway, I created a log table and wrote to it within my UDF. The problem indeed was bad data. There is no such date as 2292004. February 29th doesn't exist and my validation code had a typo in it when checking for this.
This company had less than brilliant programmers creating the programs that captured this data... and I find myself having to program around them constantly (I'm not that brilliant either!) Geez... data validation anyone?
Thanks for lending a hand Carlosdl.
Hi Mpalmer78.
I have never worked with DB2, so I can't offer any help on it.
I have seen similar situations while working with other databases/platforms, and in most cases it was due to data errors.
So, I hope somebody else can provide further help, but I think you may have better chances to get answers on how to log information, debug, or handle exceptions in UDFs if you create a new question on that topic.
Regards,
Discuss This Question: 4  Replies