How can we have auto increment with static length of size

[Anonymous]

Hi All,

I am trying to configure auto increment to my output in the format of W001,W002,W003,......,W100,W101 etc.

I am able to achieve only W1,W2,W3 using numeric sequence i.e "W"+Numeric.sequence("",1,1).

But i wanted in the static size format W001,W002,W003,......,W100,W101 etc.

Can someone please suggest how can this be achieved this using TOS 7.1

[manodwhb]

@pabburi ,check the below example.

[vapukov]

Hi,

this is possible to do with tMap:

just define in a middle part of tMap (type string) variable (in my example V_SEQ)

Numeric.sequence("s1",1,1).toString()

and then use on the right part:

StringHandling.LEN(Var.V_SEQ)<3?
"W"+StringHandling.STR('0',3-StringHandling.LEN(Var.V_SEQ))+Var.V_SEQ
:"W"+Var.V_SEQ 

[Anonymous]

Thanks for the needful information @vapukov  this is what exactly i was looking for.

[Anonymous]

Hi @vapukov ,

The approach you shared  holds good when its a one time load,but in cases of incremental load of the column defined as primary key it fails because the values are again starting from W00001(Attaching the screenshot for reference).

In my case the auto increment with static length of size should go with incremental as well i.e the next time i do the the load the increment value should start from previous ended value.

Could you please share a solution hot this can be achieved.

Incremental.PNG

[vapukov]

Hi,

this is a good example why for "partitial question" is possible only "partial answer" 🙂

btw:

• read max value from db
• clean from W - use .replaceAll("W","")
• convert to integer 

Integer.valueOf(your_max_id.replaceAll("W",""))

• increment result for 1

and use final value for sequence again 

[Anonymous]

Hi @vapukov ,

I am new to TOS and the above solution is not clear.

Could you please explain each step more elaborately or if there is any bog which would suffix my requirement then please share 

[vapukov]

Hi,

just ask - what is not clear? 

• read max value from DB - because you add requirements to increment ID with future executions, first you need request max id from the database.
  Standard way for request data from database and store result to variable - tDBInput -> tJava/tJavaFlex/tJavaRow (or tFlowToIterate to auto)
• clean from W - method included into example ( .replaceAll("W","") )

in my example:

Integer.valueOf(your_max_id.replaceAll("W",""))

you just need to replace your_max_id to values from 1st step +1

your_max_id could be like

(Integer)globalMap.get("your_variable_name")

and as result final code for new id value like:

Integer.valueOf(((Integer)globalMap.get("your_variable_name")).replaceAll("W",""))+1

at this moment you have a starting value for the new sequence (instead of 1 as default), you need again store new value to the variable and use it in sequence definition

Numeric.sequence("s1",1,1)

have 3 parameters:

• name - could be any
• start value - this is what all above about
• increment, default 1

regards,

[Anonymous]

Hi All,

From the solution shared  i was table to understand i.e

1)we have to get the max (PK) from the table 

2)Assign the output to a variable

3)Use this variable in the incremental job 

So to do i have started off with using tdbinput>>tjava

In tdbinput sql is "select max(Key) from Tablename"

Here on, what code to be used in tjava component and how the output to be stored in variable is not clear to me.

can some one please share the job design of storing a sql result in a variable.

[TRF]

Sorry, I didn't saw the preceding answers.

You can do that with the following expression:

 
  String.format("W%04d", Numeric.sequence("s1", 1, 1)) 
 

Here is the result:

 
  [statistics] connected
newLine|W0001
newLine|W0002
newLine|W0003
newLine|W0004
newLine|W0005
[statistics] disconnected