Building Mass Change Definitions

Once you’ve properly set up the types, templates, prompt tables, and security, you can build a Mass Change definition. When you create a definition, all information will default from its Mass Change type and template, except for the criteria and default field values and users

You define a Mass Change definition by using the Mass Change Definition Component. We discuss the first page, Description, in the previous section. The remaining pages are where you identify criteria fields and default fields, and generate the actual SQL statements.

To add a new definition:

  1. Select Mass Change, Use, Mass Change Definition.

    The Mass Change Definition dialog displays:

    Here is where you identify the Mass Change Template on which the definition will be based.

  2. Select a Mass Change Template, specify an Archive ID and Archive Date, if desired, and enter a Description.

  3. In the Criteria and Defaults page, specify the Criteria and Defaults information.

    In the Criteria and Defaults page, you specify the values of the criteria and default fields defined by the template.

    The Criteria rows consist of a SQL operator (edit box on the left) and a value (on the right). When you specify an operator and a value, you are completing a WHERE clause condition for the field in question. You can only enter one SQL operator per Criteria Field. However, you can enter multiple values by adding rows.

    For each criteria field, select an operator and a value. For each default field, enter a value.

    The following table lists the valid operators, how they are used, and an example of each one as used on a set of Business Units that includes: CORP, FRNGN, NEWGN, SUBCO, and WORLD.

    Operator

    Meaning

    Example

    Result

    <

    Less than

    < NEWGN

    CORP, FRNGN

    < =

    Less than or equal to

    < = NEWGN

    CORP, FRNGN, NEWGN

    < >

    Not equal to

    < >NEWGN

    CORP, FRNGN, SUBCO, WORLD

    =

    Equal to

    = NEWGN

    NEWGN

    > =

    Greater than or equal to

    > = NEWGN

    NEWGN, SUBCO, WORLD

    >

    Greater than

    > NEWGN

    SUBCO, WORLD

    BTW

    Between value A and value B

    BTW CORP SUBCO

    CORP, FRNGN, NEWGN, SUBCO

    NBT

    Not between value A and value B

    NBT CORP SUBCO

    WORLD

    IN

    In a subset of

    IN CORP SUBCO

    CORP, SUBCO

    NIN

    Not in a subset of (complement)

    NIN CORP SUBCO

    FRNGN, NEWGN, WORLD

    LIK

    Like (used with a % wildcard)

    LIK NEW%

    NEWGN

    NLK

    Not like (used with a % wildcard)

    NLK %GN

    CORP, SUBCO, WORLD

    The Default Fields box displays fields that will be updated and allows you to enter a Value for each field. If you’ve created prompt tables for the fields on this page, you can select from a list of valid values for each field.

  4. In the Application Specific Fields page, enter any information required for the definitions for your particular application.

  5. Select the Generate SQL page.

    You use this page to create the SQL statement(s) based on the information you’ve contained in the type, template, and definition. Mass Change gives you the opportunity to check the SQL text generated by a Mass Change definition before actually executing it.

    You view the SQL statement(s) by clicking the Generate SQL button. The SQL text is created and displayed in the large, display-only, edit box.

    If you’re unhappy with the SQL, you can delete it, using the Clear SQL button, and rewrite the Mass Change definition, template, or type, as needed.

    Using the Count button in the lower left corner of the page displays the total number of rows affected by each statement.

    In this page, you can also opt to Execute SQL Upon Saving. If you select this checkbox, the SQL will be executed when you save the page if your Mass Change Operator Security profile authorizes you to execute definitions online. If you do not enable this option, you can save the Mass Change definition, then execute it in the background, using a run control.

  6. Review the statement(s).

    Check the statement text. Be sure any FROM or WHERE clauses reference the proper tables, fields, and values. As a further test, press Count. This displays the total rows affected by each statement.

    Are the totals what you expected? If everything checks out, continue to the next step. If not, redefine the definition, template, or type, as necessary.

  7. Select Execute SQL Upon Saving, if desired.

  8. Click the Save button to save the definition.

    If you selected Execute SQL Upon Saving, the definition begins executing.