DSXchange

Hey,

I am looking for a way to concatenate all the reject codes that are generated as part of Data validation. All the reject codes from invalid lengths, incorrect dates, record count mismatch etc are to be written as space delimited column and appended to the incoming record and sent as reject file.

Tried doing this using a gigantic if..then statement in the transformer derivation for all combinations of reject codes (7 in my case) is not efficent. I would appreciate if some one can suggest me the best approach.

Thanks in advance!
-Davis

Use stage variables. Create one stage variable per field and give it a meaningful name. Create an IF statement in that stage variable that sets it to a reject code if the rule fails or sets it to '' if it succeeds. Have a stage variable at the bottom of your stage variable lists that concatenates all your reject codes together. If all the rules pass then your RejectCodes stage variable will concatenate empty strings become an empty string.

In your constraints use the len(RejectCodes) to determine whether you have any rejects and output the RejectCodes down your reject link.

I have been putting a char(10) carraige return as a prefix to my reject codes to push them onto seperate lines in an output text file. This gives me a text file with two record types, the original data row that failed followed by the reject codes and the field that triggered the reject. This is then processed, split and loaded into reject tables for reporting.

You can extend this in lots of ways. For example if the business rules are determined using Boolean functions, the results of these can be combined with AND operators to determine whether the row satisfies all business rules.
But "use stage variables" is sage advice.

Thanks for the suggestions!! Used Stage variables and its great.

-Davis