Skip to Content
avatar image
Former Member

unable to generate sequence values in BODS

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Jun 22, 2017 at 06:15 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 22, 2017 at 11:31 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 28, 2017 at 09:35 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 29, 2017 at 10:19 AM

    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

    Add comment
    10|10000 characters needed characters exceeded