Look up to get max id

[A_San]

New to talend. I am trying to load a table say stg0 from a source file. one of the columns in target is batch_id. For every run, the batch id should increment by 1. 

For ex: If I load file 1 and later file 2, the batch id should be 1 for file 1 and 2 for file 2. So I should look up on the STG0 table and get the max batch id and increment by 1. I am trying to use tmap to achieve this. My main connection to tmap is the source delimited file and look up table is STG 0. In tmap, I cannot join between source file and stg0. I am not sure how to go about this. Any help would be appreciated.

[JR1 · Accepted solution]

OK, here we go.

I have recreated the job as a rudimentary structure. In a first step, you create a tDBInput and put in the "max" statement. The schema of this component must just be "count" of type "Integer" (feel free to chose a different column name). If your table contains a lot of records, executing this query may take a while - use the usual query optimisation techniques like indexes in this case (outside this job).

Then you store this max value in a global variable using tSetGlobalVar (increased by one). Name it "count" or whatever.

Then you can use this global variable in your tMap (tMap_1 in the picturre above) and all the records will get the same value for "batch" (which is what you want)

Let us know, if this solves your problem.

[A_San]

JR,

I did as you said. Not sure if I missed something. I am getting some errors. Please see screenshot.

Appreciate all your help. 

SS4.PNG
SS3.PNG
SS5.PNG

[JR1]

Yes, you missed something. It is "Count" which needs to have double quotes around it and row1.count + 1 must not have them.

[A_San]

Thank you JR.

Could you also please let me know why I am getting "Type mismatch: cannot convert from char to String" error in tMap?

[JR1]

Sorry, I missed that. Sure: change the 'S' to "S" for column Id_stus. Single characters in single quotes are treated as Char in Java, whereas double quotes define a String. Basically, you tell Talend to put a Char value ('S') into a String column.

[A_San]

I am gettting this error now. Reason could the table is empty. How and Where do I say that if max(batch_id) is null, then count should be 1 to start with?

Exception in component tSetGlobalVar_1 (STG_0_LD_CNTL_NMCP)
java.lang.NullPointerException

[JR1]

How about modifying your SQL statement so that it always returns at least 1. If you are using PostgreSQL, you could try with the following (not a PostgreSQL expert):

SELECT COALESCE(MAX(batch_id), 1) FROM XXXX

COALESCE always returns the first value of the parameters that is not null.

If this does not work, you could put a tMap after the "count" tDBInput and set the value to 1 by using the following expression

row1.count == null ? 1 : row1.count

[A_San]

Many many thanks, JR. The job ran fine. The data looks good as expected.