Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: existence question

Re: existence question

From: Isaac Blank <izblank_removethis__at_yahoo.com>
Date: Thu, 22 Jul 2004 00:04:23 GMT
Message-ID: <bwDLc.118$AY5.14@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 Wed Jul 21 2004 - 19:04:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US