Skip to Content
0

unable to generate sequence values in BODS

Jun 21, 2017 at 11:02 AM

149

avatar image
Former Member

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?

capture.png (9.3 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
Dirk Venken
Jun 22, 2017 at 06:15 AM
1

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jun 22, 2017 at 11:31 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jun 28, 2017 at 09:35 AM
0

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


capture.png (10.9 kB)
capture.png (11.3 kB)
capture.png (4.3 kB)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jun 29, 2017 at 10:19 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded