cancel
Showing results for 
Search instead for 
Did you mean: 

unable to generate sequence values in BODS

former_member186160
Contributor
0 Kudos

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?

Accepted Solutions (1)

Accepted Solutions (1)

former_member187605
Active Contributor

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

Answers (3)

Answers (3)

former_member186160
Contributor
0 Kudos

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

former_member186160
Contributor
0 Kudos

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

former_member186160
Contributor
0 Kudos

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