joincognos-11

How to use the exceptjoin in Cognos-11?

I don't get an except join to work in Cognos-11. Where or what am I missing?

Some understanding for a beginner in this branch would be nice ;-)

What I've tried so far is making two queries. The first one holds data items like "customer", "BeginningDate" and "Purpose". The second query holds data items like "customer", "Adress" and "Community". What I'd like to accomplish is to get in query3: the "customers" from query1 that are not available in query2. To me it sounds like an except-join.

I went to the query work area, created a query3 and dragged an "except-join" icon on it. Then I dragged query1 into the upper space and query2 into the lower. What I'm used to getting with other joins, is a possibility to set a new link, cardinality and so on. Now double clicking the join isn't opening any pop-up. The properties of the except-join show "Set operation = Except", "Duplicates = remove", "Projection list = Manual".

How do I get query3 filled with the data item "customer" that only holds a list of customers which are solely appearing in query1?

Solution

  • In SQL terms, you want

    select T2.C1
from T1
  left outer join T2 on T1.C1 = T2.C1
where T2.C1 is null

    So, in the query pane of a Cognos report...