PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • SQL query with a selection of file records
  • SQL query with filter by programming
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
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. Select "File .. New .. Query". The wizard for query creation starts.Click New among the quick access buttons.
    • Click "Queries" in the wheel that is displayed. In the window that is displayed, click "Queries".
    • The wizard for query creation starts.
  2. Select the "Select" option and validate.Specify that you want to create a select query ("Select" option).
    Wizard for query creation

    Go to the next wizard step.
  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 description window of query. 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"
    HExecuteQuery(QRY_Filter)
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
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 required version
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment