Generate sequential number for inserts

johndeere · Post by **johndeere** » Sat Dec 04, 2004 9:20 pm

I am inserting detail records into a table. I need to generate an increasing number for each detail that relates to the same master ID. An example would be orders. Each set of order details would have a set of sequence or line numbers. Order #1 has details 1,2,3 and order #2 has details 1,2,3.

Thanks for your help.

ray.wurlod · Post by **ray.wurlod** » Sat Dec 04, 2004 11:46 pm

Welcome aboard! :D

There are several solutions to this problem. If you've already loaded the master records, then you can load a hashed file with each master key. A non-key column could store the "next value" for each master key.

Indeed, you could even load them into the SDKSequences hashed file used by the KeyMgtGetNextValue routine. This might be the easiest approach for you. The routine already exists; you would not have to write one.

You could use stage variables, detecting change in the master key value and resetting or incrementing a counter as appropriate.

You could load the detail records into a UV table normalized on the detail information and use a synthetic key (@ASSOC_ROW) to generate the detail row identifiers.

These ideas will give you something to get started.

Sunshine2323 · Post by **Sunshine2323** » Sun Dec 05, 2004 4:56 am

Hi!

I have already done this by the use of Stage Variables.

I have 2 stage Variables svChkDup and svCOUNTER

Derivation for svChkDup
IF RowProcCompareWithPreviousValue(lnkINPUT.OrderNo) THEN "Y" ELSE "N"

Derivation for svCOUNTER
IF svCHKDUP="N" THEN 1 ELSE svCOUNTER +1

Now u just have to insert svCOUNTER stage variable into the derivation for the Details No.

Hope this Helps

Sunshine

johndeere · Post by **johndeere** » Sun Dec 05, 2004 7:04 am

Thanks for the help. I am very new to the tools and I have noever used a hash file but I will start looking at them.
I will also research stage variables.

Thanks again.