cancel
Showing results for 
Search instead for 
Did you mean: 

Mixing inner/outer joins in a universe

Former Member
0 Kudos

Hi all. Hoping someone can be of assistance here. Say I have a universe with 3 tables:

Table1

Table2

Table3

Table 3 is a lookup table that will be referenced by Table2, so the join between these 2 should always be an inner join. However Table1 may contain records that do not have a corresponding record in Table2, so the join between Table1 and Table2 needs to be an outer join. It's important to join Table2 to Table3 separately so I don't lose records in Table1 when joining all 3. I know that I could build Table2 and Table3 into a derived table, but I don't really want to force the join if the user doesn't need objects from Table3. Likewise, I don't want the user to have to join to Table2 if all they want is to see a list of what's in Table3.

Aside from creating a derived table, I've had some success with forcing the join from Table2 to Table3 for all Table2 objects, but this again gives us one of the situations I mentioned above that I'm hoping to avoid.

Is there any way to tell Business Objects to join 2 tables as a unit first (in essence, make a subquery out of them)? In straight SQL, I'd write this like:


	WITH Table2_3 AS (
		SELECT [...]
		FROM Table2
		INNER JOIN Table3 ON [...]
	)

	SELECT [...]
	FROM Table1
	LEFT JOIN Table2_3 ON [...]

or:


	SELECT [...]
	FROM Table1
	LEFT JOIN (
		SELECT [...]
		FROM Table2
		INNER JOIN Table3 ON [...]
	)

Any assistance here would be appreciated. Our DB is Oracle 11g, and we're using BOE XIR2.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

I this situations I would apply " Table1 outer join Table 2" and "Table2 outer join Table 3".

Here only the scenario which fails is, the equi join which is required between Table 2 and Table 3

you could create a conditional object where you can set the equi join condition. You should add this object whenever you create queries or reports only with Table 2 and Table 3.

Former Member
0 Kudos

While this is a fine strategy for a simple example like this, it's just a basic example, and certainly not ideal in a string of joins that could end up as long as my arm. Let's say the example were to be a join path that went (in order) from Table1 to Table 15, and Table1 is the only table that should be left joined to the rest. By this strategy, I'd have to make all of those joins outer as well. This could potentially kill query performance. It could (and likely would) get even worse if there were forks along the road somewhere (Table 4 needs a lookup table to Table4a, which in turn links to Table4b, etc.).

Former Member
0 Kudos

The strategy alwasys changes depends on the requirement.

If you have more tables, then we should think of a different strategy! Depends on the question you have posted, I have given the best solution which we could use.

Answers (0)