Re: existence question

"Jeff Kish" <jeff.kish_at_mro.com> schrieb im Newsbeitrag news:ib1tf0h58k5i6a0n2kh11cmi7cvkr2p76b_at_4ax.com...
> On Wed, 21 Jul 2004 16:41:05 +0200, "André Hartmann"
> <andrehartmann_at_hotmail.com> wrote:
>
> >
> >"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
> >
> Thanks.
> I need to know the :1 and :2 values for this right?
> Is there a way to run it just to see if any rows violate this
> characteristic/rule,i.e.
> Could I use (I'm trying it now but it will take a little time to put the
data
> in place):
>
> select * from <blah> b1, <blah> b2
> where
> 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

No, if you dont want to stuff in any :1 or :2 then just make sure you join properly:

select * from <blah> b1, <blah> b2
where b1.c1 = b2.c1 and b1.c2 = b2.c2
and b1.c3 is null and b1.c4 is null
and b2.c3 is not null and b2.c4 is not null

After thinking it over again I am not quite sure about how you want to "NOT NULL"s to be evaluated. you might actually want "... and (b2.c3 is not null or b2.c4 is not null)" as the last line in the above statement... try it out :)

>
> Regards,
> Jeff Kish
Received on Wed Jul 21 2004 - 10:13:48 CDT