Using Correlated Sub-Queries on A.60.07 - Intergraph Smart Materials - Version 10.2 - Administration & Configuration - Hexagon

Intergraph Smart Materials Classic Administration (10.2)

Language
English
Product
Intergraph Smart Materials
Subproduct
Classic
Search by Category
Administration & Configuration
Smart Materials/Smart Reference Data Version
10.2

There are two ways to set up selection restrictions:

  • Include them as a comparison attribute in the A.60.06 definition

  • Implement them as a correlated sub-query in the Additional WHERE-Condition field on the A.60.07 screen

Defining the selection restriction as a comparison attribute in the A.60.06 definition enables you to use the attribute as a query parameter on the A.60.06 screen. This allows you to use the same attribute with different comparison values in different query conditions. Often, a static configuration provides better performance because you can save a large join table.

For example, you could have the Shop/Field-Indicator set up as a variable BOM position attribute. The Shop/Field-Indicator (SFI) is a states domain. In other words, the variable contains only values for shop material (S) or field material (F). To restrict selection using the Shop/Field-Indicator, you can set up the attributes as follows:

  1. Set the LP_ID attribute with a source column value of M_LIST_POS.LP_ID on A.60.07.

  2. Set the SFI attribute with source column value of M_LIST_POS_VALUES.ATTR_VALUE on A.60.07.

  3. Join to M_LIST_POS using the LP_ID attribute.

  4. Create a sub-query against M_ATTRS that only selects records whose M_LIST_POS_VALUES contain the referenced attribute SFI (attr_id).

You now have two criteria in the A.60.06 module:

  • LP_ID mapped to LP_ID

  • SFI = '<some_value>'

This condition joins the LP_ID attribute M_LIST_POS and the SFI attribute M_LIST_POS_VALUES based on a comparison of the desired value of the Shop/Field-Indicator and performs the intended task.

However, the following setup is more efficient:

  1. In the A.60.07 module, set the LP_ID_FIELD attribute with source column value M_LIST_POS.LP_ID.

  2. Add an additional WHERE-condition as shown in the following example:

    EXISTS (SELECT 1 FROM mvp_attrs a, mvp_list_pos_values lpv

    WHERE BASE_TABLE.lp_id = lpv.lp_id

    AND lpv.attr_id = a.attr_id

    AND a.attr_code = 'SFI'

    AND lpv.attr_value = 'F')

  3. Set up the LP_ID_SHOP attribute in a similar manner but set the last comparison value lpv.attr_value = 'S'.

This operation is usually more efficient. If you now map the LP_ID_FIELD attribute to the LP_ID attribute, only one table incarnation (M_LIST_POS) is present in the created view instead of two (M_LIST_POS and M_LIST_POS_VALUES) as in the preceding example.