- SQL query with a calculation
- SQL query with calculation through programming
How to create an SQL query with a calculation formula?
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:
- 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 data file is the ORDLINE file and the items are OrdLineNum and Reference.
- Double-click the names of the items to add them to the list of query items:
- To perform the calculation, in the left section of the editor, at the bottom, click the "Calculated Item" button
- In the menu that is displayed, select "New calculated item". The window for creating the calculated item is displayed.
- Give a name and a caption to the calculated item.
- 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:
- Validate. The query description window is updated. The calculated item is displayed in the list of items taken into account:
- 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.
SQL query with calculation 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
// 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)
FOR EACH Src1
Trace(Src1.Reference, Src1.ProdCap, Src1.IOT)
This page is also available for…