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:
-
field_name is the field name as specified in the search page section of the Enterprise Data Manager file (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 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 |
- © 2010, Oracle Corporation and/or its affiliates