Patti, If you must regularly perform the update of the ClientMaster LastHomeVisit field I am curious why you do not create Stored Procedure Triggers to automatically update the ClientMaster field whenever you INSERT/UPDATE/DELETE a record from the HomeVisit table.
NOTE!! Since you indicate that the ClientId is a computed field in the following skeleton code you would substitute the individual parts composing the ClientId. The code shown is a bare outline. The three triggers would be: AFTER INSERT * define variables used in the STORED PROCEDURE * Select ClientId, VisitDate INTO TAINClientId INDI TAINIv01, TAINVisitDate INDI TAINIv02 WHERE CURRENT OF SYS_NEW * Select LastHomeVisit INTO TAILastHomeVisit INDI TAINIV03 FROM ClientMaster WHERE ClientID=.TAINClientId * IF TAINVisitDate > .TAILastHomeVisit THEN UPDATE ClientMaster SET LastHomeVisit=.TAINVisitDate WHERE ClientId=.TAINClientId ENDIF * cleanup variables previously defined. * RETURN AFTER UPDATE * define variables * Select ClientId, VisitDate INTO TAUNClientId INDI TAUNIv01, TAUNVisitDate INDI TAUNIv02 WHERE CURRENT OF SYS_NEW * Select ClientId, VisitDate INTO TAUPClientId INDI TAUPIv01, TAUPVisitDate INDI TAUPIv02 WHERE CURRENT OF SYS_OLD * IF TAUNVisitDate<>.TAIUPVisitDate THEN Select LastHomeVisit INTO TAULastHomeVisit INDI TAUNIV03 FROM ClientMaster WHERE ClientID=.TAIUClientId IF TAUNVisitDate > .TAULastHomeVisit THEN UPDATE ClientMaster SET LastHomeVisit=.TAUNVisitDate WHERE ClientId=.TAUNClientId ENDIF ENDIF * Cleanup variables previously defined * RETURN AFTER DELETE * define variables * Select ClientId, VisitDate INTO TADPClientId INDI TADPIv01, TADPVisitDate INDI TADPIv02 WHERE CURRENT OF SYS_OLD * Select LastHomeVisit INTO TADLastHomeVisit INDI TADPIV03 FROM ClientMaster WHERE ClientID=.TADPClientId * IF TADPVisitDate=.TADLastHomeVisit THEN SELECT MAX(VisitDate) INTO TADNVisitDate FROM HomeVisit WHERE ClientId=.TADPClientId UPDATE ClientMaster SET LastHomeVisit=.TAUNVisitDate WHERE ClientId=.TAUNClientId ENDIF * Cleanup variables previously defined * RETURN Once these STORED PROCEDURES are defined as TRIGGERS for the HomeVisit table. The LastHomeVisit column of the ClientMaster will be automatically maintained. In my Database all of the STORED PROCEDURE that a TRIGGERS as define using a prefix + table name: TBI = BEFORE INSERT TRIGGER TAI = AFTER INSERT TRIGGER TBU = BEFORE UPDATE TRIGGER TAU = AFTER UPDATE TRIGGER TBD = BEFORE DELETE TRIGGER TAD = AFTER DELETE TRIGGER Within each TRIGGER each variable uses a prefix as defined above plus a 3 digit number plus 1 char NEW/OLD indicator plus the variable name. For Example TAU033PClientId or TAU033NClientId. Where N=NEW and P=OLD. In creating STORED PROCEDURES on must take into consideration that all variables are global hence a naming convention tied to individual STORED PROCEDURE. You can then use command "CLEAR VAR TAU033%" to safely clear variables for that STORED PROCEDURE. Jim Bentley, American Celiac Society 1-504-737-3293 ________________________________ From: Bill Downall <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Wednesday, June 19, 2013 3:11 PM Subject: [RBASE-L] - Re: I always have trouble with the update command On Wed, Jun 19, 2013 at 4:05 PM, Patti Jakusz <[email protected]> wrote: I'm not far enough along for that command yet. I'm getting illegal select function on the first half of your solution. Naturally, my actual tables are not as simple as what I wrote. My ClientId is a computed field. > >This is a little more complicated than what I had been doing - projecting a >temp table with my dates sorted in ascending order. Then I just do a regular >update command and I skip the "max" specification. It will update each client >record a bunch of times (however many times I have a HomeVisit record), but >since the records are already sorted, the last update will be the latest date. > >It just seems like the "max" spec should work in the update command, and it >never has for me. > >Thanks though, >Patti Patti, Your syntax error is my fault for not proofreading my own code. Let me try again, from the top: It takes two commands, and one clean-up command: CREATE TEMP VIEW MaxVisitDates + (ClientID, MaxVisitDate) AS + SELECT + ClientID, MAX(VisitDate) As MaxVisitDate + FROM HomeVisit + GROUP BY ClientID UPDATE ClientMaster + SET LastHomeVist = MaxVisitDate + FROM ClientMaster, MaxVisitDates h2 + WHERE ClientMaster.ClientID = h2.ClientID DROP VIEW MaxVisitDates
