Re: Open join in SQL?

Path: emory!swrinde!sdd.hp.com!hplabs!pyramid!infmx!johnl From: johnl@informix.com (Jonathan Leffler) Newsgroups: comp.databases.informix Summary: answer = outer join, but be careful Message-ID: <1991Dec12.135511.5617@informix.com> Date: 12 Dec 91 13:55:11 GMT References: <62980004@col.hp.com> Sender: Jonathan Leffler (johnl@informix.com) Organization: Informix Software, Inc. In article <62980004@col.hp.com> judym@col.hp.com (Judy Miller) writes: >How do you do an "open join" in Informix SQL? > >I have an extract of data in one temp table showing qty_needed for a part/opt. >I have a table2 showing the onhandbalances for each part/opt. > >I want to report rows showing that action is required: > > if the onhandbalance < qty_needed > OR THE PART/OPT IS NOT IN THE TABLE2. > >Actually, it gets more complicated in that it takes two columns to >uniquely identify a part/opt. > >Granted, if these tables were designed better, it wouldn't be so awkward. > >Judy At first glance, this is dead easy; you use an OUTER join. SELECT T1,*, T2.* FROM temp_table T1, OUTER table2 T2 WHERE T1.part = T2.part AND T1.opt = T2.opt AND (T2.onhandbalance < T2.qty_needed OR (T2.part IS NULL AND T2.opt IS NULL)); The only problem is that this doesn't produce the required answer because of a feature (full pejorative meaing of the term) in the Informix engines. Because of the way the filtering and outer joining works, any part/opt in T1 which has onhandbalance >= qty_needed will be retained in the answer set of rows with null values against the elements from T2. I have argued against this being correct (before I joined Informix), but I lost then, and it was documented in a set of Tech Notes (Spring 1987 covers outer join, but I couldn't immediately spot this point in the article, and I'm fairly sure the point was covered separately, somewhat later). To obtain the required effect, use: SELECT T1,*, T2.* FROM temp_table T1, OUTER table2 T2 WHERE T1.part = T2.part AND T1.opt = T2.opt INTO TEMP T3; SELECT * FROM T3 WHERE T3.onhandbalance < T3.qty_needed OR (T3.part IS NULL AND T3.opt IS NULL); DROP TABLE T3; I would argue that the split query and the single query should produce the same results; Informix does not agree. Fun, isn't it. -- ------------------------------------------------------------------- Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>