topic Re: Store Max ID using tJava and use it in tMap in Talend Studio

Store Max ID using tJava and use it in tMap

Amit4 — Mon, 10 Jun 2024 11:14:50 GMT

Hi @Shong_888 ,

I am storing max(ID) from query and in tJava.

Which I am trying to use and max(ID)+1 in

Inside tjava:

I am struggling to use it in tMap. How can I do that?

Please help!

Thanks in advance!

Amit

Re: Store Max ID using tJava and use it in tMap

Amit4 — Mon, 10 Jun 2024 11:58:00 GMT

If we can do in any other way to store the max id and use it in tMap. Idea is welcomed.
Thanks!

Re: Store Max ID using tJava and use it in tMap

Amit4 — Tue, 11 Jun 2024 08:53:04 GMT

@root

@JR1

@Shicong_Hong

Can you please help in making it successful.

Thanks in advance.

Re: Store Max ID using tJava and use it in tMap

Shicong_Hong — Wed, 12 Jun 2024 02:08:20 GMT

@Amit4 use tJavaRow instead of tJava, store the max(ID) to a context variable for used later on other components.

tJavaRow code:

context.varName=input_row.MAX_ID;

Re: Store Max ID using tJava and use it in tMap

Amit4 — Wed, 12 Jun 2024 09:01:12 GMT

Hi @Shicong_Hong ,

I applied the logic in tJavaRow

But How can we use that variable in tMap output. Also I want it to be in the incremental form. If ID comes as 100 then it should pass as 101 in the output table.

Thanks

Re: Store Max ID using tJava and use it in tMap

Shicong_Hong — Wed, 12 Jun 2024 09:34:03 GMT

change the code to

context.MaxID=input_row.MAX_ID+1;

and use the context variable directly in the expression field of column.

Re: Store Max ID using tJava and use it in tMap

Amit4 — Wed, 12 Jun 2024 11:19:32 GMT

Thanks but My ID column is of Bigdecimal data type upon adding 1 it is getting failed which is legit.
How can we deal with this data type issue.

Re: Store Max ID using tJava and use it in tMap

ThWabi — Wed, 12 Jun 2024 11:43:08 GMT

Hello Amit,

I use the tSetGlobalVar component to store a (initial) max id value, and globalMap.get(...) to retrieve it. (I have an Integer data type that is the start value for a Numeric.Sequence(...).)

Best regards,

Thomas

Re: Store Max ID using tJava and use it in tMap

Shicong_Hong — Thu, 13 Jun 2024 09:34:17 GMT

If ID column is of Bigdecimal data type in output table , use this code in the expression field of column on tMap.

new java.math.BigDecimal(context.MaxID)

Re: Store Max ID using tJava and use it in tMap

Amit4 — Thu, 13 Jun 2024 10:58:46 GMT

@Shicong_Hong

Source & Target both table is same and ID column is of BigDecimal data type.

If I use context.MaxID=input_row.MAX_ID+1 in tJavarow, it is throwing error because of integer addition to BigDecimal.

We need handle it before and then use it later in tmap

Re: Store Max ID using tJava and use it in tMap

Shicong_Hong — Thu, 13 Jun 2024 12:17:45 GMT

Define the context variable with Bigdecimal type, try this code on tJavaRow:

context.MaxID=input_row.MAX_ID.add(new java.math.BigDecimal(1));

Re: Store Max ID using tJava and use it in tMap

Amit4 — Fri, 14 Jun 2024 09:52:31 GMT

@Shicong_Hong

Thanks it worked well but there is another problem, The whole case is I have to insert the new record into the map table if it's already not there which is coming from Stage table. If there are multiple new records then it is giving same ID for all records. This we need to handle. Below screenshot you can see.

Max(ID) in Map table is 248 so it Inserted 249, but 3 new records came all got assigned the same, it should go as 249, 250, 251.

Re: Store Max ID using tJava and use it in tMap

Amit4 — Sat, 15 Jun 2024 15:18:29 GMT

I used the Numeric sequence is variable an and used in map.

Var.varIncrementedID = context.MaxID.add(new BigDecimal(Numeric.sequence("s1", 1, 1)))

Now it's working as expected.

Thanks,

Amit