UNION - connect_cdc_sqdata - Latest

The UNION sub-command chains multiple SELECT sub commands together within a the main Process Command. This allows you to perform ‘multi-step’ processing within an Apply Engine script.

When the UNION sub-command is used, the 2nd SELECT sub-command is executed after the 1st SELECT sub-command has completed.

UNION
SELECT [UNION];

Keyword and Parameter Descriptions

The basic UNION has no parameters.

See the SELECT command for more details on its syntax and usage.

SELECT [UNION] when used as a parameter of SELECT, UNION can be used to chain or concatenate multiple SELECT sub commands together allowing for multi-step processing.

Example:

1. Set a variable V_CRITERIA to the value of an input parameter %(max). Do this only one time rather than every time an input record is processed
2. Select the fields NAME and SSN from source datastore EMPLOYEE_IN and map them to the fields OUTNAME and OUTSSN in target datastore EMPLOYEE_OUT, respectively.
3. Once all of the source records have been processed, map the same fields from all of the records in datastore EMPLOYEE_OUT into target datastore EMPLOYEE_BACKUP.
4. Finally, display a count of the number of input records (Employees) whose salary exceeds the Maximum value specified by the variable V_CRITERIA

   -- Specify the source/target data structures
   DESCRIPTION COBOL /home/sqdata/COBOL/emplin.cob   AS  EMP_DESCR_IN;
   DESCRIPTION COBOL /home/sqdata/COBOL/emplout.cob   AS  EMP_DESCR_OUT;
   -- Specify the source and target datastores
   DATASTORE /users/sqdata/empin.dat
             OF BINARY
             AS EMPLOYEE_IN
             DESCRIBED BY EMPLOYEE_DESCR_IN;
   DATASTORE MQS:///EMPL_OUT_QUEUE’
             OF BINARY
             AS EMPLOYEE_OUT
             DESCRIBED BY EMPLOYEE_DESCR_OUT;
   DATASTORE /home/sqdata/empback.dat
             OF BINARY
             AS EMPLOYEE_BACKUP
             DESCRIBED BY EMPLOYEE_DESCR_OUT;
   -- Specify Variables to be used by the script
   DECLARE V_CRITERIA 10  '0';
   DECLARE V_COUNT    5   '0';
   -- Specify the Processing command
   PROCESS INTO EMPLOYEE_OUT,  EMPLOYEE_BACKUP
   SELECT
   {
      V_CRITERIA = %(max)
   }
   FROM NOTHING
   UNION
   SELECT
   {
      if SALARY > V_CRITERIA
   
     {
          V_COUNT = V_COUNT + 1
     }
       OUTNAME     =  NAME
       OUTSSN      =  SSN
   }
   FROM EMPLOYEE_IN
   UNION
   SELECT *
      EMPLOYEE_BACKUP.OUTNAME    = NAME
      EMPLOYEE_BACKUP.OUTSSN     = SSN
   FROM EMPLOYEE_OUT
   UNION
   SELECT
   {
      OUTMSG (0,STRING('Count of Employees whose salary exceeds $', V_CRITERIA, ' is: ', V_COUNT))
   }
   FROM NOTHING;