|
|
|
|
|
|
|
|
|
|
- Overview
- Creating a filter
- Characteristics
- Handling the filters in WLanguage
- WLanguage functions
- WLanguage properties
- Browse and search in a selection of filtered records
- Optimizing the filters
- Filters and statistics
- Filters and composite keys
Filtering a table, a view or a query
 Not available with this kind of connection
A filter is used to define programmatically a selection of records corresponding to one or more criteria. This selection of records can be performed on an HFSQL table, an HFSQL view or a query. Records in this selection can be read and iterated through with the read functions ( HFilter, , etc.). This allows you, during a browse, to directly process all the selected records. For example, to send a specific email to all the customers living in state 34, 35 and 36, you must: - Create a filter on the Customer table to select the customers living in states 34, 35 and 36,
- Browse the filtered table and run for each customer a procedure used to send the email.
Filters or queries?- The filters are recommended when the selection condition applies to a single table, a single view or a single query. The filters are used to create complex selection conditions on the records or to easily select the records found in an interval of values.
- The queries are recommended when the selection of records must be performed on several tables.
A filter is created by HFilter. This filter is used to select: - the records found between two bounds: the bounds must correspond to a key item.
- the records corresponding to a selection condition specific on an item.
In both cases, HFilter returns the best item that will be used to browse the filtered records. This item can be a key item of the table or not. If the table is not browsed according to this item, the filter will not be taken into account. Characteristics A single filter per table (view or query) can be enabled at a given time. Handling the filters in WLanguage WLanguage functions The following WLanguage functions are used to manage filters:
| | | HActivateFilter | Enables the filter that was previously created for the specified table (view or query). | | HDeactivateFilter | Temporarily disables the filter on a table (view or query). | | HFilter | Defines and enables a filter on a table, view or query. | | HFilterContains | Defines and enables a "Contains" filter on a table, view or query. | | HFilterIdentical | Defines and enables a filter used to find the exact value of a string item. | | HFilterIncludedBetween | Defines and enables an "Included between" filter on a file, view or query. | | HFilterStartsWith | Defines and enables a "Start with" filter on a file, view or query. |
WLanguage properties The following WLanguage properties are used to manage filters: | | | FilterCondition | Returns the selection condition implemented by HFilter on a table, an HFSQL view or a query. | | FilteredItem | Allos you to get the item on which a filter was implemented by HFilter on a table, an HFSQL view or a query. | | FilterWithBounds | Determines whether bounds have been specified on the filter implemented by HFilter on a table, an HFSQL view or a query. | | MaxValue | Retrieves the upper bound of the current filter (defined by HFilter) on a table, an HFSQL view or a query. | | MinValue | Retrieves the lower bound of the current filter (defined by HFilter) on a table, an HFSQL view or a query. |
Browse and search in a selection of filtered records - To browse a set of filtered records, simply use the HFSQL browse functions.
| | | HReadFirst | Positions on the first record corresponding to the filter (if it exists). HOut returns True if no other record corresponds to the filter. | | HReadLast | Positions on the last record corresponding to the filter (if it exists). HOut returns True if no other record corresponds to the filter. | | HReadNext | Positions on the next record corresponding to the filter (if it exists). HOut returns True if no other record corresponds to the filter. | | HReadPrevious | Positions on the previous record corresponding to the filter (if it exists). HOut returns True if no other record corresponds to the filter. |
- To browse a set of filtered records, simply use the HFSQL search functions.
| | | HReadSeek/HReadSeekFirst | - If the search applies to the search key, seeks and positions on the first record of the filter corresponding to the specified condition (if it exists). HOut returns True if no other record corresponds to the filter and to the search condition.
- If the search is for another field, searches and positions on the first record in the (unfiltered) table corresponding to the specified condition (if any). HOut returns True if no other record corresponds to the filter and to the search condition.
| | HReadSeekLast | - If the search applies to the search key, seeks and positions on the last record of the filter corresponding to the specified condition (if it exists). HFound returns True if no other record corresponds to the filter and to the search condition.
- If the search applies to another item, seeks and positions on the last record of the table (not filtered) corresponding to the specified condition (if it exists). HOut returns True if no other record corresponds to the filter and to the search condition.
|
To optimize the browse of filtered records: - perform statistical calculations of your indexes on a regular basis,
- define composite keys in your tables.
Filters and statistics The filters are based on the HFSQL index statistics. To optimize the browse of filtered records, we recommend that you use recent statistics. These statistics are updated by HStatCalculate. It is therefore advisable to regularly calculate statistics for filtered tables (especially for tables that are frequently modified, e.g. command line). These statistics are automatically recalculated when reorganizing (or reindexing) the tables. Warning: Depending on the size of the table, the calculation of statistics may take some time. In addition, this calculation freezes the table: the table is unusable during this period (HFSQL Classic only). We advise you to perform this calculation when the database is not used (night process for example). Filters and composite keys If the filter condition is applied to several items corresponding to a composite key of your table, the search key automatically chosen for the filter will be this composite key. For example: - A filter is defined on the CUSTOMER table. This filter is applied to the LASTNAME and FIRSTNAME items.
- A composite key LASTNAME+FIRSTNAME is found in CUSTOMER table.
- The composite key will be automatically chosen as search key for the filtered table.
This page is also available for…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|