Capture errors on multiple fields in a record ?

[kaps]

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

[DeepakCorning]

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 ?

[ray.wurlod]

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.

[kaps]

Deepak - we don't need to use lookup to validate data. Most of the times it's just checking the domain value of the column.

I believe Ray's reply suggests to have as many links as the errors which I am trying to avoid....

Thanks

[Krazykoolrohit]

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

[DeepakCorning]

Can a pivot stage will help? Our error validation table had the same structure for any validation failure so we used a Pivot stage to populate the table with whtvr validation failed.
I am not sure it will work for you or not but you can give it a thought.

[kaps]

Thanks for the reply...we are on the same track as Deepak suggested. will let you know...

[ShaneMuir]

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

Code: Select all

stagevar1 = If input.field1 valid then '' else 'Error Code|Error Message':Char10

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

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

[ray.wurlod]

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.