Simulating full outer join

[blom0344]

In a very rare case we need to simulate a full outer join between 2 tables. In the framework the relation is defined as an inner join and we would like to keep it that way.

In BO (bus. objects) I used to generate such an effect by defining 2 queries and synchronizing the result-sets over the common dimensions. However that was with a client-tool.

Is something similar possible within report studio?

The 2 tables involved are quite big, so we have our doubts about the performance if we try to solve this within the framework. (a bit anxious what Cognos is going to come up with when generating the SQL)..

Ties Blom

 

[yphilogene]

With Cognos 8, in Report Studio, you can bypass the model and write a SQL Query directly then link several queries. That may be a solution. I don't remember whether you can do the same with ReportNet.

A Framework solution would be to create a copy of one of the Query Subject and to create an outer join. In your model, you will then have "QS" and "Copy of QS" and the end-user must drag&drop one of them depending on the type of relationship he wants to activate.

 

[blom0344]

We certainly do not want our customers to start using straight SQL

What I had in mind is:

1. Fetch data from table 1
2. Fetch data from table 2

And then define a full outer join between the 2 data-sets.

Well, that was pretty standard stuff with the competition, but I do not know whether this possible in Reportnet.

Ties Blom

 

[mfgf]

Hi,

In Cognos 8 using the Query Explorer, you could extract your data using two separate queries, then add a join between the queries using 0..1 -> 0..n cardinality. This should give you the full outer join capabilities you are looking for.

Best regards,

MF.

 

[blom0344]

Hello mfgf,

I expect that in that case the join is executed on the C8 server, instead of in the database that is queried?

Do you have experience with this sort of solution to the full outer join issue? The C8 KB does not yield any information concerning the use of outer joins in any respect (only in the context of stitch queries)

Ties Blom

 

[blom0344]

After some fiddling I managed to create a report like MGMF suggested:
For future reference:
1. Create query1 (identifier1, fact1)
2. Create query2 (identifier2, fact2)
3. Define a third query by introducing a join and setting the proper cardinality 0..n 0..n
4. Associate objects from queries 1 and 2 with the third query.
5. Insert a new list and drag objects from query 3 into the list.
6. Delete lists associated with Q1 and Q2 (cut objects from list and then delete list)
7. To display all identifiers, you still need to use a coalesce construct. The same goes for defining new calculated objects. (null + value = null in Cognos philosophy)

Verdict: doable, but quite a lot of work. (especially compared to BO's solution)




Ties Blom