ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / WLanguage / Managing databases / HFSQL
  • 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
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Filtering a data file, a view or a query
ODBCNot available with this kind of connection
Overview
A filter is used to define through programming a selection of records corresponding to one or more criteria. This selection of records can be performed on a HFSQL data file, a HFSQL view or a query.
Records in this selection can be read and iterated through with the read functions (HReadFirst, HReadNext, 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 data file to select the customers living in state 34, 35 and 36,
  • Browse the filtered data file 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 data file, 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 data files.
Creating a filter
A filter is created by HFilter. This filter is used to select:
  • the records found between two bounds: the bounds must be defined for 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 data file or not. If the data file is not browsed according to this item, the filter will not be taken into account.

Characteristics

A single filter per data file (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:
HActivateFilterEnables the filter that was previously created for the specified data file (view or query).
HDeactivateFilterTemporarily disables the filter on a data file (view or query).
HFilterDefines and enables a filter on a data file, view or query.
HFilterContainsDefines and enables a "Contains" filter on a data file, view or query.
HFilterIdenticalDefines and enables a filter used to find the exact value of a string item.
HFilterIncludedBetweenDefines and enables an "Included between" filter on a file, view or query.
HFilterStartsWithDefines and enables a "Start with" filter on a file, view or query.

WLanguage properties

The following WLanguage properties are used to manage filters:
FilterConditionReturns the selection condition implemented by HFilter on a data file, an HFSQL view or a query.
FilteredItemAllows you to find out the item on which a filter was implemented by HFilter on a data file, an HFSQL view or a query.
FilterWithBoundsUsed to find out whether bounds have been specified on the filter implemented by HFilter on a data file, an HFSQL view or a query.
MaxValueRetrieves the upper bound of the current filter (defined by HFilter) on a data file, an HFSQL view or a query.
MinValueRetrieves the lower bound of the current filter (defined by HFilter) on a data file, an HFSQL view or a query.
Browse and search in a selection of filtered records
  • To browse a set of filtered records, all you have to do is use the HFSQL browse functions.
    HReadFirstPositions on the first record corresponding to the filter (if it exists). HOut returns True if no other record corresponds to the filter.
    HReadLastPositions on the last record corresponding to the filter (if it exists). HOut returns True if no other record corresponds to the filter.
    HReadNextPositions on the next record corresponding to the filter (if it exists). HOut returns True if no other record corresponds to the filter.
    HReadPreviousPositions 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, all you have to do is 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 applies to another item, seeks and positions on the first record of the file (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.
    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 data file (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.
Optimizing the filters
To optimize the browse of filtered records:
  • perform statistical calculations of your indexes on a regular basis,
  • define composite keys in your data files.

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.
Therefore, we recommend that you regularly calculate the statistics of the filtered files (especially for the data files that are often modified, an order line for example). These statistics are automatically recalculated when reorganizing (or re-indexing) the data files.
Caution: Depending on the size of the data file, calculating the statistics can take quite a long time. Furthermore, this calculation locks the data file: the data file cannot be used during this period of time (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 data file, the search key automatically chosen for the filter will be this composite key.
For example:
  • A filter is defined on the CUSTOMER data file. This filter is applied to the LASTNAME and FIRSTNAME items.
  • A composite key LASTNAME+FIRSTNAME is found in CUSTOMER data file.
  • The composite key will be automatically chosen as search key for the filtered data file.
For more details, see Composite keys.
See also
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 06/13/2023

Send a report | Local help