PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • SQL query with a sort performed in the query editor
  • SQL query with sort 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 sort (or classify) the records coming from a data file, you have the ability to perform a browse loop on the file by using a key item. In this case, the classifying will be perfomed on the key items only.
The best method to perform this sort (or classifying) consists in using an SQL query.
Let's see how to perform a sort by using:
SQL query with a sort performed in the query editor
The query is used to sort (classify) the records found in a data file according to an item.
In this example, we are going to create an SQL query used to sort the records found in CUSTOMER file according to the customer name.
The different steps for creating this SQL query with sort 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, we are going to select the CustomerName, Company, ZipCode and City items from the CUSTOMER file.
  6. Double-click the names of items to add them into the list of query items:
    Selecting the items
  7. To perform a sort, select the item to sort in the middle section. In our example, this item corresponds to "CustomerName".
  8. In the right section of the editor, click the "Sort" button.
    Sort
  9. Select "Sort on the selected item" from the menu that is displayed. The following window is displayed:
    Sort characteristics
  10. Select the sort order (ascending in our example).
  11. Validate. The description window of query is upated: an arrow with the sort number is displayed.
    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 sort 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

// List of orders sorted by descending Date and Time ...
sSQLCode = [
SELECT ORDERS.OrderNum,
ORDER.ORDDATE,
ORDER.ORDTime
FROM ORDERS
ORDER BY ORDDATE DESC, ORDTime DESC
]

HExecuteSQLQuery(Src1, hQueryDefault, sSQLCode)
FOR EACH Src1
Trace(Src1.OrderNum, Src1.ORDDATE, Src1.ORDTime)

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