automated sequence numbers
Moderators: chulett, rschirm, roy
automated sequence numbers
i am trying to load a file into a table...whenever i load each row i need to increment the value of the line num field by 1 and insert it into the line number field of the table ...how do i do this.
also how can i set default values ....most of the fields in the tables should be set to default ....
also how can i set default values ....most of the fields in the tables should be set to default ....
You mean if the row is already present in the table, get that line num and 1 or regardless? If anwer is latter then just add 1 in the transformer stage to that column.
Default values can be set at the database level, talk to your dba about it.
Default values can be set at the database level, talk to your dba about it.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
I suppose Surrogate Key Generator is in Parallel Job palette not in Server Job palette.DSguru2B wrote:In other words you need a surragate key. many ways to achieve this. There are out of the box key management routines or manage key generation on your own. Search the forum for posts on surrogate key generation.
Maybe, the least option is to create two Jobs for extracting the line num from the table and getting the Link Count of that Job(last Line Num) then use it in the next Job with the logic to increment by one.
(1) Use "KeyManagement" in Data StageFunctions that gives the Sequence
number from a table.
(2) If you want to create your own, have a lookup(hash file) that
has 2 columns
Key_Column= 'X'
Last_RowN0= select max(column) from the table
In your original job, do a lookup in #2 file and match with 'X' from source. That will give you the max number in the table. Then in Transformer or stage variable keep adding +1 to the number till the end of processing.
number from a table.
(2) If you want to create your own, have a lookup(hash file) that
has 2 columns
Key_Column= 'X'
Last_RowN0= select max(column) from the table
In your original job, do a lookup in #2 file and match with 'X' from source. That will give you the max number in the table. Then in Transformer or stage variable keep adding +1 to the number till the end of processing.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: automated sequence numbers
True database default values are achieved by not including the columns in your job.dstage443 wrote:also how can i set default values ....most of the fields in the tables should be set to default ....
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
If the Starting value is supplied by the user,ray.wurlod wrote:Why not just use @OUTROWNUM system variable, perhaps added to a constant (starting value) supplied as a job parameter?
1. Can we go with this solution into production?
2. How user determines the last record line number in the table?
Please clarify these questions.
If the Starting value is supplied by the user,ray.wurlod wrote:Why not just use @OUTROWNUM system variable, perhaps added to a constant (starting value) supplied as a job parameter?
1. Can we go with this solution into production?
2. How user determines the last record line number in the table?
Please clarify these questions.
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
I believe that by using @INROWNUM, he is trying to restart the sequence of the numbers from 1 through however number of rows he reads from the source.ag_ram wrote:Can you please let me know how did you do that?dstage443 wrote:@INROWNUM or @OUTROWNUM was the one that worked for me
Do you have the value of the Job parameter to mean the last line number which is supplied by the user or extracted from the table by any other job?
I also think that if it is only inserts all the time, then generating a sequence number at the Database level provides a viable solution!
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted