on 06-21-2017 12:02 PM
Hi All,
i am trying to generate sequence number in a bods job for one column.
below snapshots shows my input and my output while i tried to test with these functions , but of no use: key_generation(), gen_row_number()
my requirement is to get a sequential values generated for a column: like - 1,2,3
next run i need the values to start considering already inserted values in the table: so next run with same input should insert another 3 records with sequence values : 4,5,6
can anyone suggest me on this please?
Both methods work for me.
1/. Key_generation: define a PK constraint on the SEQ column in your target table.
2/. gen_row_num(): define a global variable, initialise it in a script $Start =sql('<datastore','select max(seq) from outputTable'); and use it in the mapping for SEQ: gen_row_num() + $Start
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Dirk,
your second suggestion works perfect , thanks.
i am closing this ticket.
i tried calling custom function and it was very bad in terms of performance [10X times] and updating the function property to parallel did wrong values to get added.
Regards,
swetha
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Dirk,
1. client is not allowing to create a primary key constraint on any column.
but still key_generation() - transform on the seq column works fine only if the seq column is int type.
2. but my requirement is not just to generate plain sequence numbers , but also to have some prefix as well. example
test01
test02
test03
so i used this below script in query transform :
'test'||(nvl(substr(sql('DS_datastore','select top 1 seq from DBO.TEST_SEQ_01 order by seq desc'),5,5),0) + 1)
but ended up getting repetitive values like below:
but expectation is :
calling a custom function for each row will also be a performance issue, and the issue would again arise due to row commit size and the above issue will still occur.
can you please suggest .
Regards,
Swetha
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Dirk,
thanks.
1. i will try this.
2. this may add overhead to the performance. i think since my rows per commit size is 5000, i need to try how it would populate.
i will try this as well.
regards,
Swetha
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.