Re: Can it be done in SQL

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? -Toby Denbow tdenbow@mathworks.com In article <4s048f$bc7@newsgate.dircon.co.uk>, windata@dircon.co.uk (Terry Boyland) wrote: > In article <tdenbow-0907961153010001@tdenbow.mathworks.com>, tdenbow@mathworks.com (Toby Denbow) says: > > > >I want to insert some rows into a detail table. The question is how can > >I, in SQL, create line numbers for the rows being inserted without > >hardcoding them? > >I could probalby do it with a stored procedure but would prefer using SQL. > > > >For example I want the_line_number to be incremented for each row os that > >line_no is a valid line_no. > > > >EXAMPLE: > >insert into line_no, col1, col2 > >select the_line_number, table.col1, table.col2 from table where col2 = "a"> > > > > >Please reply via email if you can. > > > >Thanks, > >Toby Denbow > >tdenbow@mathworks.com > > > >== Toby Denbow ========================= tdenbow@mathworks.com == > > The MathWorks, Inc. info@mathworks.com > > 24 Prime Park Way http://www.mathworks.com > > Natick, MA 01760-1500 ftp.mathworks.com > >==== Tel: 508-647-7243 ========================================== > > Toby > > I've seen a few answers one of which won't work because it selects and inserts > into the same table. > > A serial would cure the problem but here the line numbers will be chosen for > you. Is this OK ? > > Otherwise are you able to use 4GL where you can produce a string for execution > Let me know > > Terry == Toby Denbow ========================= tdenbow@mathworks.com == The MathWorks, Inc. info@mathworks.com 24 Prime Park Way http://www.mathworks.com Natick, MA 01760-1500 ftp.mathworks.com ==== Tel: 508-647-7243 ==========================================