Problem With Outer Joins in BO

[PruSQLer]

I can create a left outer join using my two common ID fields in the Join Properties box. However, I need more criteria included for the Null Supplying table (e.g. end_date is null, money_type = 1). But when I create that using the Self Restricting Join, this additional logic gets put in the FROM clause instead of the ON clause in the Outer Join, which pretty much spoils the Outer Join.

Does anyone know of a way to get around this so all criteria goes in the ON Clause?

 

[blom0344]

Probably not, but adding a self-restricting condition should not spoil the outer join principle at all.
Are you using DB2?

T. Blom
Information analyst
tbl@shimano-eu.com

 

[PruSQLer]

Good job - yes, DB2.

But it does matter to the Outer Join because the criteria now becomes a "before join" issue. So, I get reduced "NULL" results when that criteria goes in the Where Clause. You don't agree?

 

[blom0344]

We are into semantics here.
I imagine table1 and table2 joined with outer join on table1 (master). So data is returned from table1 no matter if there is a matching record from table2.
So the null supplying table is table2. Adding restriction on table2 so no null values will be returned from it can still be done while getting all from table1.

The "ON" construction DB2 uses will only be created for a join between two table fields . Anything else will fit into the FROM ... WHERE clause as a condition........

Am I missing something?

T. Blom
Information analyst
tbl@shimano-eu.com

 

[PruSQLer]

It's not semantics, it's results. If I put the 2 additional predicates for the Null Supplying table in the WHERE clause, DB2 turns it into an Inner Join which isn't what I want. Check this out from Terry Purcell of IBM:

A WHERE clause predicate must evaluate to TRUE for a row to qualify. If there is an unmatched row in the join, the columns from the NULL-supplying table are NULL. When DB2 compares the WHERE clause predicate D.DEPTNAME NOT LIKE ‘%CENTER%’ to a NULL, the result is neither TRUE nor FALSE, but UNKNOWN. This row is not returned because it does not evaluate to TRUE. Thus, NULLs that are supplied by the left outer join are negated by the WHERE clause predicate. This causes DB2 to determine that a left outer join is unnecessary, and it causes the query to be rewritten as an inner join, which may or may not be what you intended when you coded the query.

Try it out yourself. Code a Left Outer with additional qualifiers (besides key matches) for the Null Supplying table in the ON clause and then try putting them in the WHERE clause.

 

[blom0344]

Semantics still, cause I figured you would want to suppress nulls from the other table.

Firstly, I am referring to DB2 UDB.

Let me quote Graeme Birchall from his SQL cookbooks for DB2:

"An ON check is quite unlike a WHERE check in that the former will NEVER result in a row being excluded from the answer-set (in an outer-join)"

"This is because an ON predicate never removes rows, it simply categorizes them as matching or non-matching. If they match it joins them. If they don't it passes them through"

However when I pass a query like:

SELECT DISTINCT
A,
B,
C
FROM
TABLE1 LEFT OUTER JOIN TABLE2 ON A=C AND
(B) IS NOT NULL

items from table2 do indeed disappear , while all items from table1 are fetched. (thus making it seem an inner join is executed)

confusing isn't it...





T. Blom
Information analyst
tbl@shimano-eu.com

 

[PruSQLer]

Okay, so maybe it was semantics. And yes, outer joins can be confusing. Unfortunately I'm still stuck with Biz Objects putting my additional predicates in the WHERE Clause and not the ON Clause!

Hey, maybe I'll post this in that "other" forum.