Skip to Content
avatar image
Former Member

Need information on Generating unique row number

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • 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

      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

  • 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