Full-outer join in ReportNet preview SQL

[devcog]

Hi,
I have a model query subject which fetches data from 2 different schema. So, when I include the fields in the query subject definition and preview the SQL generated, ReportNet applies a full outer join and fetches duplicate records.
I have verified the joins applied by running a query in the back-end applying the same joins.
Can you please let me know the reason for this behaviour...?

Thanks,

 

[Prasad678]

Do u have many to many joins defined ??
Or, does any of ur query subject has 1 to many joins with more than 1 query subject?



Prasad
RPrasad1@Chn.Cognizant.com

 

[devcog]

Hi Prasad,
Here is the scenario:
I have 4 tables from Schema A and 2 tables from Schema B.
Tables in Schema A:
B
F
AM
M
Relationship between tables in Schema A:
M (1) : AM (M)

Tables in Schema B:
T
A
Relationship between tables in Schema B:
A (1) : T(M)

Relationship between Schema A and Schema B:
[A].B (1) : .T (M)
[A].F (1) : .T (M)
[A].AM (1) : .T (M)

So, yes there is a query subject having one-to-many (1:M) relationship with the other query subjects.

Please let me know whether this would have an impact....

Thanks,

 

[Prasad678]

Sorry for the delay in replying...

I faced the same scenario... Try converting all the 1 - M joins to 1-1.

Logically speaking, if we are going to write a SQL to pull data from 2 tables, we never bother about 1-1 or 1-M. The SQL is same for both the cases.

Keeping 1-M relation leads to multiple SQLs. CRN splits the SQL and uses 'Stich Query' to stich both the query.

Chk out having 1-1 join thru out the model except for outer joins


Prasad
RPrasad1@Chn.Cognizant.com

 

[devcog]

No problem Prasad....

Strangely, even though the full outer join is applied by Cognos in SQL preview, the data in the report was accurate.

Thanks again for the help...