Error logging
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 40
- Joined: Tue Oct 14, 2008 3:30 pm
- Location: London
Error logging
Hi,
My requirement is to reject and load the records which fail the business rule. Currently my job design is
seq stage->transformer 1->transformer 2->dataset1 and dataset2
Seq file to read the data from text, transformer 1 is to split the trailer and data record, transformer 2 is to validate the records for business logic. There can be multiple failures for a single record; we need to capture all the failures. Records which pass all the business validation moves to DataSet1 and rejected records will be moved to dataset2. Currently I am populating the different error codes against the same record with a delimiter.
The output of dataset 1 is
Emp_no Emp_Name Emp_Sal Status
1 A 100 Validation passed
2 B 100 Validation passed
The output of dataset 2 is
Emp_no Emp_Name Emp_Sal Status Error code
3 Validation Failed C2,C3
4 B Validation Failed C3
Business wants the output as below (dataset3)
Table Emp_No Error code
EMP 3 C2
EMP 3 C3
EMP 4 C3
Can someone help me how to create the output using transformer 2 and send the detail to a new dataset 3 in the above format? Option ops out of my mind is to use dataset2 and load the table errog log table as we have comma as the delimiters between different error codes (i feel this option is not efficient)[/img]
My requirement is to reject and load the records which fail the business rule. Currently my job design is
seq stage->transformer 1->transformer 2->dataset1 and dataset2
Seq file to read the data from text, transformer 1 is to split the trailer and data record, transformer 2 is to validate the records for business logic. There can be multiple failures for a single record; we need to capture all the failures. Records which pass all the business validation moves to DataSet1 and rejected records will be moved to dataset2. Currently I am populating the different error codes against the same record with a delimiter.
The output of dataset 1 is
Emp_no Emp_Name Emp_Sal Status
1 A 100 Validation passed
2 B 100 Validation passed
The output of dataset 2 is
Emp_no Emp_Name Emp_Sal Status Error code
3 Validation Failed C2,C3
4 B Validation Failed C3
Business wants the output as below (dataset3)
Table Emp_No Error code
EMP 3 C2
EMP 3 C3
EMP 4 C3
Can someone help me how to create the output using transformer 2 and send the detail to a new dataset 3 in the above format? Option ops out of my mind is to use dataset2 and load the table errog log table as we have comma as the delimiters between different error codes (i feel this option is not efficient)[/img]
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Premium Member
- Posts: 40
- Joined: Tue Oct 14, 2008 3:30 pm
- Location: London
Thanks for your response. I am not clear how funnel will help us in this case. In the transformer for a single record we will have multiple error codes i.e. for emp_no 3 emp_name & emp_sal is null so using stage variables and populated c2 & c3. Similarly for emp no 4 only emp_sal is null so we have populated c3. In my output I have only three column table_name(can be populated by hardcoding the value), emp_no will be taken from source. But how we can map multiple stage variables(2 stage variables is this example 1 to check for emp_name and other to check emp_sal) to a single column error_codes in ds2.Sainath.Srinivasan wrote:send the reject rows from tx2 through to a funnel stage and its output to ds2
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Premium Member
- Posts: 40
- Joined: Tue Oct 14, 2008 3:30 pm
- Location: London
Thanks, this should work but the problem the job design will look very complicated as we have around 100+ attributes to check and also the development time will be more. Do you have any other better approaches?Sainath.Srinivasan wrote:You will have multiple output links from the tx, each representing one possible error.
There are other methods to do this. But this will be simplest to design and implement - in my opinion.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Premium Member
- Posts: 40
- Joined: Tue Oct 14, 2008 3:30 pm
- Location: London
Thanks it worked. But the output from pivot is not coming as NULL, so filter is not working to remove the NULL value. I tried using trim function to check if there any blank spaces populated by pivot and put a constraint to remove records with NULL valus, still no luck. Please let me know if any format to be changed in pivot so it passes NULL Value for the columns for which there are no associated values.chulett wrote:That just looks like a Pivot to me, why not perform the pivot of using the maximum number of output columns and then trim null results post pivot?
-
- Premium Member
- Posts: 40
- Joined: Tue Oct 14, 2008 3:30 pm
- Location: London
I have used trim funtion and set null function to set the blank values to NULL before sending it to pivot. This fixed the issue.senthil_tcs wrote:Thanks it worked. But the output from pivot is not coming as NULL, so filter is not working to remove the NULL value. I tried using trim function to check if there any blank spaces populated by pivot and put a constraint to remove records with NULL valus, still no luck. Please let me know if any format to be changed in pivot so it passes NULL Value for the columns for which there are no associated values.chulett wrote:That just looks like a Pivot to me, why not perform the pivot of using the maximum number of output columns and then trim null results post pivot?