Unexpected results on JOIN

[webartus]

I am struggling to get Cognos to work the way I want it to. Here is my info:

ID
NAME
PRIZE = '2' AND [PRIZE_AMOUNT] is not null and [PRIZE_AMOUNT] > 0
PRIZE <> ‘5’
PRIZE_AMOUNT
FIELD_81 is not null
NOTIFICATION <> ‘CURRENT’

Each customer can have multiple notifications. If the customer has received ‘CURRENT’ notification, they should be excluded from the list even if they have other notifications. If the customer has received PRIZE = ‘5’ then they should be excluded from the list even if they have PRIZE='2' or any other PRIZE.

I have tried several approaches but cannot get the result that I am looking for. Any help/suggestions you brilliant folks can offer would be much appreciated.

 

[blom0344]

Your scenario cannot be resolved by joins and filters. Cognos allows you to build unions and except sets that can be used for this purpose:

1. Create a query that fetches all customers that should be removed from the final result (NOTIFICATION <> 'CURRENT' or PRIZE = '5')

2. Use this query definition as the second set in an except. The first part of the except is a query that fetches those customers that are returned by affirmative filters.

The restriction on union and except operators is that the type and number of query items is the same for both queries in the set.

Ties Blom