Help / How to proceed? / Query editor
  • SQL query with a calculation
  • SQL query with calculation through programming
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WindowsLinuxPHPWEBDEV - Browser code
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Stored procedures
To perform a calculation on data coming from a data file, these calculations can be performed through programming in WLanguage by reading the content of data file.
The best method to perform calculations on file data consists in using an SQL query.
Let's see how to perform a calculation by using:
SQL query with a calculation
This query is used to perform a calculation on the records found in a data file.
In this example, we are going to calculate the value of an order line according to a unit price, the quantity ordered and a discount.
The different steps for creating this SQL query with calculation 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 data file is the ORDLINE file and the items are OrdLineNum and Reference.
  6. Double-click the names of the items to add them to the list of query items:
    Selecting the items
  7. To perform the calculation, in the left section of the editor, at the bottom, click the "Calculated Item" button
  8. In the menu that is displayed, select "New calculated item". The window for creating the calculated item is displayed.
  9. Give a name and a caption to the calculated item.
    Name and caption of calculation
  10. Type the formula. The code can be directly typed in the SQL code area. To include an item, click its name in the list on the left:
  11. Validate. The query description window is updated. The calculated item is displayed in the list of items taken into account:
    Query description
  12. Validate the query description window. The query is displayed in the editor:
    Displaying the query in the editor
  13. Save the query (Ctrl + S).
  14. Press F2 to see the SQL code:
    SQL code of query
  15. Run the query test (GO in the quick access buttons).
  16. The query can be run in the program by HExecuteQuery.
SQL query with calculation 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
// Products with the price IOT ...
sSQLCode = [
PRODUCT.Reference AS Reference,
PRODUCT.ProdCap AS ProdCap,
PRODUCT.Pricebt AS Pricebt,
PRODUCT.Pricebt * (1 + PRODUCT.VATRate / 100) AS IOT
HExecuteSQLQuery(Src1, hQueryDefault, sSQLCode)
Trace(Src1.Reference, Src1.ProdCap, Src1.IOT)
Minimum version required
  • Version 9
This page is also available for…
Click [Add] to post a comment

Last update: 05/26/2022

Send a report | Local help