topic Re: tMap compare integer before UPDATE in expression filter in Talend Studio

tMap compare integer before UPDATE in expression filter

Anonymous — Mon, 22 May 2017 10:23:52 GMT

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-update-all-fields/td-p/73092) 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

Re: tMap compare integer before UPDATE in expression filter

TRF — Mon, 22 May 2017 10:35:57 GMT

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)

Re: tMap compare integer before UPDATE in expression filter

Anonymous — Mon, 22 May 2017 12:39:38 GMT

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)

Re: tMap compare integer before UPDATE in expression filter

Anonymous — Mon, 22 May 2017 13:02:01 GMT

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

Re: tMap compare integer before UPDATE in expression filter

Anonymous — Mon, 22 May 2017 13:40:29 GMT

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.

Re: tMap compare integer before UPDATE in expression filter

Anonymous — Mon, 22 May 2017 13:51:23 GMT

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".

Re: tMap compare integer before UPDATE in expression filter

Anonymous — Mon, 22 May 2017 14:15:19 GMT

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 !row2.NON.equals(row3.NOM) is TRUE so I update and if it's FALSE I don't update .

tmap

log file

Re: tMap compare integer before UPDATE in expression filter

Anonymous — Mon, 22 May 2017 14:40:37 GMT

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.

Re: tMap compare integer before UPDATE in expression filter

Anonymous — Mon, 22 May 2017 15:25:49 GMT

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

Maybe my tMap is wrong ...

Re: tMap compare integer before UPDATE in expression filter

Anonymous — Mon, 22 May 2017 15:31:15 GMT

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).

Re: tMap compare integer before UPDATE in expression filter

Anonymous — Mon, 22 May 2017 15:54:03 GMT

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.

Re: tMap compare integer before UPDATE in expression filter

Anonymous — Mon, 22 May 2017 16:01:27 GMT

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?

Re: tMap compare integer before UPDATE in expression filter

Anonymous — Mon, 22 May 2017 16:22:40 GMT

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.

Re: tMap compare integer before UPDATE in expression filter

Anonymous — Mon, 22 May 2017 16:30:37 GMT

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}

Re: tMap compare integer before UPDATE in expression filter

TRF — Mon, 22 May 2017 16:30:58 GMT

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?

Re: tMap compare integer before UPDATE in expression filter

Anonymous — Mon, 22 May 2017 16:46:13 GMT

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.

Re: tMap compare integer before UPDATE in expression filter

Anonymous — Mon, 22 May 2017 16:49:06 GMT

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-update-all-fields/td-p/73092

Re: tMap compare integer before UPDATE in expression filter

Anonymous — Mon, 22 May 2017 16:58:09 GMT

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.

Re: tMap compare integer before UPDATE in expression filter

TRF — Mon, 22 May 2017 17:08:14 GMT

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.

Re: tMap compare integer before UPDATE in expression filter

Anonymous — Tue, 23 May 2017 09:16:37 GMT

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 ?