Loops in Universe

[snshgrdn]

This is the loop:
Table 1 to Table 2, Table 2 to Table 3, Table 3 to Table 4, Table 4 to Table 5 and Table 5 to Table 2.

Info in Table 5 and Table 2 have common columns. When I run two seperate queries, using table 1 and table 2, no problems, but there is column in table 1 that need to be joined to column in table 5.

Tools are detecting loop, but not providing suggested alias tables or contexts. What is next step?

 

[PAULSGS]

You could create an alias of Table1 and call it Table6, remove the join between Table5 and Table2 and create a join between Table5 and Table6.


This will certainly remove the loop, then using your judgment you will have to create some new objects based upon Table6 and use these in your reports instead of or as well as those from Table2

 

[Guest_imported]

Thanks, but I've tried this, and tried again, after checking my joins. Can't figure out what I am doing wrong. The query is acting like my joins aren't working correctly.

When I combine objects from these two tables, whether I use the alias table or original table, I get mutlitple values returned. Table 5 values come back combined with objects from table 3 correctly..add same value from table 1 and result is correct. Add a different object (sales price) and that is where the multiples come in. The sales price is different for different "Price Codes". It is acting like it doesn't even see the "Price Code" match from Table 3.

Table 1 is Sales price lines, linked to Sales prices (table2)...join is used to link the name of the "Price Code" to the items it contains M,1. Table 2 is linked to Customer Addresses (Table 3)where that price code is assigned 1,M because each address can only have 1 "Price Code". Table 3 is linked to Invoice Headers (Table 4) 1,m because each invoice can only have 1 address. Table 4 is linked to Invoice Detail Lines (Table 5) because each invoice can have more than 1 line. Table 5 is linked to Table 6(Alias of Table 1) because it contains detail info Part Code to Part Code m,1 -- each invoice line can only have one "Price Code".

I was under the impression that the joins were read whenever the Query was presented. Since I have already stated through the joins that each Invoice Line can only have one "Price Code". Is that correct?

 

[blom0344]

You have to split the problems you are facing into 2 categories:

1. Resolving loops through adding contexts / aliases
2. avoiding Fan / Chasm traps.


The first category has to do with the fact that you have to force the generated SQL into a certain path to avoid BO splitting a query into 2 synchronized queries.

An alias actually breaks up the loop, contexts make certain that only part of the loop can be used thus disarming it.

However adding contexts /aliases has no effect on the way tables are joined and the resulting output of a query in terms of data missing or non-existing combinations emerging.

I can go into detail , if it were not so clearly described in the designers manual. Take some time to study this and the effect of the 'many-to-one-to-many' and one-to-many-to-many relationships and the effect on data retrieved and the possible solutions to these situations.

Also try to have a look at the SQL that is generated if you are unsure about the correct use of joins. The 'where'part gives exact information about this.

Conclusion could be that your universe design in terms of joins between tables has to be modified as well. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[Guest_imported]

I resolved the problem by changing the properties of the needed item from table 1 and leaving the alias table in place. The change to the property of the needed item from table 1 was made by inserting a where clause that made the object reference two of the other tables in the universe like this--@select(SA Detail Lines\Part Code)=Sales Price Lines.Part Code and @select(Cust Add data\Price Code)=Sales price line.part code. I believe the Designer Manual calls this a self restricting join. Looks like it's working so far.

Thanks for the input.