on 03-27-2011 4:03 AM
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.).
User | Count |
---|---|
81 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.