Get MAX(index) FROM table [tMysqlRow]

[Anonymous]

Hi all,
I'm trying to get the maximum value of a column which contains INT values.
Therefore, I use a tMysqlRow with the following query:

"SELECT MAX(id_partner) FROM BI_DWH_dimension_partner"

Then I try to link this tMysqlRow with a tMap, but during the execution of the job we can see that the value of the row loaded is null.
What am I doing wrong here?
Thank you for your advice

[Anonymous]

The actual purpose of the tMysqlRow component is different than your current use case. In your case it is much easier to use tMysqlInput.
I guess your use case is actually a test case. To get the values of query you have to do following:
tMysqlRow: configure here the schema for your columns you want to have and an additional column called resultset of type Object.
In the advanced settings of the tMysqlRow activate the option "Propagate QUERYs record set" and choose as column the resultset column.

Here the necessary settings for the tParseRecordSet. This component gets the values from the result set.
It is important to note that this component depends on a named column in the result set -> give the max(id_partner) an alias!


Actually for a simple use case this is very cumbersome.
Here the easy way:

[Anonymous]

Hi jlolling,
First of all, thank you for your complete answer. I decided to use your last solution by using a tMysqlInput component.
I now added screenshots of my real job since I'm still facing an issue.
Here is my job goal:
Within the tables in input I try to match the field b2b_partner with partner_name.
IF there is a match
   THEN I would like to add a row as output with the field  id_partner that match partner_name
=> This is the output link named  UPDATE
IF there is no match
   THEN I would like to add a row as output with the MAX( id_partner) +1
=> This is the output link named  NEW

As you can see in the job_execution screenshot, it doesn't work at all.
The 2 rows that goes through the UPDATE link are not suppose to go that way.
These 2 rows are suppose to use the NEW link since their b2b_partner values don't have any match with any partner_name.
Thank you for your help.

[Anonymous]

Your screenshot shows a problem. You have chosen the output NEW as inner join reject but you actually do not have an inner join.
You have to specify the join as inner join.

[Anonymous]

Hi jlolling,
Sorry for late reply I was on an another project.
Thank you for you answer, indeed I forget to set up an inner join.
Since I did this now, I'm facing a new error : java nullpointer exception (screenshot attached)
I found out that if I replace inside the tMap the value row2.id_partner+1 for the output named "NEW" by a constant number like 0 or 1, the error message is not raised.
This value  row2.id_partner comes from a tMysqlInput based on the following query

SELECT MAX(id_partner) FROM BI_DWH_dimension_partner

On the job designer we can see that I use a vlookup link and I think an Iterate one would be more appropriate because for each row in the tMap I need to get the MAX(id_partner). The problem is that Talend doesn't allow me to create an iterate link from the tMysqlInput to the tMap.
I didn't find a solution yet.
Thx a lot for your help.

[Anonymous]

This NullPointerException is the result of following implicit code caused by the so called unboxing procedure in Java:

row2.id_partner.intValue() + 1

This is the actual code which runs. The problem is a null value from the database. I suggest you put a coalesce around the expression in the SQL code like this:

SELECT COALESCE(MAX(id_partner),0) as id_partner FROM BI_DWH_dimension_partner

[Anonymous]

Thank you for your answer.
I used your query below, and I change the value of my output field to  row2.id_partner.intValue() + 1

SELECT COALESCE(MAX(id_partner),0) as id_partner FROM BI_DWH_dimension_partner

But the exact same error has been raised.
What I don't understand is that normally I shouldn't get any null pointer exception since my table BI_DWH_dimension_partner already has 2 rows inside.
That means the query should return 2+1 = 3
Am I still doing something wrong?

[Anonymous]

I have taken a look into your job now and the problem is quite clear. You get the id_partner from a left out join lookup and this lookup does not return a value. The problem is the tMap join condition.

[Anonymous]

Hi jlolling,
This is johanglasses, I just found out that I have 2 accounts... anyway..
Then, I think I didn't understand perfectly how Talend works.
From my understanding an inner join or a left join can be performed only if you make a match/join between 2 fields such as the exemple in the screenshot below between row1 and row3 (row1.b2b_partner = row3.partner_name).
Still from my understanding, if you don't make any match/join with a table(tMysqlInput), then this table should only provide the result of his query which is in my case the MAX(partner_id).
Am I wrong?
Thank you again.

[Anonymous]

row2 is wrong configured. You want to have the always latest id_partner. In this case you have to take care the the input component must run with the same connection as the output component to have always the latest id.
The tMap cannot know how often you want to get the value. At the moment the component does not read the values at all.
You have to use a join or simply set the row2 input in the tMap in its option as "Read for each row".