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: Jeff Kish <jeff.kish_at_mro.com>
Date: Wed, 21 Jul 2004 11:48:11 -0400
Message-ID: <km3tf0l093830meughmcmocfftgd425u4t@4ax.com>


On Wed, 21 Jul 2004 17:13:48 +0200, "André Hartmann" <andrehartmann_at_hotmail.com> wrote:

>

<snip>

>
>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
>

Thanks. I don't seem to be getting what I think I should. I tried the query and got no rows back. (I hope to heaven I'm not being incredibly dumb here)

I may have misstated the question slightly in terms of c1 etc...I forgot c3 which is probably not significant in figuring out the solution.

If you can persevere..Here is some data: This is OK per business rule,

c1	c2	c3	c4	c5
1001	smith	smith		NEWYORK
1001	smith	smith		SOMETHING
1002	jones	jones		NEWYORK
1002 	jones	jones		SOMETHING

This is not OK per business rule because a c1c2c3 has more than one row when there exists a row with c4 and c5 as null

c1	c2	c3	c4	c5
1001	smith	smith		NEWYORK
1001	smith	smith		

This is not OK (twice bad) per business rule
c1	c2	c3	c4	c5
1001	smith	smith		NEWYORK
1001	smith	smith		
1002	jones	jones		NEWYORK
1002 	jones	jones		

If I can come up with a where clause that tells me if either of the second conditions or similar ones exist it would meet my goal I think, maybe by not being an empty set or something.

Maybe I need "group" somewhere, I don't know. Thanks
Jeff Kish Received on Wed Jul 21 2004 - 10:48:11 CDT

Original text of this message

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