SPL to replicate a row with serial key
Hi Family.
I need to replicate rows in a table, with the new row differing only in
the value of the serial primary key. Before going for it whole hog I
decided to test it on my little stores database. Here is the procedure:
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;
Interestingly enough, this passes syntax checking. When I the SQL to
execute the procedure, it does not complain that "column onum is not in
any table in the query". This possible misinterpretation was my main
concern when writing this little test.
However, it DOES get an interesting error:
In the execution below, I am trying to create order # 2001 looking
exactly like order 1001, differing only in the value of the primary key.
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.
Thanks.
--
-- Jake (In pursuit of undomesticated aquatic avians)
+----------------------------------------------------------+
|Aside from that, how did you enjoy the play, Mrs. Lincoln?|
+----------------------------------------------------------+