Question
IN
Last activity: 24 Aug 2020 21:23 EDT
Building an IN clause in PEGA Report Definition
Hi Team,
We are building a dynamic query in the PEGA Report Definition where in to a database column we need to send list of values like as we pass in IN clause.
Eg:
Select * from table where organization_id IN (1,2,3,4,5)
But as per current pega capability (Ver. 7.4) we don't see such option available. Is there any alternate option.
***Edited by Moderator Marissa to update Content Type from Discussion to Question***
Pegasystems Inc.
US
You can achieve this by using "Equals" and a value list as the value. This will translate to "IN (<vl1>, <vl2>, ...)" in the generated query for the report.
Updated: 24 Aug 2020 21:23 EDT
Pegasystems Inc.
AU
Hi @MadhusudanR
For further elaboration on the answer from @RonLee_GCS , go to the Filter conditions part of https://community.pega.com/knowledgebase/articles/reporting/84/report-definition-query-tab and review the detail for the Value field. I believe this is still correct for Pega 7.
If the values for your IN clause are dynamic, you will need to prepare a clipboard page with one or more value lists that hold the values for your IN clause. A Data Page is a good way to prepare these values if they are driven by reference data / data from another system of record.
If the values for your IN clause are fixed, the article quotes what I thought was true in that including comma-separated constants in the Value field for an Equals comparison also yields IN clause behavior. See below for what is quoted:
Commas correspond to an OR test: If the Value field contains two or more entries separated by commas, the comparison is true at run time if the value of the Column field equals (or is greater than, or is contained in, and so on) any one of the entries.
Lastly, depending on how variable the number of members of your IN clause is, don't forget that an IN clause is a simplified OR statement.
A IN (X, Y, Z) ... is logically equivalent to A = X OR A = Y OR A = Z
Hi @MadhusudanR
For further elaboration on the answer from @RonLee_GCS , go to the Filter conditions part of https://community.pega.com/knowledgebase/articles/reporting/84/report-definition-query-tab and review the detail for the Value field. I believe this is still correct for Pega 7.
If the values for your IN clause are dynamic, you will need to prepare a clipboard page with one or more value lists that hold the values for your IN clause. A Data Page is a good way to prepare these values if they are driven by reference data / data from another system of record.
If the values for your IN clause are fixed, the article quotes what I thought was true in that including comma-separated constants in the Value field for an Equals comparison also yields IN clause behavior. See below for what is quoted:
Commas correspond to an OR test: If the Value field contains two or more entries separated by commas, the comparison is true at run time if the value of the Column field equals (or is greater than, or is contained in, and so on) any one of the entries.
Lastly, depending on how variable the number of members of your IN clause is, don't forget that an IN clause is a simplified OR statement.
A IN (X, Y, Z) ... is logically equivalent to A = X OR A = Y OR A = Z
... but given the above options for dynamic or fixed usage, it seems you don't need to revert to this.