|
|
|
|
|
|
|
|
|
|
- SQL count query on the entire data file
- SQL count query on a grouping programmatically
How to create an SQL query to count?
To count records in a data file, you have the ability to perform a browse loop and to increment an Integer variable. The best method to perform this count consists in using an SQL query. Let's see how to perform a count by using: SQL count query on the entire data file This count query is used to count all the records found in a data file. The images present the count of all records found in CUSTOMER file. The different steps for creating this SQL count query are as follows: - Click
 in the quick access buttons. - In the window that is displayed, click "Queries".
- The query creation wizard is launched.
- Specify that you want to create a select query ("Select" option).
Proceed to the next step of the wizard. - The query description window appears.
- Give a name and a caption to the query:
- Choose an item (identifier for example) in the data file where the count will be performed.
- Add this item to the list of query items.
- In the right section of the editor, click the "Count" button. In the menu that is displayed, select "Count on the selected item":
- Validate. The query description window is updated:
- 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 count query on a grouping programmatically The SQL queries can be directly written through programming in the code. To do so, you must: - Create a Data Source variable to represent the query at runtime
- Create a Character String variable to contain the SQL code and write the SQL code in this variable
- Run the SQL query with HExecuteSQLQuery
- Browse the result with the HReadXXX functions.
Code sample Src1 is Data Source
sSQLCode is string
sSQLCode = [
SELECT PAYMENT.PaymentCap, COUNT(ORDER.OrderID) AS NumberOrders
FROM ORDER, PAYMENT
WHERE ORDER.PaymentID=PAYMENT.PaymentID
GROUP BY PaymentCap
]
HExecuteSQLQuery(Src1, hQueryDefault, sSQLCode)
FOR EACH Src1
Trace(Src1.LibReglement, Src1.NumberOrders)
END
This page is also available for…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|