?? Why not create a unique multi-column index and save the overhead of the computed column?
"David M. Blocker" <[EMAIL PROTECTED]> wrote: >Yahoo - I Love it when this forum solves a problem! > >David > >----- Original Message ----- >From: <[EMAIL PROTECTED]> >To: <[EMAIL PROTECTED]> >Sent: Wednesday, October 02, 2002 2:00 PM >Subject: RE: > > >> David, that did the trick! >> >> I created a computed column c1 which is ssn + ctxt(task_id) + >> ctxt(qual_type) >> >> Then a standard rule testing for uniqueness with FAIL >> >> c2 IN (select c1 from qualified_detail t1 where t1.c1 = >qualified_detail.c1) >> >> >> Thanks! I never thought of using a computed column in a rule.. >> >> Charles Sikora >> Coordinator, Gas Storage >> Manlove Field >> Peoples Energy Corporation >> >> -----Original Message----- >> From: David M. Blocker [mailto:[EMAIL PROTECTED]] >> Sent: Wednesday, October 02, 2002 10:05 AM >> To: [EMAIL PROTECTED] >> 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/ >> > > >================================================ >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/ > -- Albert Berry Full Time Consultant to PSD Solutions 350 West Hubbard, Suite 210 Chicago, IL 60610 312-828-9253 Ext. 32 __________________________________________________________________ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.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/
