Hi Lidia,
Is it true for you all that you will only have multiple PAYMENT_ID_NBRs in the case of credits reversals? I think we are set-up in such a way that we can have several payment IDs for a variety of reasons - although this is a bit outside of my wheelhouse.
From a Query Manager perspective, to remove any instance of multiple PAYMENT_ID_NBRs I wonder if you can just get away with creating a COUNT() expression, and setting HAVING criteria against it:
1. Write the expression (make sure the Aggregate Function box is checked).
You might want to leave the "Distinct" part of the expression out, but I think you will want to use it. I would suggest trying it both ways because it will likely make a big difference.
2. Return the count as a field. This is important because it creates "Group By" logic in the SQL, that tells the query how to partition the Count.
3. Add The criteria, setting the expression to be "not greater than" 1:
If you run the query, it should drop all instances where there is more than 1 PAYMENT_ID_NBR.
You will not find the criteria in the "Criteria" tab, but instead it will exist on the "Having" tab - where aggregate filtering sits.
Let me know if you want to talk through it a bit more. :)
Jeffrie
-- Jeffrie Brooks | BUSINESS SYSTEM ANALYST UNIVERSITY OF MICHIGAN | INFORMATION AND TECHNOLOGY SERVICES | |