Capture reject from DBinput

Six Stars

Capture reject from DBinput

Hello!

I have a job in which i just extract the data i want to work with and put them in one table. I'm doing all that with a SQL query. i have the job and the query attached in one image to this post.

Given this can i get the rejects of my SQL query?

Thank you.


Accepted Solutions
Employee

Re: Capture reject from DBinput

Hi,

 

     You will have to use left outer join to identify the results. After that you can either use tmap or tfilterrows to separate the good and bad records. But my suggestion will be to go or tmap for better readability of the program for future developers.

 

     If the suggestion is working, please mark the topic as accepted solution as it will enrich our Talend community.

 

Warm Regards,

 

Nikhil Thampi


All Replies
Nine Stars

Re: Capture reject from DBinput

You are using INNER JOIN between several tables and retrieving the matched records, so to determine the rejects or the filtered out records you would need OUTER join - LEFT, RIGHT or FULL any combination of the three.
Six Stars

Re: Capture reject from DBinput

Okay thank you , but i think i'll do a treatment with tFilterRow on all the values before doing my join it will probably be simpler.

Employee

Re: Capture reject from DBinput

Hi,

      Since you are doing the inner join in the input query, Talend tmap will not be able to identify the rejected records. After verifying the screen shot, my understanding is that you are trying to get the details of each agent from different tables. So agent table is the driving table in your case and all other tables are lookups.

 

      So you will have to do left outer join to all the other child tables in the tDBInputquery. Pass the results to a tmap and wherever data is missing in child tables (for example, when you join with grade table, grades.id should be present in the result set). Pass all the records which are having null values for the columns from child table (in above example, grades.id) to different output group in tmap. If you do it for all the joining columns of child tables  of agent table, you can get the rejected columns.

 

Warm Regards,

 

Nikhil Thampi

Six Stars

Re: Capture reject from DBinput

Thanks i'll try it this way too. And you are right that is exactly what i want to do.

I was thinking about a solution like that but maybe taking everything from my table and using tFilterRow for null values would be easier but the two will have the same result.

Anyway i think the solution is good, i'll test it and come back to mark it as solved.

Employee

Re: Capture reject from DBinput

Hi,

 

     You will have to use left outer join to identify the results. After that you can either use tmap or tfilterrows to separate the good and bad records. But my suggestion will be to go or tmap for better readability of the program for future developers.

 

     If the suggestion is working, please mark the topic as accepted solution as it will enrich our Talend community.

 

Warm Regards,

 

Nikhil Thampi