You can merge data from two or more tables into a single column on a report by using the keyword UNION. First, you create two or more queries to select the data you want to merge, and then you specify the keyword UNION between the queries.
In Figure 64, the first query selects the department name and number from the Q.ORG table, and creates a new column that displays the words WAITING FOR WORK. The second query selects the department name and number from the Q.PROJECT and Q.ORG tables, and creates a new column that displays the words HAS WORK. The database determines the name of the new column, unless you change it using QMF forms.
Select the same number of columns for each query. Corresponding columns must be the same general data type, and must both either allow null values or not allow null values. If you want to order the columns, specify a column number, because the names of the columns you are merging are probably different. If you want to display duplicate rows on the report, specify UNION ALL instead of UNION.
Figure 64. This SQL query merges data from two columns into one.
SELECT DEPTNUMB, DEPTNAME, 'WAITING FOR WORK'
FROM Q.ORG
WHERE DEPTNUMB NOT IN (SELECT DEPT FROM Q.PROJECT)
UNION
SELECT O.DEPTNUMB, O.DEPTNAME, 'HAS WORK'
FROM Q.PROJECT P, Q.ORG O
WHERE P.DEPT = O.DEPTNUMB
ORDER BY 1
|
QMF displays the following report when you run the query, showing the department names and numbers and their status information on the same report.
Figure 65. The report shows the two new columns merged into one.
+--------------------------------------------------------------------------------+ | DEPTNUMB DEPTNAME EXPRESSION 1 | | -------- -------------- ---------------- | | 10 HEAD OFFICE HAS WORK | | 15 NEW ENGLAND HAS WORK | | 20 MID ATLANTIC HAS WORK | | 38 SOUTH ATLANTIC HAS WORK | | 42 GREAT LAKES HAS WORK | | 51 PLAINS HAS WORK | | 66 PACIFIC HAS WORK | | 84 MOUNTAIN WAITING FOR WORK | +--------------------------------------------------------------------------------+
You can specify the order in which you want to merge the columns from multiple tables. Specifying order is important when you use UNION and UNION ALL. Use parentheses to indicate which table's columns you want merged first. The conditions inside the parentheses are checked first, and then the conditions outside the parentheses.
For example, this query produces Report A in Figure 66:
(SELECT ID, NAME, SALARY
FROM Q.STAFF
WHERE SALARY>12000
UNION ALL
SELECT ID, NAME, SALARY
FROM Q.STAFF
WHERE DEPT=38)
UNION
SELECT ID, NAME, SALARY
FROM Q.STAFF
WHERE JOB='SALES'
If you move the parentheses, the same query produces Report B in Figure 66:
SELECT ID, NAME, SALARY
FROM Q.STAFF
WHERE SALARY>12000
UNION ALL
(SELECT ID, NAME, SALARY
FROM Q.STAFF
WHERE DEPT=38
UNION
SELECT ID, NAME, SALARY
FROM Q.STAFF
WHERE JOB='SALES')
Figure 66. The two reports show the differences in merging order.
+--------------------------------------------------------------------------------+ | REPORT A REPORT B | | ID NAME SALARY ID NAME SALARY | | ------ --------- ---------- ------ --------- ---------- | | 10 SANDERS 18357.50 20 PERNAL 18171.25 | | 20 PERNAL 18171.25 30 MARENGHI 17506.75 | | 30 MARENGHI 17506.75 40 O'BRIEN 18006.00 | | 40 O'BRIEN 18006.00 60 QUIGLEY 16808.30 | | 50 HANES 20659.80 70 ROTHMAN 16502.83 | | 60 QUIGLEY 16808.30 90 KOONITZ 18001.75 | | 70 ROTHMAN 16502.83 120 NAUGHTON 12954.75 | | 80 JAMES 13504.60 150 WILLIAMS 19456.50 | | 90 KOONITZ 18001.75 180 ABRAHAMS 12009.75 | | 100 PLOTZ 18352.80 220 SMITH 17654.50 | | 110 NGAN 12508.20 280 WILSON 18674.50 | | 120 NAUGHTON 12954.75 300 DAVIS 15454.50 | | 140 FRAYE 21150.00 310 GRAHAM 21000.00 | | 150 WILLIAMS 19456.50 320 GONZALES 16858.20 | | 160 MOLINARE 22959.20 340 EDWARDS 17844.00 | | 170 KERMISCH 12258.50 10 SANDERS 18357.50 | | 180 ABRAHAMS 12009.75 20 PERNAL 18171.25 | | 190 SNEIDER 14252.75 30 MARENGHI 17506.75 | | 210 LU 20010.00 40 O'BRIEN 18006.00 | | 220 SMITH 17654.50 50 HANES 20659.80 | | 230 LUNDQUIST 13369.80 60 QUIGLEY 16808.30 | | 240 DANIELS 19260.25 70 ROTHMAN 16502.83 | | 250 WHEELER 14460.00 80 JAMES 13504.60 | | 260 JONES 21234.00 90 KOONITZ 18001.75 | | 270 LEA 18555.50 100 PLOTZ 18352.80 | | 280 WILSON 18674.50 110 NGAN 12508.20 | | 290 QUILL 19818.00 120 NAUGHTON 12954.75 | | 300 DAVIS 15454.50 140 FRAYE 21150.00 | | 310 GRAHAM 21000.00 150 WILLIAMS 19456.50 | | 320 GONZALES 16858.20 160 MOLINARE 22959.20 | | 340 EDWARDS 17844.00 170 KERMISCH 12258.50 | | 350 GAFNEY 13030.50 180 ABRAHAMS 12009.75 | +--------------------------------------------------------------------------------+
The first query selects employees whose salaries are greater than $12,000.00 and all employees from Department 38. Then, it eliminates any duplicate entries by selecting only employees who work in sales and are not in Department 38 or making more than $12,000.00 a year.
The second query creates duplicate entries because it first selects employees from Department 38 and employees from outside Department 38 who work in sales. Then, it adds employees whose salaries are more than $12,000.00.