Compare columns from DB and File

Anonymous — Thu, 01 Nov 2018 10:28:10 GMT

Hi,

I have a scenario where I need to compare the data from file with data in DB. In file there are 13 columns and in data from DB there are 9 columns. I need to check if the value for 6 columns are same in DB and in file.

So, below are steps I have performed:

1) Get data from DB

2) Get data from file using file delimited component

3) Perform Unique match and Inner join on the 6 columns between DB and file in TMAP component (i.e. from input tmap).

4) Also created 6 variables (variables section in tmap) which performs a check to see if columns are same, for example :

row3.id == row4.ID? true : false

row3.cust == row4.CUST ? true : false

5) There are 2 outputs: Accepts and Rejects

6) In expression filters for Accepts : Added all 6 variables created with "&&" i.e. AND condition between the variables so that Accepts should have all the variables which have succeeded.

7) In output section of tmap for Rejects, have set both "Catch Output Reject" and "Catch Inner join Reject" to true.

😎 Store the above outputs i.e. Accepts and Rejects to thashoutput.

9) Also in Rejects, I have created a new variable "failure_reason" which contains the reason for failure. Example, please see below:

(Var.var1 ? "" : "id value does not match between db and ftp. FTP value : " +row4.ID+" and DB value : " + row3.id +" ; ") +
(Var.var2 ? "" : "cust value does not match between db and ftp. FTP value : " +row4.CUST+" and DB value : " + row3.cust +" ; ")

The problem is that I do not see the above steps performing comparison of data between file and DB correctly. I see that the outputs are not as expected (or in short the comparison is not as expected because I know what DB and file data contain).

Can anyone please let me know how to check if data from file and DB are same ?

Re: Compare columns from DB and File

TRF — Thu, 01 Nov 2018 13:58:24 GMT

Depending on the datatype for each field, you have to use the appropriate method for comparison such as String.equals for strings and so on.
You may also share your tMap and sample data with expected result.

topic Compare columns from DB and File in Talend Studio

Compare columns from DB and File

Re: Compare columns from DB and File