Boolean complement of a pair of OR clause 
Author Message
 Boolean complement of a pair of OR clause

WOW! That sounds like an esoteric topic!

I have data in a temp table ready to be inserted into a main table.
However, the main table has two columns, each of which must remain
unique. (So why don't my users allow me to create uniqueness
constraints? That's another story. Grrr!)  So before I do the
   INSERT INTO main-table SELECT * FROM temp-table
I run a query to see if there are any rows in the temp table that, if
inserted to the main table, would violate the above conventions.

As it happens, the temp table has 422 rows. When I seek potential
violations, I get 20 rows. Therefore, if I seek non-violators, I should
get 402 rows.  In fact, I should be able to just do:
   INSERT INTO main-table
   SELECT * FROM temp-table
    WHERE no violations would occur.

Instead, I am getting 0 rows inserted.  When I do a select count for
non-violators, I get 0.

Now, if you have held on this long, I can get specific.  I have a small
example of what I want.  It works in the small example, however.

create table t1
( number        integer,        -- Intended to be unique
  name          char(2)         -- Intended to be unique
);
create table t2
( number        integer,
  name          char(2)
);
insert into t1 values (1, "A");
insert into t1 values (2, "B");
insert into t1 values (3, "C");
insert into t1 values (4, "D");
insert into t1 values (5, "E");
insert into t1 values (6, "F");
insert into t1 values (7, "G");
insert into t1 values (8, "H");
insert into t1 values (9, "I");

insert into t2 values (11, "R");
insert into t2 values (12, "S");
insert into t2 values (13, "T");
insert into t2 values (14, "U");
insert into t2 values (15, "V");
insert into t2 values (16, "W");
insert into t2 values (17, "X");
insert into t2 values (18, "Y");
insert into t2 values (19, "Z");

insert into t2 values (2, "J");         -- Violates uniquness of number
insert into t2 values (10, "B");        -- Violates uniquness of name
insert into t2 values (3, "D");         -- Violates both uniqunesses

Suppose I want to merge the contents of table t2 into t1. Looking out
for potential violators, I run the following query:

select * from t2
 where number in (select number from t1)
    or name   in (select name from t1)

Sure enough, I get the expected results, the ones I commented about as
violators.

     number name

          2 J
         10 B
          3 D

Now suppose I want to see the rows in t2 that I *can* insert into t1 in
compliance with the uniqueness "convention". Then my WHERE clause needs
to be the logical complement of the above WHERE clause.  Recalling my
boolean algebra, not (A or B) is the same as (not A) and (not B). Hence,
the WHERE clause to seek out valid rows is:

select * from t2
 where number not in (select number from t1)
   and name   not in (select name from t1)

And, sure enough, I get:
     number name

         11 R
         12 S
         13 T
         14 U
         15 V
         16 W
         17 X
         18 Y
         19 Z

So what's the problem?  To my chagrin, when I do this pair of WHERE
clauses against a much larger table (a customer table with thousands of
rows) the "valid row" query returns 0 rows.

THIS IS NUTS!  And it's turning me into squirrel bait myself!

Does someone have a large table thaey can test this kind of logic
against?

Thanks for at least *reading* this far!  I have been on the phone with
Informix tech support and, since they see the small example working on
their end (as well as in my small example) I'm getting little sympathy.
--
    -- Jake (In pursuit of undomesticated semi-aquatic avians)
+---------------------------------------------------------------+
|Insofar as manifestations of functional deficiencies are agreed|
|by any and all concerned parties to be imperceivable, and are  |
|so stipulated, it is incumbent upon said heretofore mentioned  |
|parties to exercise the deferment of otherwise pertinent       |
|maintenance procedures.                                        |
+--------------------------------------- (www.hardyharhar.com) -+



Sat, 18 Mar 2000 03:00:00 GMT
 Boolean complement of a pair of OR clause


Quote:
>WOW! That sounds like an esoteric topic!
>select * from t2
> where number not in (select number from t1)
>   and name   not in (select name from t1)
>So what's the problem?  To my chagrin, when I do this pair of WHERE
>clauses against a much larger table (a customer table with thousands of
>rows) the "valid row" query returns 0 rows.

Without testing, I can't check it, but it sounds like the sort of wierdo
result you get when nulls stick their grubby little hands into the pot.

I don't know. Does 'number not in (<select which has some nulls>)' return
true or false.  I'll have to run one when I get a chance.

Bryan Tonnet

--
Bryan Tonnet



Sun, 19 Mar 2000 03:00:00 GMT
 Boolean complement of a pair of OR clause

Quote:

> So what's the problem?  To my chagrin, when I do this pair of WHERE
> clauses against a much larger table (a customer table with thousands of
> rows) the "valid row" query returns 0 rows.

> THIS IS NUTS!  And it's turning me into squirrel bait myself!

As Bryan says, this is a nulls problem.
Try the following:

insert into t1 values (11, NULL);
insert into t1 values (NULL, "S");

-- returns 0 rows as you found out.
select * from t2
 where number not in (select number from t1)
   and name   not in (select name from t1);

-- returns result set you want
select * from t2
 where ( number not in (select number from t1 where number is not null ))
   and ( name   not in (select name from t1 where name is not null ))

i.e. number name
         13 T
         14 U
         15 V
         16 W
         17 X
         18 Y
         19 Z

Mark Fisher
Britannic Assurance

--
------------------------------------------
The views expressed here are mine and not
those of my employer, Britannic Assurance
------------------------------------------



Sun, 19 Mar 2000 03:00:00 GMT
 Boolean complement of a pair of OR clause

Quote:

> WOW! That sounds like an esoteric topic!

> I have data in a temp table ready to be inserted into a main table.
> However, the main table has two columns, each of which must remain
> unique. (So why don't my users allow me to create uniqueness
[SNIP]

> Does someone have a large table thaey can test this kind of logic
> against?

I'll look into it.  In the meantime remember Kagel's First Law of SQL?  
Try deleting from t2 all duplicates in two separate operations then you
can insert the survivors:

delete from t2 where number in (select number from t1);
delete from t2 where name in (select name from t1);
insert into t1 select * from t2;

Art S. Kagel

Kagel's First Law of SQL:
        There are at least three ways to do ANYTHING in SQL.  

First Correllary to Kagel's First Law of SQL:
        If you have not found all three you may not be doing it the
        best way.



Sun, 19 Mar 2000 03:00:00 GMT
 Boolean complement of a pair of OR clause

... SNIP ...

Quote:
> Without testing, I can't check it, but it sounds like the sort of
> wierdo result you get when nulls stick their grubby little hands into
> the pot.

> I don't know. Does 'number not in (<select which has some nulls>)'
> return true or false.  I'll have to run one when I get a chance.

Kazowwee!!! Kawabunga! (Does this mean anything?)

I added a row to t1 with a null column and ran the same "valid row"
query again.  As with my live problem, it suddenly came up with 0 rows.
When I added in the "where name/number is not null" clauses to the
subqueries, it got me the rows I needed.

Thanks, Bryan.  That was it!  I have just implemented that in the live
query and it got the results I wanted.

Of course, I could not do the insert and select in the same statement
because SQL correctly will not let me insert into a table I am
subquerying from.  A select into temp followed by insert-select from the
temp took care of that.
--
    -- Jake (In pursuit of undomesticated semi-aquatic avians)
+---------------------------------------------------------------+
|Insofar as manifestations of functional deficiencies are agreed|
|by any and all concerned parties to be imperceivable, and are  |
|so stipulated, it is incumbent upon said heretofore mentioned  |
|parties to exercise the deferment of otherwise pertinent       |
|maintenance procedures.                                        |
+--------------------------------------- (www.hardyharhar.com) -+



Sun, 19 Mar 2000 03:00:00 GMT
 Boolean complement of a pair of OR clause

This was done real quick, but I think I see your problem.  I inserted
the following into t1 and t2:

insert into t1 values (25, null);
    insert into t1 values (null, "#");

    insert into t2 values (53, null);
    insert into t2 values (null, "*");

Afterwards I got no rows from the second select statement.

Bitten by the null again :-).

try instead:

select * from t2
 where number not in (select number from t1 where number is not null)
    and name   not in (select name from t1 where name is not null)

Or constrain t1 to not null.

Quote:
} -----Original Message-----

} Sent: Tuesday, September 30, 1997 6:32 PM

} Subject:      Boolean complement of a pair of OR clause
}
} WOW! That sounds like an esoteric topic!
}
} I have data in a temp table ready to be inserted into a main table.
} However, the main table has two columns, each of which must remain
} unique. (So why don't my users allow me to create uniqueness
} constraints? That's another story. Grrr!)  So before I do the
}    INSERT INTO main-table SELECT * FROM temp-table
} I run a query to see if there are any rows in the temp table that, if
} inserted to the main table, would violate the above conventions.
}
} As it happens, the temp table has 422 rows. When I seek potential
} violations, I get 20 rows. Therefore, if I seek non-violators, I
} should
} get 402 rows.  In fact, I should be able to just do:
}    INSERT INTO main-table
}    SELECT * FROM temp-table
}     WHERE no violations would occur.
}
} Instead, I am getting 0 rows inserted.  When I do a select count for
} non-violators, I get 0.
}
} Now, if you have held on this long, I can get specific.  I have a
} small
} example of what I want.  It works in the small example, however.
}
} create table t1
} ( number        integer,        -- Intended to be unique
}   name          char(2)         -- Intended to be unique
} );
} create table t2
} ( number        integer,
}   name          char(2)
} );
} insert into t1 values (1, "A");
} insert into t1 values (2, "B");
} insert into t1 values (3, "C");
} insert into t1 values (4, "D");
} insert into t1 values (5, "E");
} insert into t1 values (6, "F");
} insert into t1 values (7, "G");
} insert into t1 values (8, "H");
} insert into t1 values (9, "I");
}
} insert into t2 values (11, "R");
} insert into t2 values (12, "S");
} insert into t2 values (13, "T");
} insert into t2 values (14, "U");
} insert into t2 values (15, "V");
} insert into t2 values (16, "W");
} insert into t2 values (17, "X");
} insert into t2 values (18, "Y");
} insert into t2 values (19, "Z");
}
} insert into t2 values (2, "J");         -- Violates uniquness of
} number
} insert into t2 values (10, "B");        -- Violates uniquness of name
} insert into t2 values (3, "D");         -- Violates both uniqunesses
}
} Suppose I want to merge the contents of table t2 into t1. Looking out
} for potential violators, I run the following query:
}
} select * from t2
}  where number in (select number from t1)
}     or name   in (select name from t1)
}
} Sure enough, I get the expected results, the ones I commented about as
} violators.
}
}      number name
}
}           2 J
}          10 B
}           3 D
}
} Now suppose I want to see the rows in t2 that I *can* insert into t1
} in
} compliance with the uniqueness "convention". Then my WHERE clause
} needs
} to be the logical complement of the above WHERE clause.  Recalling my
} boolean algebra, not (A or B) is the same as (not A) and (not B).
} Hence,
} the WHERE clause to seek out valid rows is:
}
} And, sure enough, I get:
}      number name
}
}          11 R
}          12 S
}          13 T
}          14 U
}          15 V
}          16 W
}          17 X
}          18 Y
}          19 Z
}
} So what's the problem?  To my chagrin, when I do this pair of WHERE
} clauses against a much larger table (a customer table with thousands
} of
} rows) the "valid row" query returns 0 rows.
}
} THIS IS NUTS!  And it's turning me into squirrel bait myself!
}
} Does someone have a large table thaey can test this kind of logic
} against?
}
} Thanks for at least *reading* this far!  I have been on the phone with
} Informix tech support and, since they see the small example working on
} their end (as well as in my small example) I'm getting little
} sympathy.
} --
}     -- Jake (In pursuit of undomesticated semi-aquatic avians)
} +---------------------------------------------------------------+
} |Insofar as manifestations of functional deficiencies are agreed|
} |by any and all concerned parties to be imperceivable, and are  |
} |so stipulated, it is incumbent upon said heretofore mentioned  |
} |parties to exercise the deferment of otherwise pertinent       |
} |maintenance procedures.                                        |
} +--------------------------------------- (www.hardyharhar.com) -+



Sun, 19 Mar 2000 03:00:00 GMT
 Boolean complement of a pair of OR clause

Quote:

> WOW! That sounds like an esoteric topic!

> I have data in a temp table ready to be inserted into a main table.
> However, the main table has two columns, each of which must remain
> unique. (So why don't my users allow me to create uniqueness
> constraints? That's another story. Grrr!)  So before I do the
>    INSERT INTO main-table SELECT * FROM temp-table
> I run a query to see if there are any rows in the temp table that, if
> inserted to the main table, would violate the above conventions.

..SNIP..

Although I read from other contributors that this is a NULL problem, the
version 7 SET VIOLATIONS statement is wonderfully useful in sorting out
this sort of problem. You can get it to ignore constraint violations so
only the unique rows are loaded.

Peter
--
Peter Lancashire
Information Systems Specialist, Bayer plc
Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK
Tel: +44-1635-562258,  Fax: +44-1635-562281

My Internet plumbing does not allow me to mail and post news together.
Sorry.
All opinions are my own and not those of Bayer plc.



Fri, 24 Mar 2000 03:00:00 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. Comparing a pair of values in a IN clause

2. Boolean expression in select clause

3. Problem with boolean WHERE-clause

4. sysobjects complement

5. @@error_text to complement @@error??

6. Web access complement to Business Objects

7. Press: Leading Vendors Demonstrate Data Warehouse to Complement AS/400 Sites

8. More complements

9. How to pivot a table of name/value pairs based on the lastest timestamp for a given pair?

10. creating a table of pairs of record IDs for records with similar content in text field

11. Why does update become delete insert pair?

12. update replicates as delete/insert pair


 
Powered by phpBB® Forum Software