Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
Yes, you missed something. It is "Count" which needs to have double quotes around it and row1.count + 1 must not have them.
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?
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.
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
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
Many many thanks, JR. The job ran fine. The data looks good as expected.