Revision as of 01:19, 21 October 2011

SQL Commands Cookbook

NOTE to authors: Please make sure to specify the version on which a command was executed
[All version numbers < 7.0 refer to the Infinity Process Platform (IPP)]

Retrieving structured data initialized with non-null or null value (plus non-leaf data in the data structure)

(Versions: 5.3.x)

SELECT
  pd.ID, pi.OID, SUBSTR(sd.XPATH,1,20) as "XPATH",
  SUBSTR(sdv.STRING_VALUE,1,20) as "STRING_VALUE",
  sdv.NUMBER_VALUE
FROM
  PROCESS_INSTANCE pi,
  PROCESS_DEFINITION pd,
  DATA_VALUE dv,
  STRUCTURED_DATA sd,
  STRUCTURED_DATA_VALUE sdv
WHERE
  pd.OID = pi.processDefinition and
  pi.OID = dv.processInstance and
  dv.data = sd.data and
  sd.OID = sdv.XPATH and
  pi.OID = sdv.processInstance;

Retrieving structured non-initialized data

(Versions: 5.3.x)

SELECT
  pd.ID, pi.OID, SUBSTR(sd.XPATH,1,20) as "XPATH"
FROM
  PROCESS_INSTANCE pi,
  PROCESS_DEFINITION pd,
  DATA_VALUE dv,
  STRUCTURED_DATA sd
WHERE
  pd.OID = pi.processDefinition and
  pi.OID = dv.processInstance and
  dv.data = sd.data and
  sd.OID not in ( select sdv2.XPATH from structured_data_value sdv2 where sdv2.processinstance = pi.OID );