Skip to main content

Inclusive and exclusive queries

Discover how inclusive and exclusive queries work and their query performance behavior. A report that does not join rows generates a list of values that a column in the main report must match. When you use "Equals," the report generates an "IN" clause. When you use "Not Equals," the report generates a "NOT IN" clause. An inclusive query uses an "IN" clause, while an exclusive query uses a "NOT IN" clause.

Scenario

Several independent reviewers need to verify a case. A reviewer can use GetNextWork or similar functionalities to access a review-focused work queue assignment. The query should only return work queue assignments that the reviewer has not reviewed before.

You can use the following multiple case structures:

  1. Create N child cases and route each child case to the same work queue. The parent case should wait at a different work queue to set the AllCoveredResolved ticket..
    • Capture reviewers as child case pyWorkParty() pages only.
  2. Create a Reviewers() page list with N empty pages. Use that page list for Split For Each routing, where the system routes each pxFlow() to the same work queue. The Split For Each routing can wait for some or all of the reviewers to complete their task.
    • Capture reviewers as pyWorkParty() pages only.
  3. Define a Reviewer Queue component based on the existing Approval Queue component. Pause the case at the Reviewer Queue component's subflow, which routes to a single work queue.
    • Capture within a .Reviewers() page list of class Data-ReviewDecision.
    • Make Data-ReviewDecision reference a Data-Reviewer (which holds a list of operators entitled to review). 
    • Ensure that each page in the page list references e a different reviewer.
  4. Use a variation of the Reviewer Queue component approach, with the following differences:
    • Save the Data-ReviewDecision instances in the CustomerData schema.
    • Add a Case Reference property in the Data-ReviewDecision class. The value of the property is the key to the case that pauses at the Approval Queue component’s work queue.
    • Ensure that every Data-ReviewDecision instance that references the same case references a different reviewer. 

The advantage of options C and D over options A and B is that a separate Data-ReviewDecision class is specifically designed for entering a review decision. The Data-Party class does have a TrueFalse pyApproval property. Additionally, you can use @baseclass-inherited Text properties, such as pyNote or pyDescription, to record reviewer comments. However, while this approach might solve the data model issue, adding reviewer-specific UI and behavior rules to the Data-Party class is not appropriate.

To use a query for option C, you define a Declare Index rule that saves the same properties as in the Data-ReviewDecision class. This approach is not a best practice, resulting in performance complexity, data redundancy in both case blob and index table, re-indexing existing data if declare index is introduced later releases of application so this method is rejected. An additional disadvantage of the Reviewer Queue component structure is that case locking might be an issue.

Option B has the same issue with same-case locking potential. This method has an advantage over approach A in that Split For Each supports a Some condition.

You can set child cases to act the same way. A Declare Trigger activity can set a ticket that communicates to the parent case that it can move forward. This type of coding adds unnecessary complexity.

Approach D does not  have any same-case locking issue and is not complex. The Reviewer Queue component encapsulates complexity that you can reuse. The component saves Data-ReviewDecision instances that both reference a case and a Dat a-Reviewer instance. The component also supports an exit threshold for the number of approvals and a separate exit threshold for the number of rejections. This approach also avoids the creation of cases for what is, at most, a short process.

Solution design

Some different solutions to the scenario discussed include the following:

The filter value [workqueue] in the following examples refers to a work queue for reviews that the reviewer's access role permits them to access. The specific case type to which the work queue assignment points is irrelevant. If the case type is relevant, you can add a query filter.

The queries for options A and B require a pxPartyRole filter because Index-WorkParty supports multiple roles. The Data-ReviewDecision class is role-specific. Instances of that class only apply to cases that require a review decision that one or more reviewers make.

Option D solution

The solution for option D requires the work queue and Data-ReviewDecision to point to the same case, as shown in the following example:

SELECT pzInsKey FROM {Assign-WorkBasket} WB, {Data-ReviewDecision} REV WHERE WB.pxAssignedOperatorID = [workqueue] AND WB.pxRefObjectKey = REV.CaseReference AND REV.ReviewerKey NOT IN (SELECT REV2.ReviewerKey FROM {Data-ReviewDecision} REV2 WHERE WB.pxAssignedOperatorID = [workqueue] AND WB.pxRefObjectKey = REV2.CaseReference AND REV2.ReviewerKey = OperatorID.pyUserIdentifier)

Option B solution

The solution for option B is more complex than option D. The work queue and WorkParty Index must point to the same case. You should filter the WorkParty Index rows by pxPartyRole = 'Reviewer'.

SELECT pzInsKey FROM {Assign-WorkBasket} WB, {Index-WorkPartyUri} WP WHERE WB.pxAssignedOperatorID = [workqueue] AND WB.pxRefObjectKey = WP.pxInsIndexedKey AND WP.pxPartyRole = 'Reviewer' AND WP.pxInsIndexedKey NOT IN (SELECT WP2.pxInsIndexedKey FROM {Index-WorkPartyUri} WP2 WHERE WB.pxAssignedOperatorID = [workqueue] AND WB.pxRefObjectKey = WP2.pxInsIndexedKey AND WP2.pxPartyRole = 'Reviewer' AND WP2.pyPartyIdentifier = OperatorID.pyUserIdentifier);

 

Option A solution

The solution for option A is the most complex because it requires matching the work queue rows to WorkParty Index rows that are associated with child cases. The pxCoverInskey for the child case is what matches the pxRefObjectKey for the work queue. The query needs to know the work pool class for the child case. That query cannot JOIN to Work-Cover- class because it is an abstract class. Instead, you create a review child case class below, such as MDC-DS-Work-TruckServiceApproval.

SELECT pzInsKey FROM {Assign-WorkBasket} WB, {Index-WorkPartyUri} WP, {MDC-DS-Work-TruckServiceApproval} CHILD WHERE WB.pxAssignedOperatorID = [workqueue] AND WB.pxRefObjectKey = WP.pxInsIndexedKey AND WP.pxPartyRole = 'Reviewer' AND WP.pxInsIndexedKey = CHILD.pzInsKey AND CHILD.pxCoverInskey NOT IN (SELECT CHILD2.pxCoverInskey FROM {MDC-DS-Work-TruckServiceApproval} CHILD2 WHERE WB.pxAssignedOperatorID = [workqueue] AND WB.pxRefObjectKey = WP.pxInsIndexedKey AND WP.pxPartyRole = 'Reviewer' AND WP.pxInsIndexedKey = CHILD2.pzInsKey AND WP.pyPartyIdentifier = OperatorID.pyUserIdentifier);

 

Check your knowledge with the following interaction:

This Topic is available in the following Module:
If you are having problems with your training, please review the Pega Academy Support FAQs.

Did you find this content helpful?

Yes
No

Want to help us improve this content?
Suggest an edit

We'd prefer it if you saw us at our best.

Pega Academy has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice