|
|
|
|
|
|
|
|
|
|
- Lower bound and upper bound on a Text or Composite Key key item
- Filter and filtered iteration
- Selection condition
- Enabling/Disabling a filter
- Filter on a composite key
- Non-HFSQL data files
HFilter (Function)
 No disponible con este tipo de conexión
Defines and enables a filter on a data file, view or query. After its execution, the most common cases are as follows: - the filter cannot be created: the function HFilter FUNCTION function returns an empty string. HError returns the error identifier.
- the filter is created: the HFilter function returns the optimal route key to be used to route the data file, view or query.
SearchKey is string
mycity is string
mycity = "MONTPELLIER"
SearchKey = HFilter(Customer, "CITY='" + mycity + "'")
HReadFirst(Customer, SearchKey)
WHILE HOut() = False
...
HReadNext(Customer, SearchKey)
END
HDeactivateFilter(Customer)
SearchKey is string
SearchKey = HFilter(Invoice, InvoiceDate, "20050101", "20051231", ...
StringBuild("TotalIOT>1500 AND DiscountType='%1'" + , ...
Replace(sDiscountType, "", "\")))
IF SearchKey <> "" THEN
HReadFirst(Invoice, SearchKey)
WHILE HOut() = False
Send_Letter()
HReadNext(Invoice, SearchKey)
END
END
...
HDeactivateFilter(Invoice)
Sintaxis
Filter built with a bounded search key and a condition Ocultar los detalles
<Result> = HFilter(<Data file> , <Search key> , <Lower bound> [, <Upper bound> [, <Selection condition>]])
<Result>: Character string Browse item. Corresponds to: - the search key of data file if the filter is enabled.
- an empty string ("") if the filter cannot be implemented.
<Data file>: Character string Name of data file, HFSQL view or query used. <Search key>: Character string Name of key item used to loop through the data file, view or query. This item must be a search key of data file, view or query. The lower and upper bounds will be applied to this item. <Lower bound>: Type of search item Minimum value (inclusive) of search item (if the search key is defined with an iteration in ascending order in the analysis). The records corresponding to this minimum value will be included in the filter. For a descending iteration (i.e., if the search key is defined with an iteration in descending order in the analysis), it is the maximum value of the browse item.
The type of this parameter must correspond to the type of search item. For example, if the search item is a string, the lower bound must also be a string. <Upper bound>: Type of search item, optional parameter Maximum value (inclusive) for the browse item (ascending iteration). The records corresponding to this maximum value will be included in the filter. The type of this parameter must correspond to the type of search item. For example, if the search item is a string, the upper bound must also be a string.
For a descending iteration, it is the minimum value of the search item.
If this parameter is not specified, the upper bound corresponds to the value of <Lower bound>. <Selection condition>: Optional character string Selection condition to create the filter (see Notes). This selection condition can be an Ansi or Unicode character string.
Filter built with a condition Ocultar los detalles
<Result> = HFilter(<Data file> , <Selection condition>)
<Result>: Character string Browse item. Corresponds to: - the search key of data file if the filter is enabled
- an empty string if the filter cannot be implemented
<Data file>: Character string Name of data file, HFSQL view or query used. <Selection condition>: Character string Selection condition used to create the filter (see the Notes). This selection condition can be an Ansi or Unicode character string. Observaciones Lower bound and upper bound on a Text or Composite Key key item If the lower bound and the upper bound are the same: - to implement an exact-match filter on a value, all you have to do is specify this value in the "Lower Bound" parameter.
For example, to select the customers whose name corresponds to "Smith":
HFilter(Customer, Name, "Smith")
The customer named "Smither" will not be selected. - to implement a generic filter on a value, you must:
- fill the lower bound with the hMinVal constant to give it the minimum value.
- fill the upper bound with the hMaxVal constant to give it the maximum value.
For example, to select the customers whose last name starts with "Smith":
HFilter(Customer, Name, "Smith" + hMinVal, "Smith" + hMaxVal)
The customers named "Smith" and "Smither" are selected.
Remarks: - The hMinVal constant is equivalent to Charact(0).
- The hMaxVal constant is equivalent to Charact(255).
Filter and filtered iteration After the call to HFilter, the iteration must be performed on the item returned by HFilter. The filter will be ignored if another item is used to loop through the data file. When a filter is defined and enabled on a data file (view or query), all records read correspond to the filter. If no other record corresponds to the filter during the iteration: - HOut returns True.
- the current record corresponds to the last record read with the filter.
For example: | | | On a filtered data file, after the function: | the current record is: |
|---|
| HReadFirst | the first file record corresponding to the filter | | HReadLast | the last file record corresponding to the filter | | HReadNext (or HForward) | the next record (or the next nth record) corresponding to the filter | | HReadPrevious (or HBackward) | the previous record (or the previous nth record) corresponding to the filter |
Selection condition The general syntax of a condition has the following format: "CustomerName>'Smith' and (ZipCode=34 or ZipCode=32)" The supported operators depend on the type of items used in the condition: | | | | > | Greater than | Valid for all types | | >= | Greater than or equal to | Valid for all types | | < | Less than | Valid for all types | | <> | Different | Valid for all types | | <= | Less than or equal to | Valid for all types | | = | Strictly equal to | Valid for all types | | ] | Contains: case-sensitive characters | Valid for string types only | | ]= | Begins with: takes into account character case | Valid for string types only | | ~] | Contains: does not take character case into account | Valid for string types only | | ~~ | Very flexible equality: does not differentiate between uppercase and lowercase characters, ignores spaces before and after the string to be tested, ignores lowercase accented characters, ignores spaces and punctuation within strings. | Valid for string types only | | ~= | Approximately equal: ignores right-hand spaces, accents and case. | Valid for string types only |
Notes about the selection condition: - Constant strings must be enclosed in single quotes.
- If the item name contains single quotes, they must be doubled.
For example: Rubricwithquotes - The comparisons between strings are performed according to the parameters specified for the indexes.
- Binary memos and composite keys cannot be part of a selection condition.
For the composite keys, we recommend that you use the first syntax. The following syntax cannot be used:
HFilter(FILENAME, "COMPKEY~]'AAA' AND COMPKEY]='" + HBuildKeyValue(FILENAME, COMPKEY, 0) + "'")
- If a character string (constant or variable) contains a single quote, the quote must be preceded by a backslash ( \ ).
- To optimize the iteration, use HStatCalculate on the data file before creating the filter. The HFSQL engine analyzes the selection condition and uses these statistics to determine which items will be used to optimize the iteration performed on the data file.
- This selection condition can be an Ansi or Unicode character string.
- It is not possible to use:
- more than 6000 operands in the condition.
- more than 420 logical AND/OR/XOR operands in the condition.
Otherwise, an error 70209 appears: "Error during filter initialization. The maximum number of operands has been reached".
Enabling/Disabling a filter The filter is deleted when the data file (query or view) is closed ( HClose for example). A single filter can exist at a given time on a data file (query or view). If the HFilter function is used several times, only the last filter will be taken into account: the previous filter(s) will be deleted.
Esta página también está disponible para…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|