Routine to perform the error handling

sumesh.abraham · Post by **sumesh.abraham** » Wed Dec 17, 2008 2:40 pm

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.

kandyshandy · Post by **kandyshandy** » Wed Dec 17, 2008 2:49 pm

What do you actually do in the stage variables? What are the parameters that you are sending to the routine?

Where from your source data coming? a file or a table?

sumesh.abraham · Post by **sumesh.abraham** » Wed Dec 17, 2008 2:57 pm

As mentioned in the previous post

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).

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.

kandyshandy · Post by **kandyshandy** » Wed Dec 17, 2008 3:10 pm

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.

kandyshandy · Post by **kandyshandy** » Wed Dec 17, 2008 3:21 pm

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.

ray.wurlod · Post by **ray.wurlod** » Wed Dec 17, 2008 3:36 pm

Search DSXchange for a server version of the IsValid() function. Adapt as required.