Capture errors on multiple fields in a record ?
Moderators: chulett, rschirm, roy
Capture errors on multiple fields in a record ?
We have a process to deliver a file which has more than 50 fields. We need to add error process to this. we need to load a error table whenever there is a data violation.
Problem is we need to capture all the violations in a record not just the first one. I can have mulitple links going out but I don't think thats a good idea as I have more than 30 fields to check.
Other approach we thought is to write a routine to create a record whenever we have a violation. In this case if all 30 cols has problems then we will call this routine 30 times...don't know how it's going to affect the performance.
Anyone has any other approach ?
Thanks
Problem is we need to capture all the violations in a record not just the first one. I can have mulitple links going out but I don't think thats a good idea as I have more than 30 fields to check.
Other approach we thought is to write a routine to create a record whenever we have a violation. In this case if all 30 cols has problems then we will call this routine 30 times...don't know how it's going to affect the performance.
Anyone has any other approach ?
Thanks
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
Re: Capture errors on multiple fields in a record ?
When you say capture Data Violation Do you mean you are having lookups which Validate the data or something else. If yes then is it there for all the columns or selective one ?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Use stage variables to store the test results, and to create diagnostic information for output. The "rejects" link assembles information from as many stage variables as detect "violations".
There is no limit on the number of stage variables that you are permitted to use.
There is no limit on the number of stage variables that you are permitted to use.
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.
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio
This is what we did in our project. we knew all our errors.
Create a temporary column say col10(inside datastage). Validate each columns of yours for data and if you find an error then load a flag into the column.
ex: if col1 has a data validation error, then concatenate "A01". If col2 has a error, concatenate "B02" and so on.
in the last job you can segeregate all records according to the data existing in col10.
ex: if you find A01B02 in col10, you know which column the records has error in and send that particular record to the desired reject file
Create a temporary column say col10(inside datastage). Validate each columns of yours for data and if you find an error then load a flag into the column.
ex: if col1 has a data validation error, then concatenate "A01". If col2 has a error, concatenate "B02" and so on.
in the last job you can segeregate all records according to the data existing in col10.
ex: if you find A01B02 in col10, you know which column the records has error in and send that particular record to the desired reject file
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
Not sure this would work but, you could incorporate Ray's idea of using Stage Variables to check the validity of the incoming data. If the particular field is invalid is someway have the output be a warning message for that field eg
Not sure the structure of your error table so the above example assumes an Error Code and an error message column only and '|' is a delimeter.
You would have similar code for each field you wish to check. You would then have another Stage Variable which concatenates all the other error stage varibales eg
If you write this output to a sequential file with 2 columns and a '|' delimiter then it should be read as multiple error records, for each input line. (ie one line per column error).
You can then use this sequential to load the error table.
Once again not sure if it would work, but it sure does sound good![Wink :wink:](./images/smilies/icon_wink.gif)
Code: Select all
stagevar1 = If input.field1 valid then '' else 'Error Code|Error Message':Char10
You would have similar code for each field you wish to check. You would then have another Stage Variable which concatenates all the other error stage varibales eg
Code: Select all
StageVar1:StageVar2:...:StageVarN
If you write this output to a sequential file with 2 columns and a '|' delimiter then it should be read as multiple error records, for each input line. (ie one line per column error).
You can then use this sequential to load the error table.
Once again not sure if it would work, but it sure does sound good
![Wink :wink:](./images/smilies/icon_wink.gif)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
It does work. I've done this with over 80 tests. I use intermediate stage variables to concatenate each group of 10 or so, then concatenate those together, just to make things more manageable.
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.