This note is for iProcess version 11.

Once in a while I need to get the subcase number of a iProcess subprocedure given the case number of its iProcess main case. If there is only one subcase for this subprocedure then this can be done with the following query:

SELECT cd.casenum AS casenum FROM case_data cd, proc_index pi
WHERE
cd.field_name='SW_MAINCASE'
AND cd.field_value_n = '0000000000000000000000004688.00000000000'
AND cd.proc_id = pi.proc_id AND pi.proc_name='SUBPROCN';

In this query the name of the subprocedure is SUBPROCN and the case number of the main case is 4688. Note that I use column field_value_n instead of column field_value. This is for efficiency, since only the first column is part of an index. Note also the leading zero's in front of the main case number. These leading zero's, including the main case number itself should be exactly 28 characters.

If there are more subcases of procedure SUBPROCN then this query will produce multiple rows. In that case it might be possible to use other fields of this subprocedure to limit your query results.