You are viewing a plain text version of this content. The canonical link for it is here.

Posted to derby-user@db.apache.org by "Biene_Majo@yahoo.de" <Bi...@yahoo.de> on 2012/05/07 14:14:32 UTC

Null values with conditions

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

Re: Null values with conditions

Posted by "Biene_Majo@yahoo.de" <Bi...@yahoo.de>.

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...@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  
> <ri...@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/

Re: Null values with conditions

Posted by José Ventura <st...@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 <ri...@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
>

Re: Null values with conditions

Posted by Rick Hillegas <ri...@oracle.com>.

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