Hmm, I’ll take the table reference off the qoh and give it a try. Dan Goldberg
From: Stephen Markson Sent: Wednesday, January 22, 2014 12:27 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Updating Mystery Hi Dan, Did you try: …set qoh = (qoh + t2.qty_rec)… ? Regards, Stephen Markson The Pharmacy Examining Board of Canada 416.979.2431 x251 From: [email protected] [mailto:[email protected]] On Behalf Of Dan Goldberg Sent: January-22-14 1:24 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Updating Mystery Using the latest update 9.5 64 I have a series of commands that I think should work but does not. --create a temporary view to get totals create temp view tmppo (partnum,qty_rec) as select partnum,sum(qty_rec) from po_rec_log where rec_date = 1/14/2014 group by partnum --update master table to add to qoh update master set qoh = (t1.qoh + t2.qty_rec) from master t1, tmppo t2 where t1.partnum = t2.partnum It says it updated the correct amount of rows but does not add to the qoh. I can see the rows in the view so I know the view is working. But if I put the view’s data into a temporary table and update, it works. --create a temporary view to get totals create temp view tmppo (partnum,qty_rec) as select partnum,sum(qty_rec) from po_rec_log where rec_date = 1/14/2014 group by partnum --create a temporary table to hold the view create temp table tmppo2 (partnum text 25,qty_rec real) --insert the view into the temp table insert into tmppo2 select all from tmppo --update master table to add to qoh update master set qoh = (t1.qoh + t2.qty_rec) from master t1, tmppo2 t2 where t1.partnum = t2.partnum Then it works. Shouldn’t it work off of the view? Dan Goldberg
