|
|
|
|
|
|
|
|
|
|
- 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
<Source>.Filter (Function) In french: <Source>.Filtre
 Not available with this kind of connection
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: <Source>.Filter returns an empty string. HError returns the error identifier.
- the filter is created: <Source>.Filter returns the optimized search key that will be used to loop through the data file, view or query.
// Simple filter with condition:
// Find the customers of a city
Â
SearchKey is string
mycity is string
Â
mycity = "MONTPELLIER"
SearchKey = Customer.Filter("CITY='" + mycity + "'")
Customer.ReadFirst(SearchKey)
WHILE Customer.Out() = False
// Process the record
...
Customer.ReadNext(SearchKey)
END
// Cancels the filter
Customer.DeactivateFilter()
// Filter performed with a bounded search key and a condition:
// the search key is fixed
Â
SearchKey is string
// Filters the invoices found between 1/1/2005 and
// 12/31/2005, whose total is greater than 1500 Euros IOT
// with a discount whose type is passed as parameter (text item)
SearchKey = Invoice.Filter(InvoiceDate, "20050101", "20051231", ...
StringBuild("TotalIOT>1500 AND DiscountType='%1'" + , ...
Replace(sDiscountType, "", "\")))
// The Replace function is used to prefix the quotes
// contained in sDiscountType with a backslash
IF SearchKey <> "" THEN
Invoice.ReadFirst(SearchKey)
WHILE Invoice.Out() = False
// Process the record: send a thank you letter
Send_Letter()
Invoice.ReadNext(SearchKey)
END
END
...
// Cancels the filter
Invoice.DeactivateFilter()
Syntax
Filter built with a bounded search key and a condition Hide the details
<Result> = <Source>.Filter(<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.
<Source>: Type corresponding to the specified source 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 Hide the details
<Result> = <Source>.Filter(<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
<Source>: Type corresponding to the specified source 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. Remarks 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":
Customer.Filter(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":
Customer.Filter(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 <Source>.Filter, the iteration must be performed on the item returned by <Source>.Filter. 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: - <Source>.Out 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: takes the lowercase and uppercase characters into account | Valid for string types only | | ]= | Starts with: takes the lowercase and uppercase characters into account | Valid for string types only | | ~] | Contains: ignores the lowercase and uppercase characters | Valid for string types only | | ~~ | Very flexible equality: does not distinguish between lowercase and uppercase characters, ignores the space characters found before and after the string to test, ignores the lowercase accented characters, ignores the space characters and the punctuation characters inside the strings. | Valid for string types only | | ~= | Almost equal to: ignores the space characters on the right, the accented characters, the lowercase and uppercase characters | 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: ‘Item‘‘with‘‘quotes‘ - 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:
FileName.Filter("COMPKEY~]'AAA' AND COMPKEY]='" + ...
FileName.BuildKeyValue(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 <Source>.StatCalculate 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.
Enabling/Disabling a filter The filter is deleted when the data file (query or view) is closed ( <Source>.Close for example). A single filter can exist at a given time on a data file (query or view). If <Source>.Filter is used several times, only the last filter will be taken into account: the previous filters will be deleted.
This page is also available for…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|