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

[TRF]

For string variables like var1 use:

row1.NOM == null || row.NOM.equals("") ? "" : row1.NOM

For integer variables like var5 use:

row1.age == null || row.age.equals("") ? null : row1.age

(you cannot put "" into an integer variable).

For output flow, use conversion from string to integer for age:

Integer.parseInt(row1.age)

[Anonymous]

Thanks @TRF to reply me .

-The probleme if i put 

row1.age == null || row.age.equals("") ? null : row1.age

I have a java.lang.NullPointerException.

-row1.age in output is  an integer type , why I should to convert into string ?

For output flow, use conversion from string to integer for age:

Integer.parseInt(row1.age)

[Anonymous]

OK, the problem here is that row1.age will NEVER be a String. The incoming table shows it as an Integer. As such you don't need the following check...

row1.age == null || row.age.equals("") ? null : row1.age

You are allowing nulls for these fields and this is what causes your null pointer exception issue. If var5 is null, you cannot make use of the "equals" method. The same for var6. As such you need to implement logic similar to this....

((var5==null && var6==null) || (var5!=null && var6!=null && var6.equals(var5)))

Basically the above says where var5 and var6 are null (therefore equal) OR var5 and var6 both have values which are equal. 

This way you avoid the null pointer exception

[Anonymous]

Hi Everyone,

I guess maria requirement is something like below

 Source Table: Person

(Id Integer, Name String, Age Integer)

Id   Name Age

1    John   21

2    Richard  23

3   Sree 

Expected output:

Target table: Destinaton

(Id Integer, Name String, Age Integer)

Id   Name Age

1    John   21

2    Richard  23

3   Sree     NULL

When source table value and target table value does not equal then we should update the target table values as source table values.

If there is any updating in source table Name column that should also reflect into the target table.

Example: Source: Name: John into John Kenedy 

TargetTable: Name should get update

validating String is not a problem but it throws null pointer exception error for Integer to the Integer type.

[Anonymous]

If you look at @maria94's job you will see that Age cannot be a String. It is set as an Integer. The issue she has is what I have described above. She is trying to use a method (equals) on an un-initialized object (var6.equals). If var6 is null it cannot make use of "equals". 

[Anonymous]

Thanks @rhall to reply me.

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 .

tmap

log file

[Anonymous]

This is a different issue to the first one. It is a good idea to separate these issues into different posts. This will help others find solutions to their problems.

How are you deciding whether a row should be an update or insert? If it is related to a key field in the table you can do this using one database component. Configure the Field Options to select your Update Key field and set the Action on Data to be "Insert or Update". See below.... 

Once you have this set properly, you can have the component decide whether it is an insert or update and you can use the related counts to tell you accurately how many of each you have.

[Anonymous]

Thanks @rhall for your answer, but you can't insert and update in the same time with your exemple ?

Maybe my tMap is wrong ...

[Anonymous]

You shouldn't need the tMap lookup anymore if you are just checking to see if the record exists. That is ALL handled by the DB component. You will not need to split the flow, just plug it all into the same DB component and configure as I suggested (take a backup first in case you want to stick with your way).