FORMAT

Format 1:  For TYPE(RDB):

PROCESS

      [FILE] (rdb-retrieval-list)

      [DISTINCT]

      [GROUP [BY] (field-reference [,field-reference]...)

            [HAVING (condition)]]

      [CURSOR(cursor-name)]

      [UPDATE [(rdb-update-list)]]

      [WHERE(condition)]

      [ORDER [BY] (sort-key [,sort-key]...)]

      [COUNTER (field-reference)]

      ;

      process-block

      ENDPROCESS [EXIT(condition)]

      ;

Format 3:  For TYPE(VSAM), TYPE(F), TYPE(FB), TYPE(V), TYPE(VB) and TYPE(U):

PROCESS

      [FILE] (record-name)

      [OPEN | NOOPEN]

      [CLOSE | NOCLOSE]

      [START(NEXT)]

      [UPDATE]

      [WHERE(condition)]

      [ORDER [BY] (sort-key [,sort-key]...)]

      [COUNTER (field-reference)]

      ;

      process-block

      ENDPROCESS [EXIT(condition)]

      ;

Format 4:  To turn off the UPDATE function

PROCESS

      [FILE] [(record-name)] UPDATE OFF

sort-key::=

field-reference

[ASCENDING | DESCENDING | BUSHU | SOKAKU | ONYOMI | KUNYOMI]

rdb-retrieval-list::=

record-name [(field-reference [,field-reference]...)]

      [, record-name [(field-reference [,field-reference]...)]]

rdb-update-list::=

field-reference [=NULL] [,field-reference [=NULL]...]

FUNCTION

PROCESS retrieves, and may update, records which are defined either as TYPE(RDB), TYPE(NDB), TYPE(VSAM), TYPE(F), TYPE(FB), TYPE(V), TYPE(VB) or TYPE(U). PROCESS cannot create records on a file or database.

You may not use a PROCESS statement in a break-block.

The PROCESS statement functions as follows:

Prepare the file to be processed

For each record that satisfies the WHERE(condition):

      Retrieve the qualifying record

      Execute the process-block

      Update the record (if UPDATE specified)

      If the EXIT(condition) is true, leave the

            process-loop

Close the file being processed

The process-block is executed as many times as there are qualifying records;  if there are no qualifying records, the process-block is not executed.

File Control

Preparing and closing files and databases is normally handled automatically by Jazz, but you can over-ride those processes if you need to;  refer to the:

•     OPEN option for VSAM, F, FB, V, VB and U files.

•     AQLSTART option for AIM/RDB in the PROGRAM statement.

•     SQLSTART option for RDB II in the PROGRAM statement.

File Control, in Chapter 4, describes all of these options, and how they may be affected by records described as EXTERNAL.

OPTIONS

CLOSE

You can use this option to alter the normal pattern of file handling;  File Control in Chapter 4 of this manual describes file handling and EXTERNAL files in detail.

COUNTER (field-reference)

You may name a field that will be set to zero before the start of the PROCESS, and incremented each time the PROCESS retrieves a qualifying record. The field must be numeric, and must not be within the record named by the PROCESS statement. For example:

      PROCESS ORDERITM

            WHERE (ORDERITM.UNITS_ORDERD > 1000)

            COUNTER(SYSTEM.N1);

CURSOR(cursor-name)

If you want to refer explicitly to the SQL cursor-name, you should use this option to name it. If you do not name the cursor, Jazz will generate a name but it may change in later translations.

DISTINCT

If you specify DISTINCT, then when duplicate records occur, only the first of them is presented to the process-block. No indication given that duplicate records have been detected or which records have or have not been processed.

A record is considered to be a duplicate when all of the fields implied or specified in the rdb-select-list have the same value as those same fields in another record.

For example, if you are processing a table (CUSTOMER) with the fields:

      CUSTOMER_ID CUST_NAME   CUST_PHONE

      12345 MANA SYSTEMS LIMITED    4190046

      23456 JCN DEVELOPMENTS  607419

      23456 JCN DEVELOPMENTS  607419

      23456 JCN DEVELOPMENTS  5553869

with:

      DEFINE CUSTOMER

        TYPE(RDB DB(ADTS01) KEY(CUSTOMER.CUSTOMER_ID))

        DATA(     CUSTOMER_ID INTEGER NOTNULL,

                  CUST_NAME         CHAR(30),

                  CUST_PHONE  INTEGER);

      PROCESS CUSTOMER DISTINCT;

        process-block

      ENDPROCESS;

The process-block would be presented with:

      CUSTOMER_ID CUST_NAME   CUST_PHONE

      12345 MANA SYSTEMS LIMITED    4190046

      23456 JCN DEVELOPMENTS  607419

      23456 JCN DEVELOPMENTS  5553869

