Generation of Surrogate key for Incremental Load

[Anonymous]

Hi,

I have a column in a table in table for which am generating a surrogate value by using Numeric.sequence("S1",1000,1). This works fine for the 1st time but for the second time, I want it to  take max value form the last generated surrogate value. Here I am taking the max of that column into a context variable and using in the tmap Numeric.sequence("S1",Max(Column_Name),1).

Here I am facing an issue. Context variable is not accepting the int value in it. I am getting error.

Can you please help me solve this issue.

Thanks in advance

ShaX

[manodwhb]

@ShaX ,in th pre job you need to take the max key form the target table and load to context using tcontextload.you can use that context value this way in downstream.

Numeric.sequence("S1",context.DIm_DW_CUSTOMER_ID,1)

[vapukov]

Hi,

it absolutely not clear from the description - what you really do?

in any variants, current form:

Numeric.sequence("S1",Max(Column_Name),1)

is wrong

if you store max value (not seen - how) to context variable, it must be:

Numeric.sequence("S1",context.CONTEXT_VARIABLE_NAME,1)

no MAX(), context. - mandatory

add:

as the default value for context variable use 1, it resolve null value warning

[Anonymous]

Hi Manohar, I am trying to do the same. but my question is : 1. If I declare variable as int am getting "Null value will be used for context parameter DIM_DW_CUSTOMER_ID: For input string: "context.DIM_DW_CUSTOMER_ID"" this error. 2. If I declare variable as String then am getting "string to int conversion error" as the column am populating is Int Regards Sharukh

[Anonymous]

Hi Vapukov,
My mistake I wrote that
Actually Numeric.sequence("S1",context.DIM_DW_CUSTOMER_ID,1) is the one am using.

[manodwhb]

@ShaX ,yes As part of initialization,if you have not specified then it will take as Null and when the prejob completed successfully that max value would be stored in memory for that context and would be used in downstream.

[vapukov]

change the initial context variable value to 1, type INT

in query use CASE for return 1 if it first iteration and max(column) for any other

store to context max(column) + 1

must work

[Anonymous]

Hi,

I got the weird result, here my max value is 1900, so it has to start from 1901 instead it is starting from 1190.

I got the below error in talend. and I have only 25353 rows instead of 35353 rows

[vapukov]

---

@ShaX wrote:

Hi,

I got the weird result, here my max value is 1900, so it has to start from 1901 instead it is starting from 1190.

I got the below error in talend. and I have only 25353 rows instead of 35353 rows

---

it is not seen - what's wrong (it is work definitely) 

diffrerence for 10 000 - because database rollback all batch (10k default batch size in talend) with any error

[Anonymous]

Hi @vapukov 

Can you please explain how to over come this. caz i tried many ways but I am not understanding where am doing it wrong.

I think the issue is with the Variable which I am declaring as Int.

Am getting above error.

Regards

ShaX

Regards

ShaX