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]

It doesn't work for me ...

with your method , if i update 1 field , it  update all rows and the number of rows updated is wrong . 

I configured as your suggested 

In my exemple : I update one field and add 1 row.

The number of rows inserted is exact and the number of rows updated is wrong.

[Anonymous]

If the ID exists in the DB it will update the record, if the ID does not exist then it will insert the record. Is this not the functionality you want? An update can only occur (in any DB) if the key field is available. You cannot update a non-existent record. I think maybe you are expecting something different to what I have interpreted?

[Anonymous]

I want to return the exact number of row updated in my job for the log file.

if I update 1 field , it will be returned nb_lines_updated= 1 in the log file and not nb_lines_updated= 5.

Currently, it returns the total number of rows of staging table.

The INSERT is work.

[Anonymous]

I see. This says to me that your record from your file is updating multiple DB records. So your ID is not unique in the database. Is that what you expect? If so there is an easy work around assuming that all records output from your file will either insert or update. You can identify the number of records from your file (using a similar technique to the one you are using to count the number of inserts). All you do is use the following logic....

{Number of records updated} = {Number of records in file} - {Number of records inserted}

[TRF]

But you don't have any filter to avoid all existing rows to be updated.

So, if you have 6 rows in the input with 1 new one, you have 5 rows updated. It's normal.

Are you looking for a strategy to update only rows with at least 1 field changed compared to the actual value?

[Anonymous]

Yes , I want to update only rows with at least 1 field changed when i compare new data (staging table ) and old data (dimension data).

For UPDATE, i had used 'Equals' function to compare between new and old data .

I do not know if you have any other solution than my method.

[Anonymous]

My ID is unique (primary key  of my table) .

My problem is similar with this post .https://community.talend.com/t5/Design-and-Development/Insert-or-update-on-duplicate-key-but-not-upd...

[Anonymous]

The easiest way to achieve this is to either create a hash of your complete record and compare hashes OR (an even simpler way) concatenate your fields from your database query (your lookup), concatenate your fields from your input file (in exactly the same way....and do it as a String) then compare using "equals".

However, I now see that you have already worked out how to do that with the first part of your question. You are now looking to identify how to update your record in your DB only where there are differences. The easiest way to do this is with a unique key in your database. Do you have this? If so, simply return the unique key from your lookup where you find a change has occurred. If you do not have a unique key, this will be a lot harder. You might be able to get round this using the t{DB}Row component to dynamically create an update statement that suits each row. Ideally you will have access to a unique key.

[TRF]

So, your 1st design was not so bad as soon as you don't have to deal with delete.

You should have something like that:

                             TABLEB
                                | lookup
TABLEA --main--> tMap (inner join using ID) --reject--> insert newRecords
|
onSubjobOK
|                            TABLEB
|                               | lookup
TABLEA --main--> tMap (inner join using all fields) --reject-->update TABLEA with TABLEB fields
                                |
                             no action required

As suggested by @rhall, computing a hash key based on all the fields is the solution as soon as you have lot of (+10?) fields.

Let me know if yo want to know how.

[Anonymous]

Thanks guys .

I made @TRF solution and it works  (I think ). 

@TRF can you check if it's right or not pls.

job

INSERT tMAP

UPDATE tMAP

 In tMAP for INSERT and UPDATE, it preferable to put "Unique Match " or "All match" in join model ? I don't know what is the difference between both.

If I use Hash, it's like in this article Efficient Lookups with Talend Open Studio's Hash Components ??

@TRF or @rhall Can you tell me how it works pls ?