db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Biene_Majo@yahoo.de" <Biene_M...@yahoo.de>
Subject Re: Null values with conditions
Date Mon, 07 May 2012 14:49:53 GMT
Thanks for the answers!
You are also right José, with the answer of Rick it allows both pairs to  
be null. Will make it as you suggested.
But nonetheless thank you both, really appreciated.

Greetings

Biene Maja


Am 07.05.2012, 15:03 Uhr, schrieb José Ventura <st.never@gmail.com>:

> I think the suggested answer will allow all values to be null. If at  
> least
> one pair must not be null, we can add the following:
>
> check
> (
>     (
>         ( a1 is not null and a2 is not null )
>         or
>         ( a1 is null and a2 is null )
>     )
>     and
>     (
>         ( b1 is not null and b2 is not null )
>         or
>         ( b1 is null and b2 is null )
>     )
>     *and ( a1 is not null or b1 is not null )   *
> )
>
> (we can test only a1/b1 here because the "other member of the pair" has
> already been tested earlier).
>
> On Mon, May 7, 2012 at 9:36 AM, Rick Hillegas  
> <rick.hillegas@oracle.com>wrote:
>
>> On 5/7/12 5:14 AM, Biene_Majo@yahoo.de wrote:
>>
>>> Hi,
>>>
>>> i have a table with 4 columns:
>>> A1
>>> A2
>>> B1
>>> B2
>>> i now need something like this (in relation to null values):
>>> (A1 AND A2) OR (B1 AND B2)
>>>
>>> So that if A1 is not null A2 must not be null and the other way around
>>> (so if A2 is not null A1 is not allowed to be null).
>>> The same for B1 and B2.
>>> And at least one of the 2 pairs must not be null.
>>>
>>> Can someone tell me if this is possible or how?
>>>
>>>
>>> Thanks for any suggestions / answers
>>>
>>> Biene Majo
>>>
>>>  Hi Biene,
>>
>> What you describe sounds like a CHECK constraint to me. Not sure that I
>> understand the exact condition you're trying to catch, but something  
>> like
>> the following might work for you:
>>
>> create table t
>> (
>>    a1 int,
>>    a2 int,
>>    b1 int,
>>    b2 int,
>>
>>    check
>>    (
>>        (
>>            ( a1 is not null and a2 is not null )
>>            or
>>            ( a1 is null and a2 is null )
>>        )
>>        and
>>        (
>>            ( b1 is not null and b2 is not null )
>>            or
>>            ( b1 is null and b2 is null )
>>        )
>>    )
>> );
>>
>> Hope this helps,
>> -Rick
>>


-- 
Erstellt mit Operas revolutionärem E-Mail-Modul: http://www.opera.com/mail/

Mime
View raw message