Connect CDC SQData

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.

Syntax

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;