SQL UNION in PEGA

Problem Statement 1:

I have different case types in my application. All the case type classes belongs to the same class group which is default class group for my application. In this case I want to show all the cases created in the system irrespective of the case type. How do we achieve?

In this case all the cases for all the case types will be stored in the same work table. So, querying work table will resolve the issue.

Option-1 : I have not selected the "Report on descendant class instances" check box in this case.

Now the following query will be executed at the database side to fetch all the cases stored in work table.

SELECT
    "PC0"."pyid" AS "pyID",
    "PC0"."pxobjclass" AS "pxObjClass",
    "PC0"."pzinskey" AS "pzInsKey",
    "PC0"."pystatuswork" AS "pyStatusWork",
    "PC0"."pxcreateoperator" AS "pxCreateOperator",
    "PC0"."pxurgencywork" AS "pxUrgencyWork"
FROM
    data.pc_TMR_OMS_Work "PC0"
WHERE
    "PC0"."pxobjclass" LIKE 'TMR-OMS-Work%'        

Option-2 : I select the "Report on descendant class instances" option in the Data Access tab

The following query will be executed at the database side to fetch the cases.There is a variation in the query being executed. Now PEGA will include all the child classes of Class Group class explicitly.

SELECT
    "PC0"."pyid" AS "pyID",
    "PC0"."pxobjclass" AS "pxObjClass",
    "PC0"."pzinskey" AS "pzInsKey",
    "PC0"."pystatuswork" AS "pyStatusWork",
    "PC0"."pxcreateoperator" AS "pxCreateOperator",
    "PC0"."pxurgencywork" AS "pxUrgencyWork"
FROM
    data.pc_TMR_OMS_Work "PC0"
WHERE
    "PC0"."pxobjclass" IN (
        'TMR-OMS-Work',
        'TMR-OMS-Work-TopLevelCase',
        'TMR-OMS-Work-TestOrder',
        'TMR-OMS-Work-TestMultiAssignments',
        'TMR-OMS-Work-ParentCase',
        'TMR-OMS-Work-Order',
        'TMR-OMS-Work-DemandCollection',
        'TMR-OMS-Work-ChildCase'
    )        

Use Option-2 as

• The LIKE 'TMR-OMS-Work%' approach (Option-1) may scan more rows than necessary, especially if you have many sub classes.
• Explicit IN queries (Option-2) are generally faster because the DB optimizer can leverage indexes more effectively.

Problem Statement 2:

Now what if I create a case type which has its own class group instead of default application class group(Create custom worktable and class group and Database Table mapping), but the case type class is inheriting the application class group class. Now I want to list all the cases created in the system including the cases of new case type.

I have created a case type class "TMR-OMS-Work-TestCustomWorkTable" and a dedicated work table.

The second configuration mentioned above will work here as well. But the database query that is being executed now will vary. It will have union of cases from default work table and custom work table.

 SELECT
    "PC0"."pyid" AS "pyID",
    "PC0"."pxobjclass" AS "pxObjClass",
    "PC0"."pzinskey" AS "pzInsKey",
    "PC0"."pystatuswork" AS "pyStatusWork",
    "PC0"."pxcreateoperator" AS "pxCreateOperator",
    "PC0"."pxurgencywork" AS "pxUrgencyWork"
FROM
    data.pc_TMR_OMS_Work "PC0"
WHERE
    "PC0"."pxobjclass" IN (
        'TMR-OMS-Work',
        'TMR-OMS-Work-TopLevelCase',
        'TMR-OMS-Work-TestOrder',
        'TMR-OMS-Work-TestMultiAssignments',
        'TMR-OMS-Work-ParentCase',
        'TMR-OMS-Work-Order',
        'TMR-OMS-Work-DemandCollection',
        'TMR-OMS-Work-ChildCase'
    )
UNION
ALL
SELECT
    "UN1"."pyid" AS "pyID",
    "UN1"."pxobjclass" AS "pxObjClass",
    "UN1"."pzinskey" AS "pzInsKey",
    "UN1"."pystatuswork" AS "pyStatusWork",
    "UN1"."pxcreateoperator" AS "pxCreateOperator",
    "UN1"."pxurgencywork" AS "pxUrgencyWork"
FROM
    data.pc_work_testcustomworktable "UN1"
WHERE
    "UN1"."pxobjclass" ='TMR-OMS-Work-TestCustomWorkTable'        

In this case if you are exposing a property in the default work table and using it in the report you need to expose the same in all the custom work tables that are created in order this to work. If you don't expose the property in any custom work table PEGA will drop that table from the UNION.

Problem Statement 3:

If you wanted to get all the cases of specific case types (for example here I wanted to get all the cases of "TMR-OMS-Work-TopLevelCase" and "TMR-OMS-Work-TestCustomWorkTable")

We will add the filter condition to the above Report Definition. The filter condition is pxObjClass equal to "TMR-OMS-Work-TopLevelCase","TMR-OMS-Work-TestCustomWorkTable"

SELECT 
  "PC0"."pyid" AS "pyID", 
  "PC0"."pxobjclass" AS "pxObjClass", 
  "PC0"."pzinskey" AS "pzInsKey", 
  "PC0"."pystatuswork" AS "pyStatusWork", 
  "PC0"."pxcreateoperator" AS "pxCreateOperator", 
  "PC0"."pxurgencywork" AS "pxUrgencyWork", 
  "PC0"."ordercreatedon" AS "OrderCreatedOn" 
FROM 
  data.pc_work_testcustomworktable "PC0" 
WHERE 
  (
    "PC0"."pxobjclass" = 'TMR-OMS-Work-TestCustomWorkTable'
  ) 
UNION ALL 
SELECT 
  "UN1"."pyid" AS "pyID", 
  "UN1"."pxobjclass" AS "pxObjClass", 
  "UN1"."pzinskey" AS "pzInsKey", 
  "UN1"."pystatuswork" AS "pyStatusWork", 
  "UN1"."pxcreateoperator" AS "pxCreateOperator", 
  "UN1"."pxurgencywork" AS "pxUrgencyWork", 
  "UN1"."ordercreatedon" AS "OrderCreatedOn" 
FROM 
  data.pc_tmr_oms_work "UN1" 
WHERE 
  (
    "UN1"."pxobjclass" = 'TMR-OMS-Work-TopLevelCase'
  )