Bill TheWHERE clause for the FAILS is valid: if it deliberately put some ?bad rows?into the table, it finds them, but rule doesn?twork ? this has never happened before. However,tomorrow I?ll try your idea and let you know what happens. 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 PM To: 'RBASE-L Mailing List' Subject: [RBASE-L] - Re: An unresolved RULES issue from earlier email David, 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. Bill 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) On Tue, Jul 30, 2013 at 5:01 PM, David Blocker <[email protected]> wrote: Hi friends I just realized as I temporarily moved forward on my projectthat an issue I wrote to the list about several weeks ago never wasresolved.I?m trying again to see ifanyone 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 Foreignkey column VID. When a driver is assigned a vehicle, the VID column for thatdriver?s row is set to the VID value. However, there are two wrinkles here: 1)ADriver may NOT currently be assigned a vehicle, so VID could be NULL 2)IfDriver A has a certain vehicle, say VID = 1, Driver B should NOT be allowed toget that vehicle.In other words, NO TWORECORDS in Drivers should have the same value for VID So the rule I want would: 1)PermitNulls 2)Forbidtwo rows in Drivers from having the same value. A rule is needed, because a Unique Key for VID in Driverscannot be set up unless the column has a NOT NULL constraint. Here?s the rule that I tried based on some emails back andforth with Bill Downall: RULES 'Vehicle IDmust be either blank or unique: two drivers cannot + be assigned to the same vehicle' + FOR Drivers FAILS+ WHERE VID IS NOTNULL AND VID IN + (SELECT VID FROMDRIVERS 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 multiplerows to the same VID value. Any ideas? David Blocker David Blocker Email: [email protected] Phone: 781-344-1920 Cell: 339-206-0261
