topic Re: How to generate sequence: using tJavaRow in Talend Studio

How to generate sequence: using tJavaRow

MaryL — Tue, 10 Mar 2020 13:30:14 GMT

Hello!
I'm trying to generate the sequence following this steps from topic:

1) Create context parameter sequence_max_value.

2) Get Max value of sequence column.

    example : SELECT max(sequence_column) AS sequence_max_value FROM table_name;

    In tJavaRow check if query returns null then assign context.sequence_max_value=1 other                                                           
wise context.sequence_max_value=input_row.sequence_max_value(This is max value from table);

3) In the tmap use numeric.sequnce("s1",context.sequence_max_value,1) expression.

Note : If you want you can also use ISNULL function in query and assign value 1.

But run into some problems. Could someone please help?
1) in this section:

"check if query returns null then assign context.sequence_max_value=1 other
wise context.sequence_max_value=input_row.sequence_max_value(This is max value from table)"

How write it using java?

2) And don't clearly understand where to put tJavaRow.

Before tMap and attached it to tDBInput2?

Re: How to generate sequence: using tJavaRow

anoopbhargava — Tue, 10 Mar 2020 17:16:54 GMT

Hi ,

Check if you can use it in your SQl query , so if the max(sequence_column) is null , this will return 1. So that there is no necessary for you to write any java.

SELECT coalesce(max(sequence_column),1) AS sequence_max_value FROM table_name

Re: How to generate sequence: using tJavaRow

MaryL — Tue, 10 Mar 2020 19:41:33 GMT

@anoopbhargava thanks!
ok, I will change tDBInput2 (Max value of sequence query). Then link tDBInput2 to tMap... but still confused about this part:

context.sequence_max_value=input_row.sequence_max_value

Where I need to match them without tJavaRow? It can be done in tMap? Maybe in Var section?

Re: How to generate sequence: using tJavaRow

Anonymous — Wed, 11 Mar 2020 08:14:19 GMT

@MaryL, this part get the max value from DB and assign it to a context variable which is used later on tMap, for example:
tDBConnection
|onsubjobok
tDBInput1--main--tJavaRow
|onsubjobok
tDBInput2--main--tMap.....

Use onSubjokOK to control the execution order of each subjob, it makes the job looks more readable.

Regards
Shong

Re: How to generate sequence: using tJavaRow

MaryL — Wed, 11 Mar 2020 11:42:43 GMT

@shong thanks!

changed job as you have suggested, but tJavaRow have an error:

sequence_max_value cannot be resolved or is not a field

tJavaRow:

tDBInput1 (linked to tJavaRow):

I tried change in tDBInput1 (linked to tJavaRow) Schema from Build-in to Repository - it didn't help.

Re: How to generate sequence: using tJavaRow

Anonymous — Thu, 12 Mar 2020 03:38:17 GMT

Hi
Make sure you have defined one column called sequence_max_value on the schema of tDBInput_1.

Re: How to generate sequence: using tJavaRow

MaryL — Thu, 12 Mar 2020 12:14:58 GMT

@shong
hi thanks a lot! Job was successful, hurray!
But in Basic Run was this:

Null value will be used for context parameter sb_pdl_port_sequence_max_value: For input string: ""
[statistics] connecting to socket on port 3734
[statistics] connected
1
[statistics] disconnected

It's ok?

Re: How to generate sequence: using tJavaRow

Anonymous — Fri, 13 Mar 2020 08:15:27 GMT

The data type of context variable sb_pdl_port_sequence_max_value should be int/integer, and set its default value to 0.

Re: How to generate sequence: using tJavaRow

MaryL — Fri, 13 Mar 2020 09:40:58 GMT

@shong thank you!!

Re: How to generate sequence: using tJavaRow

MaryL — Fri, 13 Mar 2020 09:42:56 GMT

If someone need to generate sequence without using tJavaRow - good solution.