0

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?

Ekfa
  • 93
  • 3
  • 15

2 Answers2

1

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.
dougp
  • 2,810
  • 1
  • 8
  • 31
0

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.

dougp
  • 2,810
  • 1
  • 8
  • 31
  • OK, that's clear. It's a comparison. That's actually what lies beneath this question when I re-read it. But isn't an INNER JOIN going to give me all corresponding data instead of the unique data from query1? – Ekfa Jun 19 '19 at 06:52
  • In this context, an inner join is effectively a filter. If you have a table with values a, b, c, and another table with values a, b, d. `select T2.C1 from T1 inner join T2 on T1.C1 = T2.C1` will return a and b. – dougp Jun 19 '19 at 16:05
  • this is quite a late comment... Sorry for that... I got distracted... What I actually wanted was a return that would give me 'c' (given the example you used in your latest comment). And within Cognos I don't know how to use a select-statement like you mention. In Cognos there is this screen where you drag and drop your querys. I don't know how to put in SQL statements like this. I do know this way of statements in SQL Developer. – Ekfa Jul 14 '20 at 07:15
  • Yes, old post. Wow! My answer did not address your question. See new answer. – dougp Jul 15 '20 at 20:27