topic Re: Get MAX(index) FROM table [tMysqlRow] in Talend Studio

Get MAX(index) FROM table [tMysqlRow]

Anonymous — Tue, 22 Dec 2015 08:00:20 GMT

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

Re: Get MAX(index) FROM table [tMysqlRow]

Anonymous — Tue, 22 Dec 2015 10:20:02 GMT

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:

Re: Get MAX(index) FROM table [tMysqlRow]

Anonymous — Wed, 23 Dec 2015 03:33:28 GMT

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.

Re: Get MAX(index) FROM table [tMysqlRow]

Anonymous — Wed, 23 Dec 2015 12:42:37 GMT

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.

Re: Get MAX(index) FROM table [tMysqlRow]

Anonymous — Wed, 06 Jan 2016 01:50:56 GMT

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.

Re: Get MAX(index) FROM table [tMysqlRow]

Anonymous — Wed, 06 Jan 2016 07:42:56 GMT

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

Re: Get MAX(index) FROM table [tMysqlRow]

Anonymous — Wed, 06 Jan 2016 08:23:41 GMT

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?

Re: Get MAX(index) FROM table [tMysqlRow]

Anonymous — Wed, 06 Jan 2016 08:25:40 GMT

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.

Re: Get MAX(index) FROM table [tMysqlRow]

Anonymous — Fri, 08 Jan 2016 04:04:15 GMT

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.

Re: Get MAX(index) FROM table [tMysqlRow]

Anonymous — Mon, 11 Jan 2016 14:36:52 GMT

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

Re: Get MAX(index) FROM table [tMysqlRow]

Anonymous — Tue, 12 Jan 2016 03:52:53 GMT

Yes indeed, I am sorry, I forgot to tell you that I changed the Lookup Model as "Reload at each row" (screenshot below).
But I am still facing the same error.
The third screenshot is just to confirm that I have data in the table so I am not suppose to get any java NullPointerException.
Thank you for following me up on this issue.

Re: Get MAX(index) FROM table [tMysqlRow]

Anonymous — Thu, 21 Jan 2016 00:52:14 GMT

Hi @jlolling,
Any update on my issue ?
I looked into it, but I don't understand why this error is raised.
I added to my previous post 3 other screenshot for each tMysqlInput.
Maybe it will helps to understand that issue.
Thx.