- SQL query with a selection of file records
- SQL query with filter through programming
How to create an SQL query to filter records?
 Not available
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"
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: - 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.
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
This page is also available for…
|
|
|
|