Home | Sign in | English UK
  • SQL query with a selection of file records
  • SQL query with filter by programming
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WindowsLinuxPHPWEBDEV - Browser code
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Stored procedures
To filter (or select) records coming from a data file, you have the ability to perform a browse loop and to use HFilter.
The best method to perform this filter (or selection) consists in using an SQL query.
Let's see how to perform a filter by using:
SQL query with a selection of file records
This query is used to select the file records corresponding to a selection condition.
In this example, we want to select the records whose city is equal to a specific parameter from CUSTOMER file.
The steps for creating this SQL query with filter are as follows:
  1. Click New among the quick access buttons.
    • In the window that is displayed, click "Queries".
    • The query creation wizard starts.
  2. Specify that you want to create a select query ("Select" option).
    Query creation wizard

    Go to the next step of the wizard.
  3. The description window of query is displayed.
  4. Give a name and a caption to the query:
    Name and caption of query
  5. In the left section of the description window, choose the file items that will be used (in our example, the CUSTOMER file and the CustomerName, Company and City items).
  6. Double-click the names of items to add them into the list of query items:
    Selecting the items
  7. To perform the filter (selection), in the right section of the editor, click the "Selection condition" button.
    Selection condition
  8. Click "New condition...".
  9. In the window that is displayed:
    • Select the operation ("is equal" in our example).
    • Select "Parameter" to be able to pass the value typed by the user to the query.
    • Give a name to the parameter.
      Characteristics of selection condition
  10. Validate. The description window of query is upated:
    Query description
  11. Validate the query description window. The query is displayed in the editor:
    Displaying the query in the editor
  12. Save the query (Ctrl + S).
  13. Press the F2 key to see the SQL code:
    SQL code of query
  14. Run the query test (GO in the quick access buttons).
  15. The query can be run in the program by HExecuteQuery. The expected parameter(s) must be initialized in the code.
  16. Example of call from a button click:
    QRY_Filter.ParamCity = "Paris"
SQL query with filter by programming
The SQL queries can be directly written by programming in the WLanguage code. To do so, you must:
  1. Create a Data source variable to represent the query at run time.
  2. Create a Character String variable to contain the SQL code of the query and write the SQL code in this variable.
  3. Run the SQL query with HExecuteSQLQuery.
  4. Browse the result with the HReadXXX functions.
Code sample:
Src1 is Data Source
sSQLCode is string
// Select the products whose price is greater than ...
sSQLCode = [
SELECT PRODUCT.Reference AS Reference,
PRODUCT.ProdCap AS ProdCap,
PRODUCT.Pricebt AS Pricebt
WHERE PRODUCT.Pricebt > %1
cyPrice is currency
cyPrice = EDT_PRICE // value coming from an edit control
sSQLCode = StringBuild(sSQLCode, cyPrice)
HExecuteSQLQuery(Src1, hQueryDefault, sSQLCode)
Trace(Src1.Reference , Src1.ProdCap, Src1.Pricebt)
Minimum version required
  • Version 9
This page is also available for…
Click [Add] to post a comment