6.4 Understanding Property Text Substitution

Data Integrator queries support the use of property text substitution that can be utilized in the Where statement. This allows the substitution of values from a property into a query. For example:


In this example, the properties CP_HIGHEST_LASTUPDATE and CP_HIGHEST_INTERNALID are substituted in the query. The initial substitution uses the empty string for CP_HIGHEST_LASTUPDATE and –1 for CP_HIGHEST_INTERNAL ID and provides a resulting initial query with the clause of LASTUPDATE > '' or INTERNALID > ‑1.

Subsequent queries substitute the actual values of the two computed properties, based on their computed property type, such as LASTUPDATE > '2/12/2006 11:12 AM' or INTERNLID > 827.

The general format for a text substitution is:




The second format uses an initial value of the empty string, as no initial value is specifically given. Do not use spaces in the substitution_specification. Only use spaces in the initial value if spaces are needed. If the computed property value must be single-quoted in the query, then surround the substitution with &sq; (or &singlequote), which inserts the single quotes (').

To use the format {…} literally in the query, surround the braces with single quotes. For example: NAME > '{BOB}' makes no substitution because {BOB} is surrounded by single quotes. The {substitution_specification} format is based on the JAVA MessageFormat class.