- SQL query with a selection of file records
- SQL query with filter through programming
How to create an SQL query to filter records?
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:
- Click in the quick access buttons.
- In the window that is displayed, click "Queries".
- The query creation wizard starts.
- Specify that you want to create a select query ("Select" option).
Go to the next step of the wizard.
- The query description window appears.
- Give a name and a caption to the query:
- 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).
- Double-click the names of the items to add them to the list of query items:
- To apply the filter (selection), click "Selection condition" in the right section of the editor.
- Click "New condition...".
- 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.
- Validate. The query description window is updated:
- Validate the query description window. The query is displayed in the editor:
- Save the query (Ctrl + S).
- Press F2 to see the SQL code:
- Run the query test (GO in the quick access buttons).
- The query can be run in the program by HExecuteQuery. The expected parameter(s) must be initialized in the code.
- Example of call from a button click:
QRY_Filter.ParamCity = "Paris"
SQL query with filter through programming
The SQL queries can be directly written through programming in the WLanguage code. To do so, you must:
- Create a variable of type Data source to represent the query at runtime.
- Create a Character String variable to contain the SQL code of the query and write the SQL code in this variable.
- Run the SQL query with HExecuteSQLQuery.
- Browse the result with the HReadXXX functions.
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)
FOR EACH Src1
Trace(Src1.Reference , Src1.ProdCap, Src1.Pricebt)
This page is also available for…