Re: SPL to replicate a row with serial key

Hi, typically when you insert into a serailcolumn you use a value of 0. The serial value is then generated for you. Therefore, you should omit the passing of onum (or pass in 0 all the time). You probably want to return the inserted serial value (check out dbinfo). -Bill } } I, Jake Salomon, just posted this procedure and the errors involved: } } > } > Hi Family. } } >create procedure } > dup_order (inum like orders.order_num, -- order number to duplicate } > onum like orders.order_num) -- order to create } > } > insert into orders } > select onum, -- selecting variable's value, not a column name } > order_date, } > customer_num, } > ship_instruct, } > backlog, } > po_num, } > ship_date, } > ship_weight, } > ship_charge, } > paid_date } > from orders } > where order_num = inum; } > end procedure; } } .... } > execute procedure dup_order(1001, 2001); } > } > The error message I get: } > 670: Variable(order_num) declared as SERIAL type. } > } > Huh? } > } > Does this mean it would work if a serial column were not involved? } > } >Besides reading it all into variables (a tedious procedure), is there a } >way to get this done with finesse? By the time y'all answer me, I will } >probably have done it with the brute force of variables. But I'd like } >to know because I will be doing this for other tables. } } Well, I tried it with the brute force, with variables. Here it is: } } create procedure } dup_order (inum like orders.order_num, } onum like orders.order_num) } } define v_order_date like orders.order_date; } define v_customer_num like orders.customer_num; } define v_ship_instruct like orders.ship_instruct; } define v_backlog like orders.backlog; } define v_po_num like orders.po_num; } define v_ship_date like orders.ship_date; } define v_ship_weight like orders.ship_weight; } define v_ship_charge like orders.ship_charge; } define v_paid_date like orders.paid_date; } } select order_date, } customer_num, } ship_instruct, } backlog, } po_num, } ship_date, } ship_weight, } ship_charge, } paid_date } into v_order_date, } v_customer_num, } v_ship_instruct, } v_backlog, } v_po_num, } v_ship_date, } v_ship_weight, } v_ship_charge, } v_paid_date } from orders } where order_num = inum; } } insert into orders } values (onum, } v_order_date, } v_customer_num, } v_ship_instruct, } v_backlog, } v_po_num, } v_ship_date, } v_ship_weight, } v_ship_charge, } v_paid_date); } } end procedure; } } YUCHH! I know it would be tedious! But it would be worth it if it would } at least work. Alas, I get the identical error message about.. } 670: Variable(order_num) declared as SERIAL type. } } And BTW, listing the columns in the INSERT statement didn't help either. } } Now... Any ideas? } } Thanks much! } -- } -- Jake (Currently wishing to be an undomesticated aquatic avian) } } +----------------------------------------------------------+ } |Aside from that, how did you enjoy the play, Mrs. Lincoln?| } +----------------------------------------------------------+ } -- Bill Ennis Voice: 312-474-7516 SSA Fax: 312-474-7460 500 W. Madison email: ennis@ssax.com ennis@accesschicago.net