PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Overview
  • How to proceed?
  • 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
Creating a Union query
Overview
A Union query (corresponding SQL statement: UNION) is used to combine several select queries. Each select query must be based on files of identical structure.
For example, select the customers whose last name starts with the letter 'A' and the prospects who live in Paris. To perform this selection, the two following queries must be combined:
  • selecting the customers whose last name starts with the letter 'A'. This query is based on the "Customer" file.
  • selecting the prospects who live in London. This query is based on the "Prospect" file.
The "Customer" file and the "Prospect" file have the same structure in the analysis.
The records common to the two files ("Customer" and "Prospect") can:
  • appear once only in the result of the query (UNION operator).
  • appear twice in the result of the query (UNION ALL operator).
To combine queries, you must know:
  • the name of the queries to combine.
  • whether the common records must appear once or several times in the query result.
Note: The items displayed in the result of the union query correspond to the items of the first query run. Therefore, in our example, the people who are both customer and prospect, whose name starts with the letter 'A' and who live in London will only appear once in the result of the query.
Union 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].
For example:
SELECT
CUSTOMER.CustomerName AS CustomerName
FROM
CUSTOMER
WHERE
CUSTOMER.CustomerName LIKE 'A%'

UNION

SELECT
PROSPECT.CustomerName AS CustomerName
FROM
PROSPECT
WHERE
PROSPECT.CITY = 'London'
In order for the people who are both customer and prospect, whose last name starts with the letter 'A' and who live in London to appear several times in the query result, the SQL code must be:
SELECT
CUSTOMER.CustomerName AS CustomerName
FROM
CUSTOMER
WHERE
CUSTOMER.CustomerName LIKE 'A%'

UNION ALL

SELECT
PROSPECT.CustomerName AS CustomerName
FROM
PROSPECT
WHERE
PROSPECT.CITY = 'London'
How to proceed?
To create a new union query:
  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. Choose "Union" and validate.Choose "Union" and validate.
  3. If your project is linked to no analysis, specify the analysis to which the query must be attached.
  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/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. Enter the name and caption of the query.
  6. Select the different queries to combine (left box).
  7. Choose to keep or delete the records common to the combined queries ("Keep the duplicates/Delete the duplicates" button).
  8. Sort the result of the query if necessary ("Sort" button).
  9. Validate the description window of 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.
See also
Minimum required version
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment