| Author |
Message
|
| dthomps2 |
 Posted: Tue May 24, 2005 11:53 am Post subject: Date Validation |
 |
|
Newbie
Joined: 24 May 2005
Posts: 7
Location: Harrisburg, PA
|
Hi,
I hope someone can help me out, as I have had no luck by digging through manuals.
Background: Our installation takes an incoming XML message and converts it to DB2 tables where our Mainframe application can process the data. It was decided by the powers above that we will not turn on Schema Validation because they do not want an entire message to fail when one field is invalid. This really only affects us in regards to date fields.
Problem: I can't seem to find a way to perform a simple date validation that does not throw the whole message to my failure node. What I want to do is validate each date field. If it is not valid, then I want to populate my database column with Nulls.
If I try to CAST it as a Date datatype and the value is invalid (i.e. '2004-0A-25', I get an exception and the whole message goes to my failure node.
If I try to evaluate each piece of the date individually, I can't seem to find a way to check for numerics. If I CAST the value as INTEGER, and the value contains an alpha character (i.e. '0A'), I get an exception and the whole message once again goes to my failure node.
I have not tried capturing these exceptions as I have never attempted it before. But that is what I will be reading up on next.
Any suggestions or ideas would be greatly appreciated. 
thanks,
dave |
|
| Back to top |
|
 |
| mayur2378 |
| Posted: Tue May 24, 2005 3:31 pm Post subject: |
|
|
Apprentice
Joined: 26 May 2004
Posts: 47
|
How but assign the date field to an environment and then substring the entire date field into month,day and year and then validate them individually. IF any field doesnt match, set the environment field to null and insert the environment variable...
Just a thought
Mayur |
|
| Back to top |
|
|
| dthomps2 |
| Posted: Wed May 25, 2005 6:20 am Post subject: |
|
|
Newbie
Joined: 24 May 2005
Posts: 7
Location: Harrisburg, PA
|
| mayur2378 wrote: |
How but assign the date field to an environment and then substring the entire date field into month,day and year and then validate them individually. IF any field doesnt match, set the environment field to null and insert the environment variable...
Just a thought
Mayur |
Mayur, Thank you for your suggestion. I tried that already. Pulling apart the date is not the problem. It is validating the parts that is not so simple. If I CAST them as integers and the value is invalid (for example the Month has a value of 'AA'), I get an exception and the message is sent to the failure node. If I validate it as a character, a value of '0A' is valid as it is between '01' and '12'. I would first have to validate that each character is numeric. I could do that with a CASE statement, but then this homemade validation routine becomes much larger and more cumbersome. I was trying to avoid that. |
|
| Back to top |
|
|
| mayur2378 |
| Posted: Wed May 25, 2005 7:08 am Post subject: |
|
|
Apprentice
Joined: 26 May 2004
Posts: 47
|
Am not sure about ur requirement. What happens if a month field has '0A'. Do you want to treat it as an exception? How do u want to handle it? If say ur month field is not valid ie 0A. then i would suggest capture it in an environment and validate each field. In ur case once u have the month
Environment.Variable.DT=InputRoot.MRM.Date
#where mm is the substring for month
CASE
WHEN mm<0
Then SET Environment.Variable.DT=NULL;
When mm>0
Then SET Environment.Variable.DT=NULL;
and then insert the Environment.Variable.DT inside the database..
This is just a simple solution from what i have gathered from your problem. Maybe the problem is more complex, if so then it wasnt clear.
Thankz
Mayur |
|
| Back to top |
|
|
| mayur2378 |
| Posted: Wed May 25, 2005 7:10 am Post subject: |
|
|
Apprentice
Joined: 26 May 2004
Posts: 47
|
Think i failed to mention , cast the field as an integer and then validate
Mayur |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed May 25, 2005 7:26 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| mayur2378 wrote: |
| Think i failed to mention , cast the field as an integer and then validate |
And if dthomps2 does that, the cast fails, and throws an exception which takes him out of the compute node.
I think dthmops2 wants to use a Try/Catch node, but I'm not positive.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| JT |
| Posted: Wed May 25, 2005 7:37 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
| Code: |
DECLARE listOfValidMonthValues CHARACTER '01,02,03,04,05,06,07,08,09,10,11,12,';
DECLARE extractedMonthValue CHARACTER SUBSTRING('2004-0A-25' FROM 6 FOR 2);
IF position((extractedMonthValue || ',') IN listOfValidMonthValues) = 0 THEN
-- Month is invalid --
ELSE
-- Month is valid --
END IF; |
|
|
| Back to top |
|
|
| dthomps2 |
| Posted: Wed May 25, 2005 8:16 am Post subject: |
|
|
Newbie
Joined: 24 May 2005
Posts: 7
Location: Harrisburg, PA
|
Jeff,
You are correct in that I get an exception if I cast an invalid integer. I am trying to avoid that. I am also trying to avoid the message going to a Catch Node. I do not want the entire messaage to be an exception. I want to evaluate the date field. If it is valid, I will insert the date to our database,. If it is invalid, I want to insert a null value to the database. We want to do this because our message can contain numerous loan applications and we do not want to error off all of the applications because one of them has an invalid date.
JT,
I think what you have recommended can help out quite a bit. I will try to put that into my esql. My only concern there will be validating the year. That would be a large list of valid values. But I may be able to work with this.
Thank you all for your thoughts and advise. It is very appreciated.
dave |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed May 25, 2005 8:19 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
You can adjust JT's example so that it works with one character instead of two, and use it as a generic 'is valid date characters' test.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
|
|
