We have eight tables to join.

One of these tables, CUSTOMER_CONNECTION_DIM, is temporarily missing
data pending completion of load routines.

This missing data is preventing data from other tables from being
returned because the equi join between CUSTOMER_DIM and
CUSTOMER_CONNECTION_DIM tables returns no values.

In order to avoid this problem for now I created a right outer join on
the CUSTOMER_DIM and CUSTOMER_CONNECTION_DIM tables.

Sybase is returning an error:

The table 'CUSTOMER_DIM' is an inner member of an outer-join clause.
This is not allowed if the table also participates in a regular join
clause.


The error message is correct in stating that CUSTOMER_DIM is used as
an inner member of an outer join and elsewhere in the query as a
member of an inner join.



First Question

Why is this an error? I don't understand why this should be considered
and error. All I want to do is return some NULL values from the
CUSTOMER_CONNECTION_DIM table so that the whole join path is not
invalidated because values could not be found in the
CUSTOMER_CONNECTION_DIM table.



Second Question

How do I get around it?



Below is the complete SQL Statement

Thanks

J.


This SQL essentially follows the joins required for a more complex
statement to be written at a later date. It was used to track down why
certain expected records were not being returned. The problem was
determined to be that certain records were not found in the
CUSTOMER_CONNECTION_DIM table.

Notice the right outer join:

CUSTOMER_DIM.Customer_Key=*CUSTOMER_CONNECTION_DIM .Customer_Key


SELECT
CONTRACT_FACT.Customer_Key,
CUSTOMER_DIM.Customer_Key,
CUSTOMER_CONNECTION_DIM.Customer_Key,
CONTRACT_FACT.Time_Key,
TIME_DIM.Time_Key,
CONTRACT_FACT.Customer_Facility_Key,
CUSTOMER_FACILITY_DIM.Customer_Facility_Key,
CONTRACT_FACT.Industry_Key,
INDUSTRY_DIM.Industry_Key,
CONTRACT_FACT.Product_Key,
PRODUCT_DIM.Product_Key,
CONTRACT_FACT.Facility_Key,
FACILITY_DIM.Facility_Key,
CUSTOMER_DIM.Cards_Customer_Id
FROM
CONTRACT_FACT,
PRODUCT_DIM,
FACILITY_DIM,
CUSTOMER_DIM,
CUSTOMER_CONNECTION_DIM,
INDUSTRY_DIM,
TIME_DIM,
CUSTOMER_FACILITY_DIM
WHERE
( CUSTOMER_DIM.Customer_Key=CONTRACT_FACT.Customer_K ey )
AND ( CONTRACT_FACT.Product_Key=PRODUCT_DIM.Product_Key )
AND ( CUSTOMER_DIM.Customer_Key=*CUSTOMER_CONNECTION_DIM .Customer_Key
)
AND ( CONTRACT_FACT.Industry_Key=INDUSTRY_DIM.Industry_K ey )
AND ( CONTRACT_FACT.Facility_Key=FACILITY_DIM.Facility_K ey )
AND ( TIME_DIM.Time_Key=CONTRACT_FACT.Time_Key )
AND ( CUSTOMER_CONNECTION_DIM.Primary_Connection_Flag = "Y" )
AND ( CUSTOMER_FACILITY_DIM.Customer_Facility_Key=CONTRA CT_FACT.Customer_Facility_Key
)
AND (
CONTRACT_FACT.Customer_Key = 8558
)

Jean Legare wrote:

Just a quick answer (no time to go into detail right now),
since an outer join is the same as an inner join when the
rows are present in both tables, you can sometimes change
the inner join to an outer join, which makes the query
work and still gives the correct result.

Why are you using the old and deprecated *= syntax instead of the
[LEFT | RIGHT | FULL] OUTER JOIN syntax? The old stuff NEVER worked
properly..

Here is how OUTER JOINs work in SQL-92. Assume you are given:

Table1 Table2
a b a c
====== ======
1 w 1 r
2 x 2 s
3 y 3 t
4 z

and the outer join expression:

Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a <== join condition
AND Table2.c = 't'; <== single table condition

We call Table1 the "preserved table" and Table2 the "unpreserved
table" in the query. What I am going to give you is a little
different, but equivalent to the ANSI/ISO standards.

1) We build the CROSS JOIN of the two tables. Scan each row in the
result set.

2) If the predicate tests TRUE for that row, then you keep it. You
also remove all rows derived from it from the CROSS JOIN

3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.

So let us execute this by hand:

Let @ = passed the first predicate
Let * = passed the second predicate

Table1 CROSS JOIN Table2
a b a c
=========================
1 w 1 r @
1 w 2 s
1 w 3 t *
2 x 1 r
2 x 2 s @
2 x 3 t *
3 y 1 r
3 y 2 s
3 y 3 t @* <== the TRUE set
4 z 1 r
4 z 2 s
4 z 3 t *

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
3 y 3 t <= only TRUE row
-----------------------
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
-----------------------
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL <== derived from the TRUE set - Remove
3 y NULL NULL
-----------------------
4 z NULL NULL
4 z NULL NULL
4 z NULL NULL

the final results:

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL

The basic rule is that every row in the preserved table is represented
in the results in at least one result row.

There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products. Consider the two Chris Date tables

Suppliers SupParts
supno supno partno qty
========= ==============
S1 S1 P1 100
S2 S1 P2 250
S3 S2 P1 100
S2 P2 250

and let's do an extended equality outer join like this:

SELECT *
FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
AND qty < 200;

If I do the outer first, I get:

Suppliers LOJ SupParts
supno supno partno qty
=======================
S1 S1 P1 100
S1 S1 P2 250
S2 S2 P1 100
S2 S2 P2 250
S3 NULL NULL NULL

Then I apply the (qty < 200) predicate and get

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100

Doing it in the opposite order

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
S3 NULL NULL NULL

Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds! In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;

... or do it in the joining:

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;

Another problem is that you cannot show the same table as preserved
and unpreserved in the extended equality version, but it is easy in
SQL-92. For example to find the students who have taken Math 101 and
might have taken Math 102:

SELECT C1.student, C1.math, C2.math
FROM (SELECT * FROM Courses WHERE math = 101) AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math = 102) AS C2
ON C1.student = C2.student;

joe.celko (AT) northface (DOT) edu (--CELKO--) writes:

Hmm. I had assumed that the limitation on using the same table as an
inner member of an outer join (which I think means the unpreserved
table) and in a regular join was a limitation of the Sybase query
engine. If by using the SQL92 syntax one avoids this, that's a good
reason to switch. Sadly the last time I tried SQL92 join syntax,
Sybase 11.9.2 didn't like it, but I hope to move to 12.x soon.

--
Ed Avis <ed (AT) membled (DOT) com>