Now, I'm really swaggin' it here, but would it make any sense to look at this WHERE clause using an EXISTS/NOT EXISTS (SELECT ...) or "= (SELECT ... ", instead of the IN/NOT IN? I know that I've gotten myself into trouble (incorrect but correctly appearing result-sets) using IN/NOT IN in the past. There's some material on this in the "Legacy Documents" html START|PROGRAMS|RBASE 2000 FOR WINDOWS|RBASE Legacy Documents" URL=C:\RBTI\RBWin65\TECHDOCS\DOCLIST.HTM (I think).
The numbers should be : 824.HTM 720.HTM Also, Celko's "SQL For Smarties", Chapter 15, has some good stuff on the EXISTS() Predicate. If all this is irrelevant to this issue, my apologies. If I weren't so swamped just now, I'd take more time to assess that myself. Later, Steve ----- Original Message ----- From: "David M. Blocker" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, October 02, 2002 10:05 AM Subject: Re: > Charles > > <<WHERE qual_type NOT IN (select ssn task_id qual_type FROM qualified_detail > t1 WHERE t1.ssn = qualified_detail.ssn AND t1.task_id = > qualified_detail.task_id AND t1.qual_type = 1) >> > > I suspect that the problem is that you are comparing the column qual_type in > the left side of your where clause with THREE columns in the sub-select. > > I have not actually tried this, but what about creating a computed column > that joins the columns ssn task_id and qual_type together and using that > combination in both parts of the WHERE clause? > > David Blocker > > > ----- Original Message ----- > From: "Bill Downall" <[EMAIL PROTECTED]> > To: "RBase List Server" <[EMAIL PROTECTED]> > Sent: Wednesday, October 02, 2002 10:18 AM > > > > >From [EMAIL PROTECTED] Wed Oct 2 10:53:40 2002 > > Received: from pecorp.com (relay.pecorp.com [208.216.27.52]) > > by sonet.rxcpi.net (8.9.3/8.9.3) with ESMTP id KAA30429 > > for <[EMAIL PROTECTED]>; Wed, 2 Oct 2002 10:53:40 -0400 > > From: [EMAIL PROTECTED] > > Received: from ([10.10.40.40]) > > by im-relay.pecorp.com with ESMTP ; > > Wed, 02 Oct 2002 09:55:55 -0500 (CDT) > > Received: by relay.pecorp.com with Internet Mail Service (5.5.2653.19) > > id <SS3V3J2A>; Wed, 2 Oct 2002 10:01:44 -0500 > > Message-ID: <[EMAIL PROTECTED]> > > To: [EMAIL PROTECTED] > > Subject: Rules in RBW6.5++ > > Date: Wed, 2 Oct 2002 10:01:35 -0500 > > MIME-Version: 1.0 > > X-Mailer: Internet Mail Service (5.5.2653.19) > > Content-Type: text/plain; > > charset="ISO-8859-1" > > > > I am trying to write a rule that accomplishes the following goal: > > > > Rule will prevent a row from being added to the table Qualified_Detail > under > > the following condition: > > > > if an emp_id, task_id, and qual_type = 1 exist, then do not allow > > the row to be added. > > > > This means that for that same emp_id and task_id, that if qual_type = > > something other than 1, the row should be added. > > > > Already existing row: > > empid =124 > > task_id =1 > > qual_type = 1 > > cladode = 8/1/2002 > > > > Do not allow the following to be added: > > > > empid = 124 > > task_id = 1 > > qual_type = 1 > > qual_date = 8/5/2002 > > > > BUT DO allow the following to be added: > > > > empid = 124 > > task_id = 1 > > qual_type = 2 > > qual_date = 8/5/2002 > > > > > > The rule I wrote is to Add/modify if the select fails > > > > WHERE qual_type NOT IN (select ssn task_id qual_type FROM qualified_detail > > t1 WHERE t1.ssn = qualified_detail.ssn AND t1.task_id = > qualified_detail.task_id AND > > t1.qual_type = 1) > > > > Otherwise display message "Employee has already been initially qualified > on > > this task" > > > > What is happening now is that any attempt to insert a row into the table > is > > failing under this rule, regardless of what the qual_type is.. > > > > I know how to write a rule testing for uniqueness (which is basically what > > this is), but throwing the qual_type = 1 is perhaps throwing a wrench into > > things. > > > > Thanks for any assistance .. > > > > > > Charles Sikora > > Coordinator, Gas Storage > > Manlove Field > > Peoples Energy Corporation > > > > > > > > > > > > ================================================ > > TO SEE MESSAGE POSTING GUIDELINES: > > Send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: INTRO rbase-l > > ================================================ > > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: UNSUBSCRIBE rbase-l > > ================================================ > > TO SEARCH ARCHIVES: > > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