There is no indication to the process-block that either:

•     there were duplicate records in the table, or

•     the second or third record of the table that was presented to the process-block.

ENDPROCESS [EXIT(condition)]

ENDPROCESS marks the end of the process-block.

If you use the EXIT option, you can specify a condition that, if satisfied, causes the process-block to be terminated. The EXIT condition is tested at the end of the process-block. For example, in:

      FLAG  = '1';

      PROCESS CUSTOMER;

        process-block

      ENDPROCESS EXIT (FLAG = '1');

the EXIT condition is tested at the ENDPROCESS; so the process-block will always be executed at least once.

Conditions are described in Chapter 4 of this manual.

File

Specifies the name of the file to be read. The FILE option may be omitted if the record name or rdb-retrieval-list (format 1) immediately follows the PROCESS keyword.

FIND

Valid for NDB only. FIND updates the PGCS but does not retrieve data from the database. It is not valid to reference data from the subschema when it is accessed using the FIND option.

GROUP [BY] (field-reference [,field-reference]...)

Specifies that the records that have the same value for the named fields are to be processed together. For example, in a ORDERS record,

      PROCESS ORDERS GROUP ORDERS.ORDER_NO;

ensures that all records for ORDER_NO 1 are processed together, as are records for ORDER_NO 2, and so on. It does not specify that ORDER_NO 1 is processed before ORDER_NO 2;  that is controlled by ORDER BY.

When GROUP BY is used:

•     An rdb-retrieval-list must be specified and must contain the same fields that are specified in the GROUP BY option (otherwise you risk generating an invalid rdb-retrieval-list that contains fields not specified in the GROUP BY option).

•     A field may be referenced only if it is specified in the GROUP BY option.

HAVING (condition)

If you specify HAVING, you must also specify GROUP. HAVING lets you specify conditions which must be satisfied for the whole GROUP before you can process any of the records in the GROUP. For example:

      PROCESS ORDERITM

        GROUP ORDERITM.ORDER_NO

        HAVING MAX(ORDERITM.UNITS_ORDERD) > 1000;

will process ORDERITM whether or not their UNITS_ORDERD is greater than 1000, but will not process any records from ORDERITM where none of the records has sufficient UNITS_ORDERD.

The HAVING condition may use the RDB built-in functions;  MAX, MIN, SUM, AVG and COUNT.

NOCLOSE

You can use this option to alter the normal pattern of file handling;  File Control in Chapter 4 of this manual describes file handling and EXTERNAL files in detail.

NOOPEN

You can use this option to alter the normal pattern of file handling;  File Control in Chapter 4 of this manual describes file handling and EXTERNAL files in detail.

OPEN

You can use this option to alter the normal pattern of file handling;  File Control in Chapter 4 of this manual describes file handling and EXTERNAL files in detail.

ORDER [BY] (sort-key [,sort-key]...)

Note that this is not valid:

•     For records defined as TYPE(NDB).

•     For external files.

•     If the NOOPEN option is used.

•     If the NOCLOSE option is used.

You can specify the sequence in which records are presented to the process-block. You can name several fields on which the records are to be sorted;  the first-named is the major key. For example:

      PROCESS SALES

        ORDER BY (ORDERS.ORDER_NO, ORDERS.CUSTOMER_ID);

orders data into the sequence of CUSTOMER_ID within ORDER_NO.

See also sort-key, below.

process-block

The process-block may consist of any number of Jazz statements, and it is executed once for each qualifying record which is retrieved by the PROCESS statement. For example:

      PROCESS CUSTOMERS ORDER BY (CUSTOMER.CUST_NAME);

        PRINT (CUSTOMERS.*);

      ENDPROCESS;

will retrieve each record from CUSTOMERS, and print all the fields in each of those records.

rdb-retrieval-list

You can precisely control which columns are to be presented to the process- block by listing their column-names. If you do not specify a list of column- names, Jazz will retrieve all those columns to which there is a  reference in your program.

You may name more than two tables in the list. If you do, a WHERE option  must be present, and the WHERE condition must contain a condition relating a column in one table to a column in another. For example:

      PROCESS INVOICE, ORDERS

       WHERE(INVOICE.ORDER_NO = ORDERS.ORDER_NO)

in which ORDER_NO is used to match the two tables. You may also include any other conditions.

You may also select specific columns from more than one table, but you  must qualify the column-names with the table-name. For example:

      PROCESS

       CUSTOMER(CUSTOMER.CUSTOMER_ID, CUSTOMER.CUST_NAME), ORDERS(ORDERS.ORDER_NO, ORDERS.CUSTOMER_ID)

         WHERE(CUSTOMER.CUSTOMER_ID=ORDERS.CUSTOMER_ID);

      ENDPROCESS;

