Skip to Content
0

Need information on Generating unique row number

Nov 10, 2016 at 09:10 PM

65

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Devilal Rajappan Nov 11, 2016 at 12:25 AM
1

* 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.

Show 1 Share
10 |10000 characters needed characters left 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

0
Devilal Rajappan Nov 11, 2016 at 09:43 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded