RE: 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.
} -----Original Message-----
} From: Jacob Salomon [SMTP:jake@garpac.com]
} Sent: Tuesday, September 30, 1997 6:32 PM
} To: informix-list@rmy.emory.edu
} 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) -+