UNION

Unite a DB-DECLARE or DB-PROCESS call with one or more DB-OBTAIN calls via the UNION keyword, which collects similar columns from two or more tables into one new table. The DB-OBTAIN calls in a union can select rows from one or many tables; the union results in a single table containing the rows selected by each call.
Restriction: This topic applies only when the AppMaster Builder AddPack has been installed, and applies only to Windows platforms.

Target:

SQL

Syntax: for DB-DECLARE

DB-DECLARE cursorname [correlname1.]copylibname-REC
          .
          .
... UNION [ALL]
DB-OBTAIN REC copylibname-REC
          .
          .
... [ORDER 
... column1 [ASC|DESC] [...columnN [ASC|DESC]]]

Syntax: for DB-PROCESS

DB-PROCESS REC [correlname1.]copylibname-REC
          .
          .
... UNION [ALL]
DB-OBTAIN REC copylibname-REC 
          .
          .
... [ORDER
... column1 [ASC|DESC] [...columnN [ASC|DESC]]]

General Rules:

  1. To create a union of tables, code all keywords and parameters for each DB-DECLARE, DB-OBTAIN, and DB-PROCESS call just as you do for a single call.
  2. When coding a UNION, the columns selected in the DB-PROCESS or DB-DECLARE statements in the UNION determine the host variables the FETCH uses. Specify return-fields only in the DB-DECLARE or DB-PROCESS statements of the UNION. See the last example below.
  3. Because a UNION places all rows in a single table, code INTO only in the last DB-OBTAIN call. However, if a DB-OBTAIN call joins tables, do not code INTO, which puts the results from only one table's host-area into the alternate area. A joined table has only part of its contents placed into the alternate area named by INTO.
  4. The column selection list for DB-DECLARE, DB-PROCESS, and each DB-OBTAIN must adhere to standard SQL rules for UNION.
  5. To identify the source call that produces each row in a UNION, include numeric and character literals in your column specifications for each call. Each row then includes a column that identifies the specific DB-DECLARE, DB-OBTAIN, or DB-PROCESS call that retrieved it.
  6. Although you can code a literal for a column in any SQL call, its primary use is for UNIONs.
  7. UNION ALL includes all rows selected from the source tables in the new table. To eliminate duplicate rows from the new table, do not code ALL.
  8. SQL statements adhere to all other SQL requirements for tables created through a union.
  9. UNION combines tables with the same number of columns. The rows of each table must contain column sequences that match in data type and length.
  10. To sort rows in a table, code the ORDER clause, as follows:
    • Code ORDER only once, as the final statement in the last DB-OBTAIN call. The ORDER clause applies to the result table produced by UNION.
    • Specify sort columns by their position in the selection list (for example, 1 or 2), rather than by name.

Parameters:

See the applicable database call for parameter descriptions.

Examples:

Unite DB-DECLARE with one DB-OBTAIN; eliminate duplicate rows; sort the combined table in ascending order by PM_PART_NO, then in descending order by PM_UNIT_BASE_PRICE and PM_UNITS. Note that the column literals STMT1 and STMT2 identify which call retrieves each row. 

DB-DECLARE D2MAST-CURSOR D2TAB-REC
... DISTINCT
... PM_PART_NO PM_UNIT_BASE_PRICE PM_UNITS 'STMT1'
... WHERE PM_PART_SHORT_DESC = :WS-PART-SHORT-DESC
... AND PM_UNIT_BASE_PRICE BETWEEN 50 AND 150
... UNION
DB-OBTAIN REC D2TAB-REC
... PM_PART_NO PM_UNIT_BASE_PRICE PM_UNITS 'STMT2'
... WHERE PM_PART_SHORT_DESC = :WS-PART-SHORT-DESC
... AND PM_UNIT_BASE_PRICE > 300
... AND PM_UNITS > 1000
... ORDER 1,2 DESC, 3 DESC

Unite DB-PROCESS with one DB-OBTAIN call. Sort the combined table in ascending order by PM_PART_NO, then in descending order by PM_UNIT_BASE_PRICE and PM_UNITS. 

DB-PROCESS REC D2TAB-REC
... DB-PROCESS-ID D2UNION-ID
... DISTINCT
... PM_PART_NO PM_UNIT_BASE_PRICE PM_UNITS
... WHERE PM_PART_SHORT_DESC = :WS-PART-SHORT-DESC
... AND PM_UNIT_BASE_PRICE > 50
... AND PM_UNIT_BASE_PRICE < 150
... DB-LOOP-MAX=500
... UNION
DB-OBTAIN REC D2TAB-REC
... PM_PART_NO PM_UNIT_BASE_PRICE PM_UNITS
... WHERE PM_PART_SHORT_DESC = :WS-PART-SHORT-DESC
... AND PM_UNIT_BASE_PRICE > 300
... AND PM_UNITS > 1000
... ORDER 1,2

Because return-fields are not specified for JOB_NAME and PROC_NAME, the host variables of those same names are used. In the case of the literal P, no host variable exists so a return-field must be specified. Specify return-fields only in the DB-DECLARE or DB-PROCESS statements. 

DB-PROCESS
... REC A.HTJCLD-REC
... DB-PROCESS-ID SHARED-ID
... JOB_NAME
... PROC_NAME
... REC B.HTJOBR-REC
... 'P'  (WS-HOLD-ACTION)
... WHERE B.RESOURCE = :WS-RESOURCE AND 
... B.JOB_NAME = :WS-JOB-NAME
... UNION ALL
DB-OBTAIN REC A.HTJCLD-REC
... DISTINCT JOB_NAME
... PROC_NAME
... REC B.HTJSTEP-REC
... 'A'
... WHERE B.JOB_NAME = :WS-JOB-NAME
... ORDER 01 03 02

Retrieve different columns from two tables. Unite DB-PROCESS with one DB-OBTAIN call and sort the combined table in ascending order by UNIT_CODE, STD_MAT_INV_ACCT, and PART_NUMBER.

In the DB-PROCESS call, select five columns from table 1. However, in the DB-OBTAIN statement, select only two columns from table 2. To maintain the same number of columns for each select, enter literals as place holder values that correspond in data type to table 1. To use different columns in the DB-PROCESS call (or in a DB-DECLARE call), be sure that each corresponding column from the DB-PROCESS call (or DB-DECLARE call) and each DB-OBTAIN call match in data type. 

DB-PROCESS REC MANAGE01-REC
... UNIT_CODE STD_MAT_INV_ACCT
... PART_NUMBER      (WS-PART-NUMBER)
... UNIT_OF_MEASURE  (WS-UNIT-OF-MEASURE)
... FREIGHT_CODE     (WS-FREIGHT-CODE)
... WHERE NOT DATA_TYPE = `F'
... UNION
DB-OBTAIN REC-MANAGE02-REC
... UNIT_CODE STD_MAT_INV_ACCT
... 999999           (WS-PART-NUMBER)
... `uc'             (WS-UNIT-OF-MEASURE)
... `'               (WS-FREIGHT-CODE)
... WHERE DATA_TYPE = `F'
... ORDER 1 2 3
Parent topic: Database Calls