Javier thanks :-) I just finished writing it in a horrible cursor update loop ordeal (I hate cursors! there MUST be a way to do it in one SQL-engine grinding fell-swoop!) Looks like I will have to rewrite it again with your suggestion at hand.. :-)
Jeff Watson [EMAIL PROTECTED] Tube Methods, Inc. 610-279-7700 From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of javier valencia Sent: Thursday, February 14, 2008 10:04 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: update Jeff: A few weeks ago I ran into the same issue and like you, I could not get the count to update. I finally ended up with the following code: SET ERROR MESSAGE 677 OFF DROP VIEW TempWorkLoc SET ERROR MESSAGE 677 ON PAUSE 3 USING 'UPDATING COUNT...' CAPTION 'System Message...' ICON WARNING CREATE TEMP VIEW TempWorkLoc(LocName,LocCount) + AS SELECT fwo_work_location , count(*) from fwo_file GROUP By fwo_work_location UPDATE FleetServiceLoc SET ServLocCount = 0 UPDATE FleetServiceLoc SET ServLocCount = T1.LocCount + FROM TempWorkLoc T1, FleetServiceLoc T2 where T1.LocName = T2.ServLocName *( -- Use the next 2 lines if you have null values as the previous code does -- not seem to update nulls. SELECT LocCount INTO vLocCount FROM TempWorkLoc WHERE LocName IS NULL UPDATE FleetServiceLoc SET ServLocCount = .vLocCount WHERE ServLocName IS NULL ) SET ERROR MESSAGE 677 OFF DROP VIEW TempWorkLoc SET ERROR MESSAGE 677 ON Javier Valencia, PE Sr. Project Manager Universal Asset Management, L.L.C. 801 Westchester Ave. Harrisonville, MO 64701 Phone: 816-887-4011 Fax: 816-887-1960 Cell: 913-915-3137 is there a way to update a column with a count value from another table? something like update tback set ops_behind = (count(t2.lotnumber) from tback t1, lotsched t2 WHERE t1.lotnumber = t2.lotnumber AND t2.cycle IS NOT NULL AND t2.cycle < 95 AND t2.act_strt IS NULL ? (obviously the above did not work but that's the idea) Thanks :-/ Jeff Watson [EMAIL PROTECTED] Tube Methods, Inc. 610-279-7700
