Re: existence question

I guess I misunderstood your business rules. Here's another version:

select c1,c2
from foo
group by c1,c2
having sum(case when c4 is null and c5 is null then 1 else 0 end) > 0

    and sum(case when c4 is not null or c5 is not null then 1 else 0 end )
> 0

"Isaac Blank" <izblank_removethis__at_yahoo.com> wrote in message news:bwDLc.118$AY5.14_at_newssvr21.news.prodigy.com...
> select c1,c2 from (
> select c1,c2, sum(case when c4 is null and c5 is null then 1 else 0 end)
> twonulls,
> sum(case when (c4 is null and c5 is not null) or (c4 is not null and c5 is
> null) then 1 else 0 end ) onenull
> from foo
> group by c1,c2) x
> where twonulls > 0
> and onenull > 0
>
> Or, in more compact form:
>
> select c1,c2
> from foo
> group by c1,c2
> having sum(case when c4 is null and c5 is null then 1 else 0 end) > 0
> and sum(case when (c4 is null and c5 is not null) or (c4 is not null
> and c5 is null) then 1 else 0 end ) > 0
>
>
> "Jeff Kish" <jeff.kish_at_mro.com> wrote in message
> 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.
> >
> > I'm a bit out of practice.
> >
> >
> > Thanks
> > Jeff
> > Jeff Kish
>
Received on Thu Jul 22 2004 - 11:04:04 CDT