Re: existence question

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

Regards,
Jeff Kish Received on Wed Jul 21 2004 - 10:04:14 CDT