Eureka!
I have both solved the problem by using a view rather than the table for the
comparison and also found out why there was a problem:
The rules now looks like this:
MESSAGE : Duplicate combination: Group Sequence
TABLE : Images Row is added or changed if condition SUCCEEDS
WHERE : NOT (( Images.Group_Number * 1000) + Images.Group_Sequence) IN
(SELECT InValidSequ FROM DupSequ_Rule_View)
with the view defined as:
... select ( (Group_Number * 1000) + Group_Sequence) from Images where
Group_Number > 0
It's very hard to tell but I suspect that this is very slightly faster that
the original way.
The reason for the problem was that, somehow, one Group_Number had been
corrupted to a vary large value and so gave a null value in the calculation.
Sorry, everyone, I should have spotted that before I posted the original
question.
Thanks & regards,
Alastair.
----- Original Message -----
From: "Alastair Burr" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, August 27, 2002 9:16 PM
Subject: Re: Rule problem
> Good idea, David, but, no, it hasn't changed and it is set to ON which
> should be right because there are no nulls in the two columns.
>
> I've also discovered that I can't insert rows with both columns set to
> zero - not surprisingly, if the rule won't now allow that duplication.
>
> Another little snippet of info: in the past, ie: the pre-restore database,
I
> could not have rows with zero in the Group_Number and either 2 or 3 in the
> Group_Sequence regardless of the fact that neither combination - 0 & 2 or
0
> & 3 - did definitely not exist. 0 & 1 and 0 & anything 4 and above were
> fine... This didn't matter because there was no group zero - I only
> discovered it because I used it once to try and re-sequence a group by
> changing the group number to 0 to allow me to change the remaining
sequence
> numbers.
>
> Any further suggestions gratefully accepted,
> Regards,
> Alastair.
>
>
> ----- Original Message -----
> From: "David M. Blocker" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, August 27, 2002 6:07 PM
> Subject: Re: Rule problem
>
>
> > Did your ZERO setting change? SET ZERO ON or OFF could affect the
results
> > here if you leave a column blank.
> >
> > David Blocker
> >
> > ----- Original Message -----
> > From: "Albert Berry" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Tuesday, August 27, 2002 8:36 AM
> > Subject: RE: Rule problem
> >
> >
> > > Alastair - when I have to force an FK I use a 0 "Unknown" and use zero
> as
> > the equivalent to IS NULL, and define the column as NOT NULL. I have
> found
> > over the years that this is a more foolproof method than allowing NULL
> FKs.
> > FWIW
> > >
> > > "Alastair Burr" <[EMAIL PROTECTED]> wrote:
> > >
> > > >Hi all,
> > > >
> > > >This rule has worked correctly for months until I restored the
database
> > from
> > > >a backup today:
> > > >
> > > >MESSAGE : Duplicate combination: Group Sequence
> > > > TABLE : Images Row is added or changed if condition SUCCEEDS
> > > > WHERE : NOT (( Images.Group_Number * 1000) + Images.Group_Sequence)
IN
> > > > (select (( Images.Group_Number * 1000) + Images.Group_Sequence)
> > > > from Images where Group_Number > 0)
> > > >
> > > >Now it won't let me amend rows where the Group_Number and
> Group_Sequence
> > are
> > > >both zero.
> > > >
> > > >It is intended to ensure that I cannot have duplicate sequence
numbers
> > where
> > > >there is a group number above zero.
> > > >
> > > >Both columns default to zero to avoid nulls so zero means no group.
> There
> > > >are many rows with no group so a constraint is not possible.
> > Group_Sequence
> > > >can be from 1 to 999 when there is a Group_Number.
> > > >
> > > >Can anybody see anything wrong with it or shed some light on why a
> > restore
> > > >might have affected it?
> > > >
> > > >Thanks in advance for any suggestions,
> > > >Regards,
> > > >Alastair.
> > > >
> > > >
> > > >----------------------------------
> > > >A D B Burr,
> > > >St. Albans, UK.
> > > >----------------------------------
> > > >[EMAIL PROTECTED]
> > > >----------------------------------
>
> ================================================
> 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/
