ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / WLanguage / Managing databases / HFSQL / HFSQL functions
  • 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
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
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: HFilter returns an empty string. HError returns the error identifier.
  • the filter is created: HFilter returns the optimized search key that will be used to loop through the data file, view or query.
Example
// Simple filter with condition: 
// Find the customers of a city

SearchKey is string
mycity is string

mycity = "MONTPELLIER"
SearchKey = HFilter(Customer, "CITY='" + mycity + "'")
HReadFirst(Customer, SearchKey)
WHILE HOut() = False
// Process the record 
...
HReadNext(Customer, SearchKey)
END
// Cancels the filter
HDeactivateFilter(Customer)
// 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 = HFilter(Invoice, 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
HReadFirst(Invoice, SearchKey)
WHILE HOut() = False
// Process the record: send a thank you letter
Send_Letter()
HReadNext(Invoice, SearchKey)
END
END
...
// Cancels the filter
HDeactivateFilter(Invoice)
Syntax

Filter built with a bounded search key and a condition Hide the details

<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 Hide the details

<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.
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":
    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:
HReadFirstthe first file record corresponding to the filter
HReadLastthe 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 thanValid for all types
>=Greater than or equal toValid for all types
<Less thanValid for all types
<>DifferentValid for all types
<=Less than or equal toValid for all types
 =Strictly equal toValid for all types
]Contains: takes the lowercase and uppercase characters into accountValid for string types only
]=Starts with: takes the lowercase and uppercase characters into accountValid for string types only
~]Contains: ignores the lowercase and uppercase charactersValid 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 charactersValid for string types only
AndroidAndroid Widget The ~~ and ~= operators must not be used. These operators are not allowed.
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:
    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.

Enabling/Disabling a filter

HDeactivateFilter is used to disable a filter.
HActivateFilter is used to re-enable 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 HFilter is used several times, only the last filter will be taken into account: the previous filters will be deleted.
Universal Windows 10 AppiPhone/iPadHFSQL ClassicHFSQL Client/ServerStored procedures

Filter on a composite key

Several methods can be used to implement a filter on a composite key:
  • Method 1: Using a list of values in HFilter.
  • Method 2: Using the FOR EACH statement.
  • Method 3: Using the HBuildKeyValue function.
For more details on how to use composite keys on filters, see Composite keys and filters.
Usage example of method 1: Using a list of values
The value of the composite key on which the filter will be set can be directly built in HFilter using the following syntax:
HFilter(<File name>, <Name of composite key>,
[<List of values for the lower bound of composite key>],
[<List of values for the upper bound of composite Key>])
Example: Find all the records in the CUSTOMER file whose last name is found between "AA" and "Barnaby" and whose first name is found between "Philomene" and "Tartuffe".
HFilter(Customer, LastNameFirstName, ["AA", "Philomene"], ["Barnaby", "Tartuffe"])
In this example, "AA","Zorro" is returned by the filter while "Philomene","Zorro" is not.
Example: Find all the records in the Tasks file whose tasks are included between 15/03/2021 00:00 and 25/03/2021 00:00.
HFilter(Tasks, TaskStarDateTaskStartTime, [20210315,0000], [20210325,0000])
AndroidAndroid Widget Stored proceduresNative Connectors (Native Accesses)

Non-HFSQL data files

Component: wd290hf.dll
Minimum version required
  • Version 9
This page is also available for…
Comments
Exemplo de montagem de filtro em variável para aplicar no HFilter
HourGlass(True)

x is int = 30

sMontaFiltro is string = "T002_Produtos_Local.T002_Produtos_ID IN ("

LOOP(5)

x++

sMontaFiltro += (Length(sMontaFiltro)>0 and x<35?", " else "") + X

END

sMontaFiltro += ")"

HFilter(T002_Produtos_Local, sMontaFiltro)

TableDisplay(TABLE_T002_Produtos_PCSOFT,taInit)

gnTotal = TABLE_T002_Produtos_PCSOFT.Count(toTotal)

HourGlass(False)

by Mello
Boller
25 Jan. 2023
Exemplo usando a clausula IN
HFilter(T002_Produtos_Local, "T002_Produtos_Local.T002_Produtos_ID IN(36, 37, 38, 32, 27)")

by Mello
Boller
25 Jan. 2023
Exemplo com Between de intervalo de valores dinamicos atraves de Stringbuild
2 campos na tela de entrada
Edt_gn_glo_cod_emp = 2
Edt_gn_glo_cod_obra = 120

HourGlass(True)

Hfilter(pla_cus_obra, StringBuild("id_cllientes = %1 and id_obras = %2 and id_insumos > 0", gn_glo_cod_emp, gn_glo_cod_obra))

TableDisplay(pla_cus_obra,taInit)

gnTotal = pla_cus_obra.Count(toTotal)

HourGlass(False)

by Mello
Boller
25 Jan. 2023
Exemplo montando um filtro dinamicamente com LOOP ou FOR ou While...
HourGlass(True)

x is int = 30

sMontaFiltro is string

LOOP(5)

x++

sMontaFiltro += "T002_Produtos_Local.T002_Produtos_ID="+x+" OR "

END

//remove o ultimo OR = 4 caracteres

sMontaFiltro = Middle(sMontaFiltro,1,Length(sMontaFiltro)-4)

HFilter(T002_Produtos_Local, (sMontaFiltro))

TableDisplay(TABLE_T002_Produtos_PCSOFT,taInit)

gnTotal = TABLE_T002_Produtos_PCSOFT.Count(toTotal)

HourGlass(False)
Boller
25 Jan. 2023
Exemplos com valores Fixos
HourGlass(True)

HFilter(T002_Produtos_Local,("T002_Produtos_Local.T002_Produtos_ID=36 OR T002_Produtos_Local.T002_Produtos_ID = 37 OR T002_Produtos_Local.T002_Produtos_ID = 38 OR T002_Produtos_Local.T002_Produtos_ID = 32 OR T002_Produtos_Local.T002_Produtos_ID = 27"))

TableDisplay(TABLE_T002_Produtos_PCSOFT,taInit)

gnTotal = TABLE_T002_Produtos_PCSOFT.Count(toTotal)

HourGlass(false)
Boller
25 Jan. 2023

Last update: 09/07/2023

Send a report | Local help