WPS: Querying human tasks with multiple custom properties

Suppose you have a human task application running on WebSphere Process Server. Your application requires creating, manipulating and assigning tasks based on certain criteria that are not exposed by HTM interface. Naturally, you add a custom property to your tasks to hold this “side” information. This process is described in this developerWorks article.

As an example, your query might look like this:
resultSet = htm.query(
"DISTINCT TASK.TKIID, TASK.NAME, TASK_CPROP.NAME",
"TASK_CPROP.NAME = 'branch'", …)


But what if you have more then 1 custom property? This Infocenter article gives an example where clause:
"TASK_CPROP1.NAME = 'prop1' AND " TASK_CPROP1.STRING_VALUE = 'v1' AND
TASK_CPROP2.NAME = 'prop2' AND " TASK_CPROP2.STRING_VALUE = 'v2'"

When do you need to add these ordinal numbers (like 1 and 2 in the example above) to the TASK_CPROP table name? Does the number has anything to do with the order in which the properties are defined on the task?

Rule:

1. If you only use a single custom property in a query, reference TASK_CPROP table without ordinal number.
   You should not use TASK_CPROP1, TASK_CPROP2 or 3,4,5…9 in this case.
2. If you use more then one custom property in a query, differentiate between them by adding a number to TASK_CPROP table name. You can pick any numbers you want, but be consistent within each query: use TASK_CPROP1 for one parameter, TASK_CPROP2 for another and so on.
   The number has no relationship with the order in which parameters are defined on the task.

   For example, your task may have 3 properties defined in the following order: propcount, branch, costCenter.
   

   

   Task with 3 properties: propcount, branch, costCenter

   And the following query referencing “branch” as #1 and “costCenter” as #2 will be perfectly valid:
   htm.query("DISTINCT TASK.TKIID, TASK.NAME, TASK_CPROP1.STRING_VALUE, TASK_CPROP2.STRING_VALUE",
"TASK_CPROP1.NAME='branch' and TASK.CPROP2.NAME='costCenter'", ...)


Explanation:
Underlying data for custom properties of human tasks is stored in database table TASK_INST_PROP_T and exposed through a database view TASK_CPROP. Both the table and the view are agnostic of property number. The view is documented here. The view contains only 4 columns: TKIID, NAME, DATA_TYPE and STRING_VALUE. There is only one table/view for custom properties and it has no room to store property’s ordinal number. TASK_CPROP1, TASK_CPROP2 and so on are aliases created in the FROM clause of the query, which is auto-generated behind the scenes based on SELECT and WHERE clauses. So this query:
htm.query("DISTINCT TASK.TKIID, TASK.NAME, TASK_CPROP1.STRING_VALUE, TASK_CPROP2.STRING_VALUE",
"TASK_CPROP1.NAME='branch' and TASK.CPROP2.NAME='costCenter'", ...)

may be translated into SQL resembling this (my reconstruction, not actual SQL from WPS product):

SELECT
DISTINCT TASK.TKIID, TASK.NAME, TASK_CPROP1.STRING_VALUE, TASK_CPROP2.STRING_VALUE
FROM
TASK INNER JOIN TASK_CPROP as TASK_CPROP1 ON TASK.TKIID=TASK_CPROP1.TKIID
INNER JOIN TASK_CPROP as TASK_CPROP2 ON TASK.TKIID=TASK_CPROP2.TKIID
WHERE
TASK_CPROP1.NAME='branch'
and TASK.CPROP2.NAME='costCenter'

Same logic applies, mutatis mutandis, to task templates (TASK_TEMPL_CPROP view) and escalations (ESCALATION_CPROP view)