Understanding Sun Master Index Configuration Options (Repository)

Range Search Processing (Repository)

Both basic and blocking queries can be configured to perform both exact searches and range searches. The following topics describe how different configurations of exact and range searches are processed.

Basic Query Range Searching

Range searching for basic queries is configured in the search page section of the Enterprise Data Manager file by tagging the field with a “choice” attribute. When you specify a field for range searching, two corresponding fields appear on the EDM with “From” and “To” appended to the name (for example, a field named “Date of Birth” would display two fields: “Date of Birth From” and Date of Birth To”). You can also define a field for both exact and range searching by defining the field twice for the search page, once with the choice attribute set to “exact” and once with it set to “range”. In this case, three fields appear on the EDM: one with the given field name, one with “From” appended to the name, and one with “To” appended to the name.

Table 3 describes the queries formed for different exact or range search scenarios. Table 4 describes the queries formed for combination exact and range search scenarios.

The following variables are used in these tables:

Table 3 Standard Range Queries

Field Configuration in the Enterprise Data Manager file 

Resulting Fields on EDM 

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

choice attribute set to “range”

field_name From

field_name To

field_name To

where field_name <= value_to

In the following table, 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 4 Combination Exact and Range Queries

Field Configuration in the Enterprise Data Manager file 

Resulting Fields on EDM 

Fields Populated for Search 

Where Clause 

field defined once with choice attribute set to “exact” and once with it set to “range”

field_name

field_name From

field_name To

field_name

where field_name = value

field defined once with choice attribute set to “exact” and once with it set to “range”

field_name

field_name From

field_name To

field_name From

field_name To

where field_name >= value_from and field_name <= value_to

field defined once with choice attribute set to “exact” and once with it set to “range”

field_name

field_name From

field_name To

field_name From

where field_name >= value_from

field defined once with choice attribute set to “exact” and once with it set to “range”

field_name

field_name From

field_name To

field_name To

where field_name <= value_to

field defined once with choice attribute set to “exact” and once with it set to “range” *

field_name

field_name From

field_name To

field_name

field_name From

where field_name >= value_from

field defined once with choice attribute set to “exact” and once with it set to “range” *

field_name

field_name From

field_name To

field_name

field_name To

where field_name <= value_to

field defined once with choice attribute set to “exact” and once with it set to “range” *

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

Blocking Query Range Searching

Blocking queries are configured in the Candidate Select file, and, if the blocking query is used on the EDM, in the Enterprise Data Manager file. In order for the fields defined for range searching in the blocking query to appear on the EDM, the fields must be configured correctly in the Enterprise Data Manager file.

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.

Blocking Query Offset Values

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.


Note –

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:

Table 5 Standard Offset Range Queries

Field Configuration in the Enterprise Data Manager file 

Resulting Fields on EDM 

Offset Configuration in the Candidate Select file 

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 the Enterprise Data Manager file 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 the Candidate Select file 

Fields on EDM 

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

Blocking Query Constants

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.


Note –

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:

Table 7 Standard Constant Range Queries

Field Configuration in the Enterprise Data Manager file 

Resulting Fields on EDM 

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 the Enterprise Data Manager file 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 the Candidate Select file 

Fields on EDM 

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

Blocking Query Offset and Constant Combinations

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:

In Table 9, the field configuration in the Enterprise Data Manager file 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 the Candidate Select file 

Fields on EDM 

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