Rules

•     A column-reference may be qualified;  if it is not then Jazz  will  search all the variables defined to this program and qualify the  column-reference with the first matching variable name. If no  matching variable name is found or, if the column is contained within a data definition that is not named in the FILE option of the PROCESS statement, then Jazz will issue an error message.

•     Jazz does not relate the list of columns to the rest of the  program. So, no error is detected if:

-     a column is shown in the list but never referenced in the  program, or

-     a column is referenced in the program but does not appear in  the retrieval-list.

rdb-update-list

The rdb-update-list allows you to nominate the columns which are to be  rewritten. Each column-reference may contain an assignment of a NULL  value to any columns to be updated. For example:

            PROCESS CUSTOMER

              WHERE (CUSTOMER.CUSTOMER_ID = 1234)

              UPDATE (CUSTOMER.CUST_NAME=NULL);

Rules

•     A column-reference may be qualified;  if it is not then Jazz  will  search all the variables defined to this program and qualify the  column-reference with the first matching variable name. If no  matching variable name is found or, if the column is contained within a data definition that is not named in the FILE option of the PROCESS statement, then Jazz will issue an error message.

•     You can specify the =NULL option only if the column is not defined  with the NOTNULL option;  see DEFINE in Chapter 2.

•     Jazz does not relate the list of column-references to the rest of  the program. For example, it does not check that the columns in the  rdb-update-list are assigned values in the process-block, or that the  columns referenced in the process-block are included in the rdb- update-list. No error message is produced if a column appears in the  list but is never referenced in the process-block, or if assignments are made to columns which will not be updated or which will be set to  NULL:

            PROCESS CUSTOMER

              WHERE (CUSTOMER.CUSTOMER_ID = 1234)

              UPDATE (CUSTOMER.CUST_NAME=NULL);

              CUSTOMER.CUST_NAME = 'MANA SYSTEMS LTD';

              CUSTOMER.CUST_PHONE = 4190046;

            ENDPROCESS;

      Here, CUST_NAME will be updated with NULL value despite having been assigned a value in the process-block, and CUSTOMER_ID will not be updated at all because it is not included in the rdb-update-list.

•     Only the specified columns will be updated at the ENDPROCESS.

If you do not specify an rdb-update-list, each column from the record which has been referenced by the program is re-written.

record-name

For records defined as TYPE(NDB), the record name must be a record-name in the subschema named in the PROGRAM statement. For other types of records, it must be the name of a record definition.

If the record does not already exist in the program, Jazz will generate a USE statement to retrieve the record's definition from the DATALIB (refer to the MANATALK Manual for a description of each of the Jazz libraries). For example, in:

      PROCESS CUSTOMERS;

Jazz will insert the statement:

      USE CUSTOMERS;

if CUSTOMERS is not already defined in your program.

SET (set-name)

See WITHIN.

sort-key

For numeric and string data, DESCENDING (DESC can be used as an abbreviation) may be specified to put data into descending sequence:

      PROCESS CUSTOMERS

            ORDER BY (CUSTOMER.CUST_NAME DESC);

See also ORDER BY, above.

START({PRIOR | CURRENT | ANY | FIRST | NEXT | LAST})

Note that START(NEXT) is for VSAM only:

•     START(NEXT) may be used if the PROCESS statement is within a ROUTINE statement.

•     You cannot use the WHERE option if you use the START(NEXT) option.

UPDATE

When you use this option, each record that is processed is re-written when the ENDPROCESS statement is executed, provided that no PROCESS UPDATE OFF statement has been executed.

For TYPE(RDB) records, you can nominate the fields to be updated;  see rdb-update-list, above.

UPDATE OFF

You can only use this option in the process-block. If you do use it, no update takes place for the current record. For example, in:

      PROCESS ORDERS UPDATE;

        MATCH CUSTOMERS

          WHERE (CUSTOMER.CUSTOMER_ID =

                 ORDERS.CUSTOMER_ID);

        IF NOT FOUND

          PROCESS UPDATE OFF;

        ENDIF;

      ENDPROCESS;

the PROCESS UPDATE OFF prevents the updated ORDERS record from being re-written at the ENDPROCESS.

If you do not specify the record-name, Jazz assumes the record-name of the PROCESS which controls this process-block.

WHERE(condition)

You can specify a condition that must be true before a record is presented to the process-block.

Note that for RDB II:

•     When sorting, NULL is larger than non-NULL values.

•     NULL cannot be used in a condition list.

•     National Hexadecimal constants cannot be used in a WHERE condition.

For VSAM, note that you cannot use the WHERE option if you use the START(NEXT) option.

Conditions, in Chapter 4, describes the types of conditions and how they can be used.