Incrementing, from 1, based on another column
muffinbubble
Posts: 2 New member
Hi, I have a table which simplified is a sales orders lines table, with two columns, a reference to the sales order (this is a FK) and an order line. The order line column should increment from 1 to x but be unique to that sales order ID.
For example:
SalesOrderID OrderLine
1 1
1 2
1 3
2 1
2 2
3 1
And so on. Currently the order line is totally random and doesn't start from 1. I can set a min/max but it needs to start from the SalesOrderID.
Is this possible? So in effect, the logic for each generated line will ask "Is this a new sales order ID, therefore the OrderLine will generate 1. Further down I randomly generate the same sales order ID, but as a row already exists once with an OrderLine of 1, increment 1 to it, so the OrderLine becomes 2. If a new sales order ID is generated which doesn't exist, we start from 1 again.".
Edit: I did try a generic Python script but it seemed to only allow you to run tests on that column and not get values of others.
For example:
SalesOrderID OrderLine
1 1
1 2
1 3
2 1
2 2
3 1
And so on. Currently the order line is totally random and doesn't start from 1. I can set a min/max but it needs to start from the SalesOrderID.
Is this possible? So in effect, the logic for each generated line will ask "Is this a new sales order ID, therefore the OrderLine will generate 1. Further down I randomly generate the same sales order ID, but as a row already exists once with an OrderLine of 1, increment 1 to it, so the OrderLine becomes 2. If a new sales order ID is generated which doesn't exist, we start from 1 again.".
Edit: I did try a generic Python script but it seemed to only allow you to run tests on that column and not get values of others.
Tagged:
Answers
From the information you have provided, I cannot identify a method to achieve your goal using the generators provided within the tool.
One possible solution, would be to create a csv file or SQL Table that can be used as a reference for the example data you wish to generate.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com