[resolved] How to add a Numeric.sequence across the entire job.

[Anonymous]

I have a two part job, the first part loads the data from database into another. The second part of the job I load data from an Excel file into the same database. However, I need a Unique Global Identifier that increments on every new row. In the first part of the job i use Numeric.sequence in a Variable in the tMap. How do i call this Variable again in a new tMap? Or is there a better way of handling this type of action?
Pic of Entire Job


Pic of the 1st tMap

Pic of the 2nd tMap


Thank you,
David

[Anonymous]

Open to any suggestions on how to do this or alternative methods. The backend is MSSQL 2008.

[Anonymous · Accepted solution]

Hi
Redesign your job to :
tMSSQLInput_1--row1-->tMap_2--row2-->tMSSQOutput_1--row3-->tJavaRow_1-->tFlowToIterate--iterate-->tExcelInput--main-->tMap_3---main-->tMSSQLOutput_2
on tJavaRow1, put your sequence var to global variable:
globalMap.put("PATIENT_ID", row3.PATIENT_ID);
global.put("PATIENT_ID2", row3.PATIENT_ID2);
on tMap3, get the value of global variable:
(Integer)globalMap.get("PATIENT_ID")

Best regards
Shong

[Anonymous]

Thanks for the reply Shong. However, I did redesign the job but it took forever to run. The table i have to import from has over 2 million records so this design won't work.
How would you instead do a call to database to get the last id generated and place that in a variable (e.g. "GUID") where it can be placed into a new Numeric.sequence("s1",GUID,1)?

[Anonymous]

If the two source are both inserted in the target database as new records, you just have to "continue" the sequence in the second subjob from where you left... just take ( in a context var with a java row ) the last inserted value from the first subjob.
If instead the two sources are one inserted and then the second excel file is used for updating, of course you need some id/key to correlate the two sources...

[Anonymous]

Hi Emaxt6,
thanks for the reply.
Since i'm still new to Talend, how does one "just take ( in a context var with a java row ) the last inserted value from the first subjob." exactly?

[Anonymous]

Well, I recalled that the behaviour of Numeric.sequence("s1",1,1) if used in the same job, is just to continue from the last number.... so you need to just use Numeric.sequence("s1",1,1) in both tMap... it will just continue where you left, the second parameter doesn't matter on subsequent calls....
hope it helps

[Anonymous]

thank you emaxt6,
That was easy... thank for the reply!