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 |