Manythanks to Razzak, Bill, Dennis and Steve for putting in time on these puzzlingrules issues. First,I followed Razzak?s excellent advice on checking the basics. EQNULL, ZERO and IDQUOTES were all set appropriately. AnUNLOAD ALL and running the file to rebuild the database found several smallerrors which I corrected, but largely the database reloaded correctly. Ithen systematically re-tested every suggested syntax for the first failingrule, trying to trap duplicate Vehicle ID (VID) values in a Drivers table whilestill permitting nulls.The resultswere the same, either with R:Base rejecting the syntax, or the rule beingaccepted or permitting 1 or more duplicates. BUT, we have a solution!Dennis?s last suggestion:
RULES + 'Another driver is already assigned to that vehicle' + FOR Drivers + SUCCEEDS + WHERE VID IS NULL + OR VID NOT IN + ( SELECT VID FROM Drivers #T1 + WHERE #T1.VID = Drivers.VID) ANDSteve?s even simpler suggestion: RULES 'Vehicle ID must be either blank or unique: two drivers cannot + be assigned to the same vehicle' + FOR Drivers FAILS WHERE + VID IS NOT NULL AND + VID IN (SELECT VID FROM DRIVERS) BOTHwork!Apparently, and I?m not sure why,all previous versions which had R:Base also make sure it wasDIFFERENT row by comparing the PK DCodevalues is what caused the problem.I?mhappy I have a solution, but not sure what I?ve learned from this other than: KISS Keepit simple Stupid! David David Blocker Email: [email protected] Phone: 781-344-1920 Cell: 339-206-0261 -----Original Message----- From: Dennis McGrath [mailto:[email protected]] Sent: Wednesday, July 31, 2013 05:15 PM To: 'RBASE-L Mailing List' Subject: [RBASE-L] - Re: An unresolved RULES issue from earlier email This one works.Secret is to use DB designer to create the unique rule and then only tweek enough to get the IS NULL OR part to work.Apparently rules is smart enough to avoid checking the current row against itself and any attempt to do this in the rule breaks it.RULES +'Another driver is already assigned to that vehicle' +FOR Drivers +SUCCEEDS +WHERE VID IS NULL +OR VID NOT IN +( SELECT VID FROM Drivers #T1 +WHERE #T1.VID = Drivers.VID)Dennis McGrathSoftware DeveloperQMI Security Solutions1661 Glenlake AveItasca IL [email protected]: [RBASE-L] - Re: An unresolved RULES issue from earlier emailHi David, Did you try RULES +'Another driver is already assigned to that vehicle' +FOR Drivers +SUCCEEDS +WHERE VID IS NULL +OR NOT EXISTS +(SELECT * FROM Drivers d1, Drivers d2 + WHERE d2.VID = d1.VID AND d2.DriverID <> d1.DriverID)Or RULES 'Vehicle ID must be either blank or unique: two drivers cannot +be assigned to the same vehicle' + FOR Drivers FAILS + WHERE VID IS NOT NULL AND VID IN + (SELECT VID FROM DRIVERS d1, DRIVERS d2 WHERE d2.VID = d1.VID AND +d2.DCode <> d1.DCode)? Regards, Stephen MarksonThe Pharmacy Examining Board of Canada416.979.2431 x251 From: [email protected] [mailto:[email protected]] On Behalf Of David BlockerSent: July-31-13 1:08 PMTo: RBASE-L Mailing ListSubject: [RBASE-L] - Re: An unresolved RULES issue from earlier email BillI tried your rule as sent to me a few weeks back. Your exact syntax:RULES +'Another driver is already assigned to that vehicle' +FOR Drivers +SUCCEEDS +WHERE VID IS NULL +OR NOT EXISTS +(SELECT * FROM Drivers d2 + WHERE d2.VID = Drivers.VID AND d2.DriverID <> Drivers.DriverID)was rejected by R:Base as an invalid WHERE clause. I translated it to the equivalent:RULES +'Another driver is already assigned to that vehicle' +FOR Drivers +SUCCEEDS +WHERE VID IS NULL +OR VID NOT IN +(SELECT VID FROM Drivers d2 + WHERE d2.VID = Drivers.VID AND d2.DriverID <> Drivers.DriverID)This was accepted by R:Base.However, the rule still does not work. Nulls are accepted by R:Base, as they were with my FAILS version of the rule, but R:Base happily allows me to put the same VID value in for 2 or more rows. (And yes, RULES are ON)Here's my original rule again: RULES 'Vehicle ID must be either blank or unique: two drivers cannot +be assigned to the same vehicle' + FOR Drivers FAILS + WHERE VID IS NOT NULL AND VID IN + (SELECT VID FROM DRIVERS d2 WHERE d2.VID = Drivers.VID AND +d2.DCode <> Drivers.DCode)(Bill, this means it fails if it's FILLED IN - NOT NULL AND is a duplicate of one already there)Looks like I may have to go with Dennis? solution unless anyone else has an idea.Razzak, care to weigh in?David David Blocker Email: [email protected] Phone: 781-344-1920 Cell: 339-206-0261 -----Original Message-----From: Bill Downall [mailto:[email protected]]Sent: Tuesday, July 30, 2013 06:15 PMTo: 'RBASE-L Mailing List'Subject: [RBASE-L] - Re: An unresolved RULES issue from earlier emailDavid, In order for the rule to fail, the VID must be null, and the VID also must be found in the lookup. I would have a hard time fining any rows that would fail under those conditions. Your reversing of the logic from the SUCCEEDS version got twisted too much, because you changed the OR to an AND, and you also changed the NOT IN to an IN. Did you try the SUCCEEDS syntax I came up with originally? Did it also not work? I know it violates our tried and true DMB methodology, but it is easier to read and mentally translate into English. BillRULES +'Another driver is already assigned to that vehicle' +FOR Drivers +SUCCEEDS +WHERE VID IS NULL +OR NOT EXISTS +(SELECT * FROM Drivers d2 + WHERE d2.VID = Drivers.VID AND d2.DriverID <> Drivers.DriverID) On Tue, Jul 30, 2013 at 5:01 PM, David Blocker wrote: Hi friendsI just realized as I temporarily moved forward on my project that an issue I wrote to the list about several weeks ago never was resolved. I?m trying again to see if anyone has an idea for a solution. Here?s the Structure: VEH (vehicles) table has primary key column VID (vehicle ID)Table DRIVERS (people who drive the vehicles) has Foreign key column VID. When a driver is assigned a vehicle, the VID column for that driver?s row is set to the VID value. However, there are two wrinkles here:1) A Driver may NOT currently be assigned a vehicle, so VID could be NULL2) If Driver A has a certain vehicle, say VID = 1, Driver B should NOT be allowed to get that vehicle. In other words, NO TWO RECORDS in Drivers should have the same value for VID So the rule I want would:1) Permit Nulls2) Forbid two rows in Drivers from having the same value. A rule is needed, because a Unique Key for VID in Drivers cannot be set up unless the column has a NOT NULL constraint. Here?s the rule that I tried based on some emails back and forth with Bill Downall: RULES 'Vehicle ID must be either blank or unique: two drivers cannot +be assigned to the same vehicle' + FOR Drivers FAILS + WHERE VID IS NOT NULL AND VID IN + (SELECT VID FROM DRIVERS d2 WHERE d2.VID = Drivers.VID AND +d2.DCode <> Drivers.DCode) This does NOT work. R:Base DOES allow null values, but it also allows me to set multiple rows to the same VID value. Any ideas? David BlockerDavid Blocker Email: [email protected] Phone: 781-344-1920 Cell: 339-206-0261
