PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • SQL query with a calculation
  • SQL query with calculation 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 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. 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 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
    Calculation
  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:
    Calculation
  11. Validate. The description window of query is upated. The calculated item is displayed in the list of items taken into account:
    Query description
  12. Validate the description window of query. The query is displayed in the editor:
    Displaying the query in the editor
  13. Save the query (CTRL S).
  14. Press the F2 key 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 = [
SELECT
PRODUCT.Reference AS Reference,
PRODUCT.ProdCap AS ProdCap,
PRODUCT.Pricebt AS Pricebt,
PRODUCT.Pricebt * (1 + PRODUCT.VATRate / 100) AS IOT
FROM
PRODUCT
]


HExecuteSQLQuery(Src1, hQueryDefault, sSQLCode)
FOR EACH Src1
Trace(Src1.Reference, Src1.ProdCap, Src1.IOT)

END
Minimum required version
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment