UDF returning DATE always fails

25 pts.
Tags:
UDF
UDF Returning DATE
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 ;
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

The code you posted seems to protect itself from incorrect data, but I guess there must be something in the data that is causing an error when trying to cast it to a date type (for example, a record with a negative integer value).

I would suggest to add some logging to the function or run it in debug mode if possible, so you can identify what is the exact record that is causing the problem, and see if there is something wrong with its data.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Mpalmer78
    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.
    25 pointsBadges:
    report
  • Mpalmer78
    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.
    25 pointsBadges:
    report
  • 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,
    84,805 pointsBadges:
    report
  • carlosdl
    I saw your last comment after I posted mine. I'm glad you found the way, and identified the cause.
    84,805 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: