PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Overview
  • How to proceed?
  • Creating a new select query with the description window of a select query
  • Creating a new Select query with the simplified wizard
  • Notes
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
Overview
A select query (corresponding SQL statement SELECT) is used to view a selection of records in a database. This is the most common type of query.
To perform this selection, the main characteristics of the query are as follows:
  • the type of join between the files used (if several files are used),
  • the selection conditions of the records (customers who live in Seattle, products whose price is greater than 40 Dollars, ...),
  • the grouping of the item values (grouping the products by type, the customers by age, ...),
  • the operations performed on the content of items (total amount of the orders for a customer, average sum spent per customer, ...),
  • the sort of the query result (result sorted by zip code, by price, ...).
For example, you want to display the total amount of orders per customer living in London. Only the customers with a total amount of orders exceeding 3000 Euros will be considered. The result will be sorted by total amount in ascending order. To perform this query, the result will be automatically grouped by customer name and by city.
Select query
The query editor automatically generates the SQL code of this query.
To see the SQL code of a query:
  1. Display the popup menu of the query graph.
  2. Select "SQL code".
You also have the ability to press [F2].
SELECT
CUSTOMER.CustomerName AS CustomerName,
CUSTOMER.CITY AS CITY,
SUM(ORDERS.TotalIOT) AS the_sum_TotalIOT
FROM
CUSTOMER,
ORDERS
WHERE
CUSTOMER.CustomerNum = ORDERS.CustomerNum
AND
(
CUSTOMER.CITY = 'London'
)
GROUP BY
CUSTOMER.CustomerName,
CUSTOMER.CITY
HAVING
SUM(ORDERS.TotalIOT) > 3000
ORDER BY
the_sum_TotalIOT ASC
For more details, see:
How to proceed?
Two methods can be used to create a select query:
  • creation via the description window of a select query.
  • creation via a simplified wizard.

Creating a new select query with the description window of a select query

To create a new select query with the description window of a select query:
  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).
    Go to the next wizard step.
  3. If your project is linked to no analysis, specify the analysis to which the query must be attached. Validate.
  4. The description window of the query is displayed.
    Note: The floating help of this window (green and transparent help) is automatically displayed. The floating help is displayed when the mouse cursor does not move. To remove this help:
    • for the current WINDEV or WEBDEV session: press [Alt]+[F1] simultaneously.
    • permanently: display the configuration options of the query editor ("Tools .. Options .. Options of the editor") and check "Don't display the floating help".permanently:
      • display the configuration options of the query editor: on the "Home" pane, in the "Environment" group, expand "Options" and select "Options of the query editor".
      • check "Don't display the floating help".
  5. Type the name of the query (name of the ".WDR" file corresponding to the query). This name will be used to identify the query in your programs.
  6. Specify the caption of the query by briefly describing the purpose of the query. This caption will be used in the project documentation.
  7. In the left section, select the different items used by the query.
    Note: To simplify the selection of items, these items can be displayed by associated file, by associated query or in alphabetical order.
    See Items taking part in a query for more details.
  8. In the middle section, select the query onto which an action must be performed (selection condition, sort, ...).
  9. In the right section, define the actions that must be performed on the selected item (selection condition, sort, ...).
    For more details, see:
  10. Delete (if necessary) the "duplicate" records from the result of the query ("Delete the duplicates" button).
    In this case, each record found in the result of the query will be unique. For example, if a query is used to display all the customers who placed at least one order, the customers who placed several orders will appear once only in the query result.
  11. Validate the window. The graphic representation of the query is automatically displayed.
  12. Select "File .. Save" to save the query.On the "Home" pane, in the "General" group, expand "Save" and click "Save" to save the query.

Creating a new Select query with the simplified wizard

To create a new Select query with the simplified wizard:
  1. Select "File .. New .. Query". The wizard for query creation starts.Click New among the quick access buttons.  Click "Query" in the wheel that is displayed. In the window that is displayed, click "Query".  The wizard for query creation starts.
  2. Select the "Select" option and check "Use the simplified wizard". Validate.Specify that you want to create a Select query ("Select" option), check "Use the simplified wizard".
    Go to the next wizard step.
  3. If your project is linked to no analysis, specify the analysis to which the query must be attached.
    Go to the next wizard step.
  4. Select the items used by the query.
    Note: To simplify the selection of items, these items can be displayed by associated file, by associated query or in alphabetical order.
    See Items taking part in a query for more details.
    Go to the next wizard step.
  5. Select the order in which the items will be viewed. Go to the next step.
  6. Specify whether selection conditions must be described or not.
    Go to the next step.
    If selection conditions must be defined:
    • select the item for which a selection condition must be added.
    • choose the selection criteria (equal to, greater than, ...) of the condition. This condition can be applied to a given value, a parameter, the records selected by another query, ...
      Repeat this step for all the selection conditions to define.
  7. Specify whether the elements selected by the query must be sorted or not.
    In this case, select the sort item and specify the sort order.
  8. Type the name of the query (name of the ".WDR" file corresponding to the query). This name will be used to identify the query in your programs.
  9. Specify the caption of the query by briefly describing the purpose of the query. This caption will be used in the project documentation.
    Note: The query name is automatically defined from the caption typed.
  10. Validate the description of the query. The graphic representation of the query is automatically displayed in the query editor.
Notes
  • To modify the characteristics of a query, select "Query .. Query description". See Describing a query for more details.To modify the characteristics of a query, on the "Query" pane, in the "Edit" group, click "Description". See Describing a query for more details.
  • To generate the SQL code corresponding to this query, select "Query .. SQL code". If modifications are performed in the description of the query, these modifications will be automatically carried over into the corresponding SQL code.To generate the SQL code corresponding to this query, select "SQL code" from the popup menu of the query. If modifications are performed in the description of the query, these modifications will be automatically carried over into the corresponding SQL code.
  • You also have the ability to create a query from one or more files described in the data model editor: to do so, Drag and Drop the selected files from the "Analysis" pane to the query editor.
Minimum required version
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment