Followup: SPL to replicate a row with serial key
Hi folks,
I still don't understand the error I was getting but the original INSERT
statement was flawed anyway.
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;
Since posting the original call for help last night, I have had a chance
to sleep on it. Amazing, what what solutions come to mind after a
night's sleep. (I do not work for an investment bank. ;-)
When I tried this in a shell script, I got the error message "Cannot
insert/update table used in subquery" or something like that. I have
abandoned the stored procedure business here and I am taking the shell
script path.
#!/usr/bin/ksh
# dup_order.sh - Replicate an order onto another order number
# Usage: dup_order.sh original new_num (or 0)
#
if [ $# -lt 1 ] -- Some little bit of parameter checking
then
echo Usage: $0 original \\[new\\]
exit 1
fi
orig="$1"
newt=${2:-0}
dbaccess yutz - <<%%
select $newt new_order_num,
order_date,
customer_num,
ship_instruct,
backlog,
po_num,
ship_date,
ship_weight,
ship_charge,
paid_date
from orders
where order_num = $orig
into temp tmp_ordl;
insert into orders (order_num, order_date, customer_num, ship_instruct,
backlog, po_num, ship_date, ship_weight,
ship_charge, paid_date)
select * from tmp_ordl;%%
This works juuuuust fine.
--
-- Jake (In pursuit of undomesticated aquatic avians but catching
sounding boards)
+----------------------------------------------------------+
|Aside from that, how did you enjoy the play, Mrs. Lincoln?|
+----------------------------------------------------------+