Query Builder - Intergraph Smart Materials - Version 2020 (10.0) - Training - Hexagon

Smart Materials Web Tutorials 2020 (10.0)

Language
English
Product
Intergraph Smart Materials
Subproduct
Web
Search by Category
Training
Smart Materials/Smart Reference Data Version
2020 (10.0)

A where condition corresponds to a database object called VIEW and is also called as a stored query. Based on the data you provide; the application generates a view and assigns a unique name to the view. The module that is assigned to the where condition can then access the contents of this view using the view name. There are two methods of creating a where condition:

  • Property based

  • Rule based.

The Property based where conditions require the creation of an alias or property that references the base table structure of Smart Materials, whereas the Rule Based where conditions can be built from predefined tables and properties.

You can use the rule-based conditions similar to the property based conditions, except that the conditions defined in the Rules tab are joined using the OR operator and hence you cannot define additional conditions. You can only define alternative conditions, which means any one of the conditions must be fulfilled.

 You cannot define both properties based and rule based definitions for one view.

Properties for Where conditions

To define property based where condition, you must first create properties in the Query Builder > Properties screen as you define selection criteria based on the properties.

The properties used in the where condition are aliases and not the base table property definitions from Smart Materials core database. It is easier for the engineers to easily identify aliases defined by the database administrator than the core database table/property structures. The aliases are called properties for where conditions and the more common ones are delivered by default.

Defining properties involves the following steps:

  1. Assign a unique name and a module reference to the property definition

  2. Decide a base table to select a property

  3. Specify the column of the table.

You must know how the table you choose relates to tables M_IDENTS, M_LIST_NODES, M_LIST_POS, and M_REQ_LINE_ITEMS. Each property must be joined to at least one of these tables. If there is a direct relationship, you might use enter the name of the join column or AUTO. If there are no direct relationships, you can specify the use of a specific intersection table to create the join.

Define properties and property based where condition

  1. From the Home screen, select Administration > Global Setup > Query Builder > Properties.

  2. Click Add Row and type a property name in Property box.

  3. Select a table from the Tables list.

  4. Select a column name from the Columns list.

     The Table and Column define where this property fetches its value from.

  5. In the Join with Table section, do one of the following:

    Select a column from either one or more M_Idents, M_List_Nodes, M_List_POS, or M_Req_Line_Items tables.

    1. Only columns with the same name in both tables are displayed.

    2. Use Intersection Table for tables that have no direct relationships.

      You can also select an intersection table to join the table with the corresponding table M_IDENTS, M_LIST_NODES, M_LIST_POS, or M_REQ_LINE_ITEMS.

  6. You can specify a user-defined where condition in the Additional Query box to further restrict the property.

  7. You can also define an optional SQL statement in the Select List Command box to use as the property value.

    The statement entered must be a valid SQL statement. The SQL statement must have TWO columns in the Select list.

  8. Click Save.

    Use the project default ZX_AQC_MOD to limit the list of property values.

    1. (default) - only the properties that are explicitly assigned to the same module are available

    2. Y - all where condition properties are always available.

Define modules

  1. From the Home screen, select Administration > Global Setup > Query Builder > Modules.

  2. Click Add Row.

  3. Type a menu name in the Module cell.

  4. Select a mapping from the Column Mappings list.

  5. Type description, and then click Save.

Create a Where condition

  1. From the Home screen, select Administration > Global Setup > Query Builder.

  2. Click Add Row, and then type the Where Condition name.

  3. Select the where condition type from the Type list.

    • Property Based - references the base table structure of Smart Materials

    • Rule Based - uses predefined tables and properties from Smart Materials.

  4. Type the short description and description for the where condition.

  5. Select a user from the Users list.

  6. Select Yes from the Regenerate list to enable regeneration of the where condition.

     The Generate View button in the Where Condition Details page is enabled for the where condition when you select Yes.

  7. Select a module name from the Modules list.

  8. Add a comment in the Comment cell, and then click Save.

Where Condition details

To view a where condition, from the Home screen, select Administration > Global Setup > Query Builder.

Based on the Type, you can now add properties or rules to the where condition.