KB9948: “ORA-01417: a table may be outer joined to at most one other table” error message appears when executing a report against an Oracle warehouse.

SYMPTOM:
When running reports with outer-joined metrics against an Oracle warehouse, the following ODBC error appears:
 
ORA-01417: A table may be outer joined to at most one other table
A similar error may be returned in other databases such as Informix and SQL Server. For SQL Server, the error will occur only if join-89 syntax is used. Join-89 syntax places the join conditions in the where clause, whereas join-92 syntax places them in the from clause.
 
A report that will cause this error in Oracle is illustrated below. The report contains one attribute, Quarter, and the following three metrics:
 

• Sum (Revenue) {~}
• Sum (Revenue) {~} <Year = 2000>
• Sum (Revenue) {~} <Year = 2001>

The metric conditions are used to force each of the metrics to be calculated in a separate pass. This report will generate the following SQL:
 
create table ZZMD00 nologging as
select a11.QUARTER_ID QUARTER_ID,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from QTR_CATEGORY_SLS a11
group by a11.QUARTER_ID
create table ZZMD01 nologging as
select a11.QUARTER_ID QUARTER_ID,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from QTR_CATEGORY_SLS a11,
   LU_QUARTER a12
where a11.QUARTER_ID = a12.QUARTER_ID
   and a12.YEAR_ID in (2000)
group by a11.QUARTER_ID
create table ZZMD02 nologging as
select a11.QUARTER_ID QUARTER_ID,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from QTR_CATEGORY_SLS a11,
   LU_QUARTER a12
where a11.QUARTER_ID = a12.QUARTER_ID
   and a12.YEAR_ID in (2001)
group by a11.QUARTER_ID
select decode(pa1.QUARTER_ID, NULL, pa2.QUARTER_ID, pa1.QUARTER_ID) QUARTER_ID,
   a11.QUARTER_DESC QUARTER_DESC,
   pa1.WJXBFS1 WJXBFS1,
   pa2.WJXBFS1 WJXBFS2,
   pa3.WJXBFS1 WJXBFS3
from ZZMD00 pa1,
   ZZMD01 pa2,
   ZZMD02 pa3,
   LU_QUARTER a11
where pa1.QUARTER_ID = pa2.QUARTER_ID and
   decode(pa1.QUARTER_ID, NULL, pa2.QUARTER_ID, pa1.QUARTER_ID) = pa3.QUARTER_ID and
   decode(pa1.QUARTER_ID, NULL, pa2.QUARTER_ID, pa1.QUARTER_ID) = a11.QUARTER_ID
drop table ZZMD00
drop table ZZMD01
drop table ZZMD02
CAUSE:
The error is the result of the Decode function calls in the where clause of the final pass. Decode (or Coalesce, in some databases) in this context returns the first item in the list that is not null and is used here to implement a full outer join in join-89 syntax (that is, an outer join in which null values may be found in either column being joined).
 
The Strategy SQL Engine generates this syntax when the VLDB property "Full outer join support" is set to "support".
 
Some databases, like Oracle and Informix, do not support full outer joins constructed in this way when they are applied to more than one table. They will return an error when presented with this syntax.
 
ACTION:
For such databases, the "Full outer join support" VLDB property should be set to "No support." This is the default setting of this VLDB property for databases that do not support direct full outer joins.
 
If the VLDB property is changed to "No support", the same report will generate the following SQL. Observe the alternate syntax generated in the Where clause of the final pass:
 
create table ZZMD00 nologging as
select a11.QUARTER_ID QUARTER_ID,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from QTR_CATEGORY_SLS a11
group by a11.QUARTER_ID
create table ZZMD01 nologging as
select a11.QUARTER_ID QUARTER_ID,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from QTR_CATEGORY_SLS a11,
   LU_QUARTER a12
where a11.QUARTER_ID = a12.QUARTER_ID
   and a12.YEAR_ID in (2000)
group by a11.QUARTER_ID
create table ZZMD02 nologging as
select a11.QUARTER_ID QUARTER_ID,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from QTR_CATEGORY_SLS a11,
   LU_QUARTER a12
where a11.QUARTER_ID = a12.QUARTER_ID
   and a12.YEAR_ID in (2001)
group by a11.QUARTER_ID
create table ZZOJ03 nologging as
select pa1.QUARTER_ID QUARTER_ID
from ZZMD00 pa1
union
select pa2.QUARTER_ID QUARTER_ID
from ZZMD01 pa2
union
select pa3.QUARTER_ID QUARTER_ID
from ZZMD02 pa3
select pa4.QUARTER_ID QUARTER_ID,
   a11.QUARTER_DESC QUARTER_DESC,
   pa1.WJXBFS1 WJXBFS1,
   pa2.WJXBFS1 WJXBFS2,
   pa3.WJXBFS1 WJXBFS3
from ZZOJ03 pa4,
   ZZMD00 pa1,
   ZZMD01 pa2,
   ZZMD02 pa3,
   LU_QUARTER a11
where pa4.QUARTER_ID = pa1.QUARTER_ID (+) and
   pa4.QUARTER_ID = pa2.QUARTER_ID (+) and
   pa4.QUARTER_ID = pa3.QUARTER_ID (+) and
   pa4.QUARTER_ID = a11.QUARTER_ID
drop table ZZMD00
drop table ZZMD01
drop table ZZMD02
drop table ZZOJ03