Home | Sign in | English EN
  • SQL query with a calculation
  • SQL query with calculation by programming
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WindowsLinuxPHPWEBDEV - Browser code
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Stored procedures
To perform a calculation on data coming from a data file, these calculations can be performed by 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 among 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 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 items to add them into 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 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
// 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