tMap compare integer before UPDATE in expression filter

[Anonymous]

Hello,

I want to compare two Integer values before update filed.
I have this message when I execute "Incompatible operand types Integer and String"

Here is my problem:

I want to insert / update rows and get the exact number of rows inserted and updated for my log filr.

Exemple:

- Staging table (Table A)  : 4 rows        Dimension table (Table B) : 5 rows

If  I update one field or one row in my staging table , I must have nb_line_updated =1 in my file log but it put me the number of rows in Staging table (4 rows). I deduce that Talend update all fields...

I saw in this topic (https://community.talend.com/t5/Design-and-Development/Insert-or-update-on-duplicate-key-but-not-upd...) how to get the exact number of rows updated, I need to use "Equals" method if a value of my staging table has changed.  "Equals" method returns a boolean True or False.

If !row2.NON.equals(row3.NOM) is TRUE so I update and if it's FALSE I don't update .

it works for String and Date type but not works for Integer type.

Can someone help me how I can figure that with tmap or explain me an other way?
Any help would be really appreciated

Thank you.

Version of Talend :Talend Open Studio for Data Integration 6.3.1

JobSetting jdbc dimension tableSetting jdbc dimension tabletmap inner join -equalslog file Excel

[Anonymous]

This will be wrong. The only reason you are getting 1 update is because of the unique match option. 

For the moment forget hashing the records, it is completely different to what you are thinking. What you need to do is return all matches, concatenate all of the fields (from the main and lookup), then compare the concatenated values in a tMap variable. If they match, then use that to filter out the record. If they do not match, use that to allow the record to be updated.

[TRF]

@maria94, your design is OK for 1st subjob (insert) but I made a mistake for 2nd subjob update.

After tMap3, for rejected records, you need to have another tMap like tMap_3 but with only ID for the join.

Then, for matching records, get the values from TABLEB instead of TABLEA.

For Hash, not, this is not the way.

I'll come back soon with complements.

[TRF]

Concerning the "matching" strategy, the idea is to compare the MD5 signature for old record with the MD5 signature for new record.

To compute the signature, create a routine like this one (Repositry > Code > Routines then Create Folder and Create Routine) :

package routines;

import java.security.*;

public class GetHashCode {
	public static String getMD5(String strCode) {
		java.security.MessageDigest msg;
		String digest1 = "";
		try {
			msg = java.security.MessageDigest.getInstance("MD5");
			msg.update(strCode.getBytes(), 0, strCode.length());
			digest1 = new java.math.BigInteger(1, msg.digest()).toString(32);
		}
		catch (NoSuchAlgorithmException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return digest1.toUpperCase();
	}
}

The getMD5 method expect a string and return the MD5 signature for this string.

Now, to use the routine in a tMap, follow this example:

Push the result to a tHashOutput.

Do the same for both old and new records, then using the ID and rowMD5 fields, do a inner join, rejected records will give you all changed records. Imagine you have 100 fields, this method can be very usefull.

[Anonymous]

For UPDATE, it works with this design .

Look .

-Before UPDATE on TALEND

• In sql server , I updated 2 rows ID 1 and  2  on TABLE A
• tMAP UPDATE on Talend

• AFTER UPDATE on TALEND

In sql server , I get values updated in TABLE B

• After execute the job

[TRF]

OK sorry, I thought TABLEA was the target

[Anonymous]

Hi All,

I am implementating SCD type2 without using SCD component in talend but i am not getting Startdate and End as par my requirement.

Requirement is:

1.If data is fresh then start date will set as current date and end date as null.

2.if data is already exist in DB but one attribute changed that data then what it should do for previous record end date should be as current date and for new updated record start date as current date and end date will be null.

[Anonymous]

Hi All,

I am implementating SCD type2 without using SCD component in talend but i am not getting Startdate and End as par my requirement.

Requirement is:

1.If data is fresh then start date will set as current date and end date as null.

2.if data is already exist in DB but one attribute changed that data then what it should do for previous record end date should be as current date and for new updated record start date as current date and end date will be null.