Hi,
I'm using a routine to do field validations. Multiple fields in a record should be validated for null,maxlength,numeric format,date format. If any of the fileds fail the validation, that record need to be sent to error table and there is no validation required for any other fields for that record.
I implemented this using a routine in conjunction with stage variables. I call the routine with the filed name,field value,maxlength,null flag('Y' or 'N' as the value),numeric flag('Y' or 'N' as the value),date flag ('Y' or 'N' as the value). If a field fails the validation, the routine returns the error column,error code and error name as a concatenated string.I captured the return value and based on it i decide whether to call the routine for the next field or push the record to error table. This works fine.
But the issue is that if I've 10 fields for length check, then I need to have 10 stage variables which is not desirable. I even thought of passing the arguments as a string. Say if 10 fileds need to be checked I will concatenate all these field names with a delimiter. Similarly all other arguments as well and in the routine I'll retrieve each field and do the check.
Can you suggest a better solution.
Routine to perform the error handling
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 153
- Joined: Thu May 11, 2006 1:52 am
- Location: Bangalore
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
-
- Participant
- Posts: 153
- Joined: Thu May 11, 2006 1:52 am
- Location: Bangalore
As mentioned in the previous post
Source is a flat file.First stage variable calls the routine with the first field name which needs to be checked. The routine returns empty string if validation is successful, else it returns a string with field name,field value and err code . I check the return value from the routine and call the routine again(here I pass the second field ...so on) only if the first field doesn't fail the validation. Finally based on the stage variable values I write the record to landing table or error table.
Code: Select all
I call the routine with the filed name,field value,maxlength,null flag('Y' or 'N' as the value),numeric flag('Y' or 'N' as the value),date flag ('Y' or 'N' as the value).
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
When you are reading the sequential file by a DS stage, then you are sure of the no. of columns available in the sequential file. Sorry, I was thinking that you don't know how many columns will come from source!!
How about this?
Just define one stage variable.
Code could be like.. (sample for 3 fields from source)
If routine(1st field, 1st field details) = '' Then
If routine(2nd field, 2nd field details) = '' Then
If routine(3rd field, 3rd field details) = '' Then ''
Else 'ERROR'
If you want to capture which field failed and what is the error code, then you redefine the IF statements differently. i.e. you need to have else for every IF.
So if one of the fields fail validation, your stage var will be populated with error code. Based on that you can define constraints in your transformer.
How about this?
Just define one stage variable.
Code could be like.. (sample for 3 fields from source)
If routine(1st field, 1st field details) = '' Then
If routine(2nd field, 2nd field details) = '' Then
If routine(3rd field, 3rd field details) = '' Then ''
Else 'ERROR'
If you want to capture which field failed and what is the error code, then you redefine the IF statements differently. i.e. you need to have else for every IF.
So if one of the fields fail validation, your stage var will be populated with error code. Based on that you can define constraints in your transformer.
Last edited by kandyshandy on Wed Dec 17, 2008 3:14 pm, edited 1 time in total.
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
this is another way..
define a stage var like this
routine(1st field, 1st field details) : routine(2nd field, 2nd field details) : routine(3rd field, 3rd field details) and so on....
Since you told that your routine returns '' on success, stage var value will be '' on success. Otherwise, it will have the error code received.
Note that it might have multiple error codes concatenated as we are just calling the routines for all fields.
define a stage var like this
routine(1st field, 1st field details) : routine(2nd field, 2nd field details) : routine(3rd field, 3rd field details) and so on....
Since you told that your routine returns '' on success, stage var value will be '' on success. Otherwise, it will have the error code received.
Note that it might have multiple error codes concatenated as we are just calling the routines for all fields.
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Search DSXchange for a server version of the IsValid() function. Adapt as required.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.