How to generate sequence: using tJavaRow

[MaryL]

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?

[anoopbhargava]

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

[MaryL]

@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?

[Anonymous]

@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

[MaryL]

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

[Anonymous]

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

[MaryL]

@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? 

[Anonymous · Accepted solution]

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

[MaryL]

@shong thank you!! 

[MaryL]

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