Blocking queries are configured in query.xml, and, if the blocking query is used on the MIDM, in midm.xml. In order for the fields defined for range searching in the blocking query to appear on the MIDM, the fields must be configured correctly in midm.xml.
In addition to the standard range searching (described under Basic Query Range Searching), blocking queries support constant and offset range searches, allowing you to specify default upper and lower offset values or to specify upper and lower constant limits. Using offsets adds the specified values to the actual field value to determine the range on which to search. Note that this means the lower offset value should be a negative number and the upper offset value should be a positive number in order to create a valid range. You can also define a combination of a constant upper limit with lower offset value or a constant lower limit with an upper offset value.
When upper and lower offset values are defined, the application searches for values that are greater than or equal to the field value plus the lower offset value (which is typically a negative number) and less than or equal to the field value plus the upper offset value. You do not need to define both an upper and a lower offset value.
For date fields, the method for adding the offsets is different for numeric than for date type fields. For numeric data types, the offset value is added to the actual number. For date data types, the offset value is added to the day portion of the date (for example, if the offsets were -5 and +5 and the date entered is 01/10/2005, then the upper and lower bounds would be 01/05/2005 and 01/15/2005).
Table 5 describes the queries formed for different exact or range offset search scenarios. Table 6 describes the query formed for combination exact and offset range search scenarios.
The following variables are used in these tables:
field_name is the field name as specified in the search page section of midm.xml (the field named field_name is used for exact searching)
value is the value entered into the exact search field
value_from is the value entered into the field_name From field
value_to is the value entered into the field_name To field
lower is the lower offset value
upper is the upper offset value
Field Configuration in midm.xml |
Resulting Fields on MIDM |
Offset Configuration in query.xml |
Fields Populated for Search |
Where Clause |
---|---|---|---|---|
choice attribute set to “exact” |
field_name |
both upper and lower offsets defined |
field_name |
where field_name >= (value + lower) and field_name <= (value + upper) |
choice attribute set to “exact” |
field_name |
only lower offset defined |
field_name |
where field_name >= (value + lower) |
choice attribute set to “exact” |
field_name |
only upper offset defined |
field_name |
where field_name <= (value + upper) |
choice attribute set to “range” |
field_name From field_name To |
upper, lower, or both offsets are defined |
field_name From field_name To |
where field_name >= value_from and field_name <= value_to |
choice attribute set to “range” |
field_name From field_name To |
upper, lower, or both offsets are defined |
field_name From |
where field_name >= value_from |
choice attribute set to “range” |
field_name From field_name To |
upper, lower, or both offsets are defined |
field_name To |
where field_name <= value_to |
In Table 6, the field configuration in midm.xml defines the field twice for searching, once with the choice attribute set to “exact” and once with it set to “range”.
In the following cases, when field_name is populated but not used in the WHERE clause, its value is used for weighting purposes. These cases are marked with an asterisk (*).
Table 6 Combination Offset Range Queries
Offset Configuration in query.xml |
Fields on MIDM |
Fields Populated for Search |
Query Result |
---|---|---|---|
both upper and lower bound offsets are defined |
field_name field_name From field_name To |
field_name |
where field_name >= (value + lower) and field_name <= (value + upper) |
only a lower offset is defined |
field_name field_name From field_name To |
field_name |
where field_name >= (value + lower) |
only an upper offset is defined |
field_name field_name From field_name To |
field_name |
where field_name <= (value + upper) |
upper, lower, or both offsets are defined |
field_name field_name From field_name To |
field_name From field_name To |
where field_name >= value_from and field_name <= value_to |
upper, lower, or both offsets are defined |
field_name field_name From field_name To |
field_name From |
where field_name >= value_from |
upper, lower, or both offsets are defined |
field_name field_name From field_name To |
field_name To |
where field_name <= value_to |
both upper and lower offsets are defined |
field_name field_name From field_name To |
field_name field_name From |
where field_name >= value_from and field_name <= (value + upper) |
only a lower offset is defined |
field_name field_name From field_name To |
field_name field_name From |
where field_name >= (value + lower) |
only an upper offset is defined |
field_name field_name From field_name To |
field_name field_name From |
where field_name <= (value + upper) |
both upper and lower offsets are defined |
field_name field_name From field_name To |
field_name field_name To |
where field_name <= value_to and field_name >= (value + lower) |
only a lower offset is defined |
field_name field_name From field_name To |
field_name field_name To |
where field_name >= (value + lower) |
only an upper offset is defined |
field_name field_name From field_name To |
field_name field_name To |
where field_name <= (value + upper) |
both upper and lower offsets are defined* |
field_name field_name From field_name To |
field_name field_name From field_name To |
where field_name >= value_from and field_name <= value_to |
When you define upper and lower constants for a field, these values are used for the WHERE clause of the query if no data is passed in as search criteria for that field. They are also used when only one of the “from” or “to” fields is populated. You do not need to define both an upper and a lower constant value. If you define only an upper constant value, only a “less than or equals” clause is used in the query; if you define only a lower constant value, only a “greater than or equals” clause is used in the query.
For numeric type fields, the constant must be defined as all digits, with one decimal point allowed. For date type fields, the constant must be in the standard SQL format of yyyy-mm-dd.
Table 7 describes the queries formed for different exact or range constant search scenarios. Table 8 describes the query formed for combination exact and range search scenarios.
The following variables are used in these tables:
field_name is the field name as defined in the search page section of midm.xml (the field named field_name is used for exact searching)
value is the value entered into the exact search field
value_from is the value entered into the field_name From field
value_to is the value entered into the field_name To field
lower is the lower constant value
upper is the upper constant value
Field Configuration in midm.xml |
Resulting Fields on MIDM |
Fields Populated for Search |
Where Clause |
---|---|---|---|
choice attribute set to “exact” |
field_name |
field_name |
where field_name = value |
choice attribute set to “range” |
field_name From field_name To |
field_name From field_name To |
where field_name >= value_from and field_name <= value_to |
choice attribute set to “range” |
field_name From field_name To |
field_name From |
where field_name >= value_from and field_name <= upper |
choice attribute set to “range” |
field_name From field_name To |
field_name To |
where field_name <= value_to and field_name >= lower |
In Table 8, the field configuration in midm.xml defines the field twice for searching, once with the choice attribute set to “exact” and once with it set to “range”.
In the following cases, when field_name is populated but not used in the WHERE clause, its value is used for weighting purposes. These cases are marked with an asterisk (*).
Table 8 Combination Constant Range Queries
Offset Configuration in query.xml |
Fields on MIDM |
Fields Populated for Search |
Query Result |
---|---|---|---|
upper, lower, or both constants are defined |
field_name field_name From field_name To |
field_name |
where field_name = value |
upper, lower, or both constants are defined |
field_name field_name From field_name To |
field_name From field_name To |
where field_name >= value_from and field_name <= value_to |
either upper or both constants are defined |
field_name field_name From field_name To |
field_name From |
where field_name >= value_from and field_name <= upper |
lower constant is defined |
field_name field_name From field_name To |
field_name From |
where field_name >= value_from |
either upper or both constants are defined |
field_name field_name From field_name To |
field_name field_name From |
where field_name >= value_from and field_name <= upper |
lower constant is defined * |
field_name field_name From field_name To |
field_name field_name From |
where field_name >= value_from |
either lower or both constants are defined |
field_name field_name From field_name To |
field_name To |
where field_name <= value_to and field_name >= lower |
upper constant is defined |
field_name field_name From field_name To |
field_name To |
where field_name <= value_to |
either lower or both constants are defined |
field_name field_name From field_name To |
field_name field_name To |
where field_name <= value_to and field_name >= lower |
upper constant is defined * |
field_name field_name From field_name To |
field_name field_name To |
where field_name <= value_to |
upper, lower, or both constants are defined * |
field_name field_name From field_name To |
field_name field_name From field_name To |
where field_name >= value_from and field_name <= value_to |
You can use a combination of offset and constant values to define range searching for a field. Table 9 describes the query formed for combination offset and constant search scenarios.
The following variables are used in these tables:
field_name is the field name as defined in the search page section of midm.xml (the field named field_name is used for exact searching)
value is the value entered into the exact search field
value_from is the value entered into the field_name From field
value_to is the value entered into the field_name To field
lower is the lower constant or offset value
upper is the upper constant or offset value
In Table 9, the field configuration in midm.xml defines the field twice for searching, once with the choice attribute set to “exact” and once with it set to “range”.
In the following cases, when field_name is populated but not used in the WHERE clause, its value is used for weighting purposes. These cases are marked with an asterisk (*).
Table 9 Combination Constant and Offset Range Queries
Offset Configuration in query.xml |
Fields on MIDM |
Fields Populated for Search |
Query Result |
---|---|---|---|
upper offset and lower constant are defined |
field_name field_name From field_name To |
field_name |
where field_name >= lower and field_name <= (value + upper) |
upper offset and lower constant are defined |
field_name field_name From field_name To |
field_name From field_name To |
where field_name >= value_from and field_name <= value_to |
upper offset and lower constant are defined |
field_name field_name From field_name To |
field_name From |
where field_name >= value_from |
upper offset and lower constant are defined |
field_name field_name From field_name To |
field_name To |
where field_name <= value_to and field_name >= lower |
upper offset and lower constant are defined |
field_name field_name From field_name To |
field_name field_name From |
where field_name <= (value + upper) and field_name >= value_from |
upper offset and lower constant are defined |
field_name field_name From field_name To |
field_name field_name To |
where field_name <= value_to and field_name >= lower |
upper offset and lower constant are defined * |
field_name field_name From field_name To |
field_name field_name From field_name To |
where field_name >= value_from and field_name <= value_to |
upper constant and lower offset are defined |
field_name field_name From field_name To |
field_name |
where field_name <= upper and field_name >= (value + lower) |
upper constant and lower offset are defined |
field_name field_name From field_name To |
field_name From field_name To |
where field_name >= value_from and field_name <= value_to |
upper constant and lower offset are defined |
field_name field_name From field_name To |
field_name From |
where field_name >= value_from and field_name <= upper |
upper constant and lower offset are defined |
field_name field_name From field_name To |
field_name To |
where field_name <= value_to |
upper constant and lower offset are defined * |
field_name field_name From field_name To |
field_name field_name From |
where field_name <= upper and field_name >= value_from |
upper constant and lower offset are defined * |
field_name field_name From field_name To |
field_name field_name To |
where field_name <= value_to and field_name >= (value + lower) |
upper constant and lower offset are defined + |
field_name field_name From field_name To |
field_name field_name From field_name To |
where field_name >= value_from and field_name <= value_to |