FM relational modelling (loop between tables)

[rteruyas]

Good day guys
I'm trying to model the following SQL in FM

Code Select Expand


SELECT A.*,
B.*,
C.*
FROM SCHEMA.TABLEA A
JOIN SCHEMA.TABLEB B ON A.NUMFCTFQ=B.NUMFCTFR
JOIN SCHEMA.TABLEC C ON B.DTECHP BETWEEN C.DTEBGN AND C.DTELST
AND C.ENTCOD = A.ENTCOD
AND C.FLAG1 <> 0   
AND C.FLAG2 = 1


I have 3 query subjects in the foundation view: TABLEA, TABLEB and TABLEC
I have created the same same joins A -> B -> C -> A
I created a new query subject in the business view that pulls A.*, B.*, C.*

However, when I do the test on this query subject, I see one relationship is missing

Code Select Expand


FROM SCHEMA.TABLEA A
INNER JOIN SCHEMA.TABLEB B
                ON A.NUMFCTFQ=B.NUMFCTFR
        INNER JOIN SCHEMA.TABLEC
                        C ON B.DTECHP BETWEEN C.DTEBGN AND C.DTELST
WHERE
AND C.FLAG1 <> 0   
AND C.FLAG2 = 1


The SQL is missing
C.ENTCOD = A.ENTCOD
which translates in getting a lot more rows than expected.

Do you have any idea what I'm missing here. I feel like I've been hitting a wall for the last couple of days.
Thanks in advance!

[MFGF]

Good day guys
I'm trying to model the following SQL in FM

Code Select Expand


SELECT A.*,
B.*,
C.*
FROM SCHEMA.TABLEA A
JOIN SCHEMA.TABLEB B ON A.NUMFCTFQ=B.NUMFCTFR
JOIN SCHEMA.TABLEC C ON B.DTECHP BETWEEN C.DTEBGN AND C.DTELST
AND C.ENTCOD = A.ENTCOD
AND C.FLAG1 <> 0   
AND C.FLAG2 = 1


I have 3 query subjects in the foundation view: TABLEA, TABLEB and TABLEC
I have created the same same joins A -> B -> C -> A
I created a new query subject in the business view that pulls A.*, B.*, C.*

However, when I do the test on this query subject, I see one relationship is missing

Code Select Expand


FROM SCHEMA.TABLEA A
INNER JOIN SCHEMA.TABLEB B
                ON A.NUMFCTFQ=B.NUMFCTFR
        INNER JOIN SCHEMA.TABLEC
                        C ON B.DTECHP BETWEEN C.DTEBGN AND C.DTELST
WHERE
AND C.FLAG1 <> 0   
AND C.FLAG2 = 1


The SQL is missing
C.ENTCOD = A.ENTCOD
which translates in getting a lot more rows than expected.

Do you have any idea what I'm missing here. I feel like I've been hitting a wall for the last couple of days.
Thanks in advance!

Hi,

The way the model works when generating queries is to figure out a join path between all the query subjects used. You are using A, B and C in your query, so it will look to see firstly whether these are linked via relationships (which they are), then it will determine which to use. If there is a choice of relationships (ie more than one way of linking from A to C), it will use only one of the relationship paths. Which one? To decide, it looks at the names of the relationships and uses the first alphabetical. Is this what you'd want? Almost certainly not.

For this reason, loops are considered as modelling traps when creating FM models, and should be on your radar as a modeller as something to watch out for and resolve if needed. Often the approach to take is to use alias shortcuts to create new objects to break the loop apart. There is a good section covering this in the FM User Guide:

https://www.ibm.com/support/knowledgecenter/en/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_fm.10.2.2.doc/c_bo-loop_joins.html#bo-loop_joins

In the example in the guide, a good solution is to create an alias shortcut for the Order query subject, and join this alias shortcut to Branch, then remove the relationship between Branch and Order, thus breaking the loop apart.

Cheers!

MF.