ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / How to proceed? / Query editor
  • SQL query with a selection of file records
  • SQL query with filter through programming
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
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 in 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 query description window appears.
  4. Give a name and a caption to the query:
    Name and caption of the 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 the items to add them to the list of query items:
    Selection of items
  7. To apply the filter (selection), click "Selection condition" in the right section of the editor.
    Selection condition
  8. Click "New condition...".
  9. In the window that appears:
    • 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 query description window is updated:
    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 F2 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"
    HExecuteQuery(QRY_Filter)
SQL query with filter through programming
The SQL queries can be directly written through programming in the WLanguage code. To do so, you must:
  1. Create a variable of type Data source to represent the query at runtime.
  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 example:
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
FROM PRODUCT
WHERE PRODUCT.Pricebt > %1
]
 
cyPrice is currency
cyPrice = EDT_PRICE // value coming from an edit control
 
sSQLCode = StringBuild(sSQLCode, cyPrice)
 
HExecuteSQLQuery(Src1, hQueryDefault, sSQLCode)
FOR EACH Src1
Trace(Src1.Reference , Src1.ProdCap, Src1.Pricebt)
 
END
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 05/26/2022

Send a report | Local help