Re: existence question

"Jeff Kish" <jeff.kish_at_mro.com> schrieb im Newsbeitrag news:brusf0hcl6oi8e9a390c7k815gi0vjelgl_at_4ax.com...
> Greetings.
>
> Can someone help me out here?
> I have a table. with a unique index across five columns (c1, c2, c3, c4,
c5.
>
> I was trying to figure out if there was a way to form an sql where clause
that
> would tell me if a certain business rule would be violated.
>
> The rule says, for a given (c1 c2), if you have any rows that have c4 and
c5
> null, then you can't have any other rows for that (c1 c2) which have a not
> null c4 or a not null c5.

without trying it out really, how about this... lets call your table <blah>:

select * from <blah> b1, <blah> b2
where b1.c1 = :1 and b1.c2 = :2

and b2.c1 = :1 and b2.c2 = :2
and b1.c3 is null and b1.c4 is null
and b2.c3 is not null and b2.c4 is not null

  This statement will return all rows of the b1,b2 join that violate your business rule... The key is to join the table with itself through the partial (c1 c2) of the unique (c1..c5)

>
> I'm a bit out of practice.
>
>
> Thanks
> Jeff
> Jeff Kish
Received on Wed Jul 21 2004 - 09:41:05 CDT