In OBIEE/OAC we sometimes observe that for a report, 2 select queries are generated, each using a different fact, in the session logs. Those 2 queries are then stitched using a full outer join and that outer join is created on key columns from both the queries. Something like this:

SAWITH1 AS (select F1.D1, F1.D2, F1.Ky1, F1.Ky2 from FACT1 F1),
SAWITH2 AS (select F2.D1, F2.D2, F2.Ky1, F2.Ky2 from FACT2 F2),

SAWITH3 AS (select T1.D1,T2.D2 FROM SAWITH1 T1 full outer join SAWITH2 T2 ON
T1.Ky1=T2.Ky1)

In this snippet D1, D2 are 2 dim columns and Ky1, Ky2 are two columns from each fact table FACT1 and FACT2. And the 2 queries are joined at FACT1.Ky1 and FACT2.Ky1 only. I want to know if there’s a way to change the full outer join conditions in include another join condition, say FACT1.Ky1 and FACT2.Ky1, as well as FACT1.Ky2 and FACT2.Ky2, so the outer join statement will become something like this:

SAWITH3 AS (select T1.D1,T2.D2 FROM SAWITH1 T1 full outer join SAWITH2 T2 ON
T1.Ky1=T2.Ky1
and T1.Ky2.T2.Ky2)

Please let me know if more explanation is required in case I am not being very clear here.