PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Overview
  • UNION/UNION ALL operators
  • UNION
  • UNION ALL
  • Union of queries with parameters: managing the parameters
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
The Union queries are used to combine the Select queries (SELECT statement). Each select query must be based on files of identical structure.
To create a union query, you have the ability to use:
  • the UNION operator.
  • the UNION ALL operator.
Example: Create a query containing the customers whose name starts with the letter 'A' and the prospects who live in London. To create this query, the two following queries must be combined:
  • selecting the customers whose last name starts with the letter 'A'.
  • selecting the prospects who live in London.
The records common to both files can:
  • appear once only in the result of the query (UNION operator).
  • appear twice in the result of the query (UNION ALL operator).
Caution: During the union of queries with parameters, a specific management of parameters is required.
UNION/UNION ALL operators

UNION

The UNION operator is used to combine some Select queries that apply to files of identical structure. The common records will be displayed only once in the query result.
To create an union query with the UNION operator, each select query must use the same number of items.
The caption of result items correspond to the caption of the items found in the first select query.
Use format:
SELECT Item1 [, Item2]
FROM File1 [, File2]
[WHERE ...]
UNION
SELECT Item1 [, Item2]
FROM File1 [, File2]
[WHERE ...]
Example: The following SQL code is used to select the customers whose name starts with the letter 'A' and the prospects who live in London. 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.
SELECT CustomerLastName
FROM CUSTOMER
WHERE CustomerLName LIKE 'A%'
UNION
SELECT ProspectName
FROM PROSPECT
WHERE CustomerCity = 'London'

UNION ALL

The UNION ALL operator is used to combine Select queries that apply to files of identical structure. The common records will be displayed several times in the query result.
To create a union query with the UNION ALL operator, each select query must use the same number of items.
The caption of result items correspond to the caption of the items found in the first select query.
Use format:
SELECT Item1 [, Item2]
FROM File1 [, File2]
[WHERE ...]
UNION ALL
SELECT Item1 [, Item2]
FROM File1 [, File2]
[WHERE ...]
Example: The following SQL code is used to select the customers whose name starts with the letter 'A' and the prospects who live in London. The people who are both customer and prospect, whose name starts with the letter 'A' and who live in London will appear twice in the result of the query.
SELECT CustomerLastName
FROM CUSTOMER
WHERE CustomerLName LIKE 'A%'
UNION ALL
SELECT ProspectName
FROM PROSPECT
WHERE CustomerCity = 'London'
Union of queries with parameters: managing the parameters
When creating a composite query (UNION), each query has its own parameters. For example:
  • myQuery1.sparam1
  • myQuery2.sparam2
  • myUnionQuery is the UNION query of maQuery1 and myQuery2.
At run time, the Union query and the queries used to built the Union query are distinct.
In our example, "myQuery1.sparam1" initializes the parameter for the myQuery1 query but not the sParam1 parameter of the Union query.
To get the right result, you must initialize myUnionQuery.sparam1 to initialize the parameter1 of the Union query.
Minimum required version
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment