Re: Can it be done in SQL
On Wed, 10 Jul 1996 08:55:16 -0400, tdenbow@mathworks.com (Toby
Denbow) wrote:
>A serial is not ok for line_number because I do not want line_number to be
>unique across multiple records.
>
>Order_header 100 could have 3 detail records associated to it. I want the
>first detail record to have line_number 1, the second record to have
>line_number 2 the third record to have line_number 3.
>(This is a simple parent-child relationship that is joined together by
>order_no.)
>
>Order_header 101 could have 2 detail records the first detail record would
>have line_number 1, the second line_number 2, etc....
>
>I am doing this within SPL and would prefer not to use a FOR loop, that is
>why I am trying to do it using straight SQL.
>
>
>I have a table that contains all of the products a customer has
>purchased. I'll call the table product_history. For simplicity
>product_history only has two fields, cust_id and product_id.
>
>I want to select product_id from product_history into an order_detail
>record that has three fields( order_number, line_number, and product_id)
>where cust_id = somevalue. I want line_number to start at 1 and
>increment by 1 for each detail record for that order. The line_number is
>unique within the order_number. (I already have the value for
>order_number.)
>
>
>Does this make sense? Can it be done in SQL?
I don't think you can do it in SQL. You can however do it in SPL.
Create a SP that you put in a pre-insert trigger.
The SP should look like this :
create procedure sequence ()
define global int i default 0;
if i =2147483647 then
let i = 1;
else
let i = i + 1;
end i;
end procedure.
If you execute this procedure with each transaction, it will generate
a unique sequence number (starting from 1) within the transaction.
Look out : you have to commit each group of insert operations on the
detail table ! Each order and its details must be commited seperatly !
Koen