How does except work?

[paloalto_cognos]

Hi,

Can any one explain how the except function works in report studio?

I'm trying to get difference between two queries each has a data item and the output should give me a number.

example [query1].[casperid] - [query2].[committeeid]

please help .....

[Lynn]

I don't think except is what you want. I assume you are referring to the Except item in the toolbox of the query explorer. This is a "minus" in the sense of a database minus operator where you can identify data from one set that is not in the other. It isn't a mathematical subtraction which is what your expression implies. Not sure why you'd subtract to ID fields, so I'm not sure I fully understand what you are trying to do, but below explains except a little...

If query1 returns one column / 4 rows and the values are
A
B
C
D
and query2 returns one column / 3 rows and the values are
A
B
C

then query1 minus query2 would return D.

[paloalto_cognos]

I appreciate your response and understood it but it is not working tht way as you expalined in your example.

I'm actually using the ID column to get the distinct count of casperid and committeeid and also need the difference(substraction) of their counts. on the counts I'm implementing drill through to show wht ID are present in the counts.

the count for casper id  gives the eligible id's

the count for committeeid's gives the represnted id's and thier difference gives the non represented ic's.

Hope it gives some picture of wht I'm trying to do. Hence I was hoping except would be the solution to get the difference as I read it in some forums except does the minus functionality in DB2 database.

But if you could suggest me some other option tht would be really great.... thnks again

[Lynn]

Maybe this is a better explanation than mine: http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/sqlp/rbafyexcept.htm

Except is a set operation, not a mathematical "minus" or subtraction. In Oracle the syntax is "MINUS" while DB2 uses "EXCEPT" as the keyword.

I'm not sure what the overall structure of your two queries looks like, so not sure other suggestions I make would be right but ....

If each query has some other column like a business unit or something you could join based on that. Then you'd have both metrics in the same query and could just create a data item that is a subtraction of the two.

Another option might be to do a union. So query1 as the eligible ids and a dummy item with zero for represented ids. Query2 has a dummy item zero for eligible ids and the represented ids. The union then puts the two queries together so you have the metrics side by side and create a data item that is a subtraction of the two.

Perhaps others will chime in with better advice :)

[CognosPaul]

Your explanation is fine, but maybe it should be expanded a bit.

Cognos will look at every field in the queries when performing set operations.

So if your data looks like:
Table 1
ID    | Count
------+ -----
123   |     5
234   |    45
345   |    20
456   |    98
567   |     1
678   |    17
789   |    33

Table 2
ID    | Count
------+ -----
123   |     5
234   |    24
456   |    13
678   |     6

And you try to do an except operation you'll send up with the following:
Table 1
ID    | Count
------+ -----
234   |    45
345   |    20
456   |    98
567   |     1
678   |    17
789   |    33


Only the ID 123 disappeared because that was the only row in common between the two.

It sounds like you're trying to subtract the count of Table 2 from Table 1 so ID 234 would be 21 (45 - 24). There are a number of ways of doing it. Since we're on set expressions let's go with UNION.


The union of these two tables
Table 1
ID    | Count
------+ -----
123   |     5
234   |    45
345   |    20
456   |    98
567   |     1
678   |    17
789   |    33

Table 2
ID    | Count
------+ -----
123   |     5
234   |    24
456   |    13
678   |     6

would get you

Table 1
ID    | Count
------+ -----
123   |     5
234   |    45
345   |    20
456   |    98
567   |     1
678   |    17
789   |    33
123   |     5
234   |    24
456   |    13
678   |     6

Which, when aggregated, would be

Table 1
ID    | Count
------+ -----
123   |    10
234   |    69
345   |    20
456   |   111
567   |     1
678   |    23
789   |    33

Almost, but it needs to be a subtraction.

Well, that's easy. In the count of the second table just do count([field]) * -1

That would get you
Table 1
ID    | Count
------+ -----
123   |     5
234   |    45
345   |    20
456   |    98
567   |     1
678   |    17
789   |    33

Table 2
ID    | Count
------+ -----
123   |    -5
234   |   -24
456   |   -13
678   |    -6

which would aggregate to
Table 1
ID    | Count
------+ -----
123   |     0
234   |    21
345   |    20
456   |    85
567   |     1
678   |    11
789   |    33

[paloalto_cognos]

Hey

@Lynn  - I would try your example and see if it works

@PaulM  - Thanks a lot for your effort to explain the scenario...  I would try each of these and see the output....

thanks again

[Lynn]

I like Paul's approach!

[MFGF]

I like Paul's approach!

Yep - I'll join the PaulM fan club too! :D

Thanks Lynn and Paul for your great explanations.

MF.

[paloalto_cognos]

Hi Paul/Lynn

I 'm actually not asking for the substraction as Paul explained.  I want to get the the ID's which are not common in those two columns

from your example,

Query1
ID                                 
------                   
123   
234   
345   
456   
567   
678   
789   


Query 2
ID   
------
123   
234   
456   
678
 

If these columns are inserted in each query then How do I get an output which looks like

Query 3
ID
------
345
567
789

Please advice...

[MFGF]

Hi,

If Query 1 and Query 2 contain *only* the ID values you included in your post, using an EXCEPT query operator between them would produce the results you require in Query 3.

If you have other items of data in the queries, these will also be considered when doing the EXCEPT - if you look at Paul's first example, it shows this nicely.

Regards,

MF.

[nthacker]

I have  Query1 that selects the ID's of all Full Time Employees (952)

           Query2 that selects the ID's of Employees that have a review Date (858)

Query3 using the EXCEPT gives me a result list of  125 exceptions instead of 94.

I used Query Explorer to create the Queries and Except. 

I solved my problem using a join and creating a query calculation to filter on but the except looks like it would be much simpler if it worked.  Anyone have any suggestions on what I may be doing wrong?
Thanks

[PRIT AMRIT]

I am not sure if this is what you are looking after.

I suppose you have two queries Query 1 & Query 2. Now Mouser over Query Explorer in Report Studio, click on Queries.

From the Insertable  Objects, drag and drop 'EXCEPT'.  Then Drag and drop Query 1 and Query 2 on it.

Right click on the EXCEPT query and View tabular data, see if you get the desired result?

Thanks

[nthacker]

Thanks for your help.

[CognosPaul]

Does changing the EXCEPT to INTERSECT return 858 or 827 records? 31 rows might not be completely identical.