CODEWITHSUNDEEP
joincognos-11
Ekfa
Ekfa

Reputation: 115

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?

Upvotes: 0

Views: 2042

Answers (2)

dougp
dougp

Reputation: 3087

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...

  • Use a regular join.
  • Join using customer from both queries.
  • Change the cardinality to 1..1 on the query1 side and 0..1 on the query2 side.
  • In the filters for query3, add a filter for query2.customer is null.

Upvotes: 1

dougp
dougp

Reputation: 3087

EXCEPT is not a join. It is used to compare two data sets.

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-2017

What you need is an INNER JOIN. That would be the join tool in the Toolbox in Cognos.

Upvotes: 0

Related Questions