how to create unique sequence no with reference to primary

pxraja · Post by **pxraja** » Thu Sep 18, 2008 5:41 am

Hi all,

requirement is like this,

Unique numbers should be generated based upon the composite primary keys. for example,

Inlink.Field1 Inlink.Field2 Inlink.Field3 Inlink.Field3
65465 'kjdjfsak' 5645 'kjdfoi'
68845 'doewlks' 8556 'dkgjioe'
65465 'eujdskd' 6455 'doewl'

Outlink.Field1 Outlink.Field2 Outlink.Field3 Outlink.Field4 Outlink.Field5
1 65465 'kjdjfsak' 5645 'kjdfoi'
2 78845 'doewlks' 8556 'dkgjioe'
3 65465 'eujdskd' 6455 'doewl'

where Inlink.Field1 and Inlink.Field3 are primary keys
and Outlink.Field1 is the Unique Field

any ideas are welcome

Thanks in Advance

brvishwas · Post by **brvishwas** » Thu Sep 18, 2008 6:37 am

Use @inrownum function in the transformer.

DS_SUPPORT · Post by **DS_SUPPORT** » Thu Sep 18, 2008 6:40 am

Use the routine KeyMgtGetNextValueConcurrent for getting Sequence number.

Do whatever mechanism in your job to identify the unique string, use order by in your sql , and use stage variables to identify the uniqueness, or use a lookup to idetify the uniqueness.

and for the unique value, just call the routine as KeyMgtGetNextValueConcurrent('whateverstring') , this will give you the unique sequence number.

ray.wurlod · Post by **ray.wurlod** » Thu Sep 18, 2008 7:10 am

I'd prefer to use the system variable @OUTROWNUM, particularly if you're using a constraint expression on the output.

pxraja · Post by **pxraja** » Thu Sep 18, 2008 10:53 pm

DS_SUPPORT wrote:Use the routine KeyMgtGetNextValueConcurrent for getting Sequence number.

Do whatever mechanism in your job to identify the unique string, use order by in your sql , and use stage variables to identify the uniqueness, or use a lookup to idetify the uniqueness.

and for the unique value, just call the routine as KeyMgtGetNextValueConcurrent('whateverstring') , this will give you the unique sequence number.

sequence should start with 1 and its should be incremented by 1 when using the KeyMgtGetNextValueConcurrent('Whateverstring'), will it provides incremental value from the last sequence? how to get the value of the last sequence and on the next run the sequence should start with last sequence value +1

Any suggestions are welcome

Thanks in Advance

DS_SUPPORT · Post by **DS_SUPPORT** » Thu Sep 18, 2008 11:15 pm

Yes, Your sequence number will start with 1, and for the next run also, it will give you the next max number. You dont have to do any extra settings for achieving this.

But when using, consider changing 'whateverstring' to some meaningful string.

pxraja · Post by **pxraja** » Fri Sep 19, 2008 1:44 am

DS_SUPPORT wrote:Yes, Your sequence number will start with 1, and for the next run also, it will give you the next max number. You dont have to do any extra settings for achieving this.

But when using, consider changing 'whateverstring' to some meaningful string.

While running a new job with KeyMgtNextValueConcurrent(1), the sequenct number is starting with lastMax Sequence used in some other job. How to make the new job to start with the sequence starts with 1 and how to maintain the sequence for that particular Job.

I want to use KeyMgtNextValueConcurrent(1) for Job wise, but it is taking the value from project wise.

Any suggestions are welcome

Thanks in Advance

ray.wurlod · Post by **ray.wurlod** » Fri Sep 19, 2008 1:47 am

Use a different sequence name. The sequence name is the argument to this routine. You are using a sequence named "1" in both jobs. Don't.

DS_SUPPORT · Post by **DS_SUPPORT** » Fri Sep 19, 2008 2:42 am

Use KeyMgtNextValueConcurrent(YOURJOBNAME), so it will maintain sequence based on each job.

pxraja · Post by **pxraja** » Fri Sep 19, 2008 2:59 am

Hi All,

I had used seq(1) got into a stagevariable, and used that stagevariable in KeyMgtNextValueConcurrent(StgVar) it's getting sequence starting from 1..

Its resolved..

Thanks for your suggestions.