The loop problem is this: Business Objects doesn’t know how to connect to Table 3.
(1) should it connect using the join from Table 1 to Table 2, then Table 2 to Table3?
(2) Should it just use the join from Table 1 to Table 3? You can resolve this by creating an alias for Table 3 and using that for the Table 1 to Table 3 join.
So your new join would read:
T1.PdtId = T3_Alias.PdtId
Sincerely,
Roger Poole
rpoole@nswc.navy.mil
I am using the following joins
T1.TrxId = T2.TrxId
T2.custId = T3.custid
T1.PdtId = T3.PdtId
When I run Integerity Check, It gives me the following error message Loop Found : T1 —> T2 -> T3 —> T1
. . .
Depending on your entity relationships and the rest of your universe, another approach you may want to attempt is to make the join between T1 and T3 a shortcut join. Therefore, when the user chooses objects referencing T1 and T3, the joins between T1 and T2 and T2 and T3 would not be used.
Ramesh Narayanan had a problem concerning three tables and three joins. All suggestions sofar were pointing him on how to make BO choose at the most two of the three joins.
What if, in his case you need ALL THREE joins to get correct results. I had a similar case with one of my universe where T1 was a delivery-head, T2 was a delivery-row and T3 a reclamation which could only be joined to the correct delivery-row using both elements from the delivery head (delivered to…) and delivery row (rownumber). The head and rows in turn could be joined using the delivery number.
This kind of situation is something you might expect in a fully normalized transaction based database that only uses row to row navigation. Only solution that worked in this case was to hide this functionality from Business Objects using a view.
Ramesh Narayanan had a problem concerning three tables and three joins. All suggestions sofar were pointing him on how to make BO choose at the most two of the three joins.
What if, in his case you need ALL THREE joins to get correct results. I had a similar case with one of my universe where T1 was a delivery-head, T2 was a delivery-row and T3 a reclamation which could only be joined to the correct delivery-row using both elements from the delivery head (delivered to…) and delivery row (rownumber). The head and rows in turn could be joined using the delivery number.
No problem for BO, simply ignore the “error messages” when checking the univers. To be more precise: you have to include all three joins into the same context (if any) or (if none) ignore the messages. in this case BO will use all three joins whenever you create a query. To force the usage of all three joins for a single object (from a single table) force the usage of the other tables by including them into the object’s definition (tables button in object properties). This will force BO to use the additional tables, and, all related joins…
Hope this helps.
Walter.
DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at
What if, in his case you need ALL THREE joins to get correct results. I had a similar case with one of my universe where T1 was a delivery-head, T2 was a delivery-row and T3 a reclamation which could only be joined to the correct delivery-row using both elements from the delivery head (delivered to…) and delivery row (rownumber). The head and rows in turn could be joined using the delivery number.
I’m not sure I understood the issue correctly, but: Designer can force a join using the Tables… -button in object properties dialog. In this case, creating an alias for T3 (or T2?) and then forcing the join all the way would give correct results(…?)
Harri Kinnunen
Consultant/BusinessObjects
Enator Group, Finland.
Hi Ranayara,
. . .
Judging from your response, it looks like you need all joins to be ‘active’ to select the correct row from T3. I’ll assume this is true and proceed with the explanation. I created some tables on my system to test this out and it worked.
I have these tables defined: T1, T2, T3, and T3_Alias. T1 joins T2 on TrxId.
T1 joins T3 on PdtId.
T2 joins T3_Alias on custid.
The objective would be to retrieve info from T3 based on selecting a particular TrxId and PdtId. The custid is provided automagically by the join to T2.
The following SQL was generated by my system and retrieved the correct (I hope) row from T3.
SELECT
RPOOLE.T3.CUSTID,
RPOOLE.T3.PDTID,
RPOOLE.T3.T3TEXT
FROM
RPOOLE.T3,
RPOOLE.T1,
RPOOLE.T2
WHERE
( RPOOLE.T1.TRXID=RPOOLE.T2.TRXID )
AND ( RPOOLE.T1.PDTID=RPOOLE.T3.PDTID )
AND ( RPOOLE.T1.TRXID = 12 AND RPOOLE.T2.CUSTID = 23 )