Skip to Content
avatar image
Former Member

Need information on Generating unique row number

Nov 10, 2016 at 09:10 PM | 73 Views

Hello All,

My requirement is to generate unique row numbers for a column ROW_ID.

I used GEN_ROW_NUM function to generate row numbers, it generated a sequence say from 1-1000. However, when I tried to load a new file, It again generates the row number starting from 1.

This column ROW_ID value should start with the previous maximum count of column ROW_ID and then raise the counter by 1.

Similarly should continue while loading other files.

Can any one please advise me on this requirement?

Regards,

Rajiv

SAP Data Services
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • avatar image
    Devilal Rajappan
    Nov 11, 2016 at 12:25 AM

    * Stage the output to a table and use key generation transform. Taking this stage table as a source generate file.
    * Lookup the previous flat file and get the latest row_id.
    * Store the max(row_id) during each load to a temp table and refer it during next loads.

    May be some other options, but these are the ones which came to my mind now.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member
      Nov 11, 2016 at 12:15 PM

      Hello Devilal,

      I am loading the flat file data which includes field ROW_ID field into a permanent table.

      You mean to say that I need to use this permanent table now as a source for key generation transform, this will generate the value starting from 1 and load the result in a temp table.

      Will also store the max(Row_ID) from the temp table into a global variable.

      But what happens when I again try to reload a new file, the new records will be inserted into the permanent table(which is set as do not delete data before loading) then again this table will act as a source for key generation transform.

      But how it is going to generate the values for field ROW_ID from the previous MAX(ROW_ID) where the value are blank

      Could you please explain on this?

      Regards,

      Rajiv

  • avatar image
    Devilal Rajappan
    Nov 11, 2016 at 09:43 PM

    If you are loading file to a permanent table then like you mentioned, use this table in key generation transform. Refer this link for more details.

    Add comment
    10|10000 characters needed characters exceeded