Countries in Geographical Groups

This solution demonstrates an integration between DecisionRules and a PostgreSQL database, utilizing it as both a source for data retrieval and a store for persistent results. 

The primary objective of this flow is to allow users to filter geographical groups and calculate the total number of countries within those matching groups. The flow automates the process of querying a country database, aggregating data, and persisting the results into a dedicated output table for external use.

Solution Components

The flow is organized into two primary workflows:

• Countries in Geographical Groups: The core logic that processes user input, queries the database, performs calculations, and manages data persistence.

• Init: A utility workflow (sub-flow) designed to prepare the database tables by clearing previous outputs and initializing operational tables.

The flow orchestrates several specialized nodes to achieve its goal:

• Business Rule (initDB): Executes the Init as a sub-flow to reset output tables and create temporary operational storage.

• Declare Node (declareVariables): Defines and initializes the numberOfMatchingCountries variable to zero to track the total count across groups.

• RDBMS Node (matchingGroups): Queries the lov_country_region_table using the user-provided string to find matching geographical groups.

• RDBMS Node (dbStoreTempTableINSERT) together with Assign Node (sumOfCountries): Iteratively sums the country counts from each matching group into a single total

• RDBMS Node (dbStoreCopyTemp): Efficiently transfers the processed data from a temporary table into the final target table (out_templates).
• Assign Node (assignOutput): Filling out the output parameters.

The flow follows a "best practice" design for database operations to ensure data integrity:

1. Initialization: The flow starts by calling the initDB sub-flow, which deletes existing records in the output table and prepares a temporary table for the current session.

2. Data Retrieval: It accepts a single input parameter, group. If the input is an empty string (""), it returns all groups; otherwise, it filters groups based on a partial match (e.g., "ica" matches Americas and Africa).

3. Processing Loop: For every matching group found:

   • The group name and country count are stored in a temporary table.

   • The flow updates the running total of countries.

4. Finalization: Once the loop completes, the temporary data is copied to the final out_templates table, and the final results (matching group counts and total countries) are propagated to the output parameters.

How to Use This Rule

• Prerequisites: A PostgreSQL database must be accessible to the DecisionRules backend with the alias conn-postgresql-templates.

• Setup: Use the provided DDL and INSERT nodes to create the source table (templates.lov_country_region_table) and populate it with the sample data provided in the template. The output table (templates.out_templates) could be created by the DDL node, too.

• More details could be found in the main flow description - in the Flow Designer, select in the Sidebar the Rule Settings  and scroll down to Description