Content for Property based where condition - Intergraph Smart Materials - Version 2020 (10.0) - Help - Hexagon

Intergraph Smart Materials Web Help (2020)

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

The properties used in the where condition are not the base table property definitions from Smart Materials core database but are are aliases. This helps you to easily identify aliases defined by the database administrator than having to understand the core database table/property structure. The idea is to avoid data errors due to different interpretations of the base structure. The aliases are called Properties for Where Conditions and the common properties are delivered with the software.

Setting up properties involves the following steps:

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

  • Specify the base table for the property.

  • Specify the column name of the table.

  • You can specify a user-defined where condition to further restrict the property. If you refer to the base table of the property, use the prefix BASE_TABLE.

You must also understand how the selected table relates to M_IDENTS, M_LIST_NODES, M_LIST_POS, and M_REQ_LINE_ITEMS tables. Remember the following points while creating a join:

  • Each property must be joined to at least one of these tables.

  • If there is a direct relationship, enter the name of the join column or AUTO.

    Use the AUTO option carefully. Check the SQL command of the generated view as it might result in an unwanted cross join and thus retrieve in a wrong result set.

  • If no direct relationship exists, specify the use of a specific intersection table to create the join.

You can specify a user-defined where condition to further restrict the property. To refer to the base table of the property, use the prefix BASE_TABLE.

Where condition criteria definition

You must define the selection criteria for the selected where condition in the Properties tab of the Where Condition Details page. Use the following operators for selection criteria:

Operator

Description

MAPPED TO

This is a system internal operator used to define the column mapping. Mapping can be created to the database columns IDENT, LP_ID, LN_ID, RLI_ID.

Example:

IDENT MAPPED TO IDENT

This creates the relation of the alias (property) IDENT to the database column IDENT.

=

<>

<

<=

>

>=

These operators perform comparisons.

Examples:

ATTR = 'XA' -> matches only values equal to XA

ATTR <= 5 -> matches any value less than or equal 5

BETWEEN

NOT BETWEEN

For these operators, you must define a range as a comparison value.

Example:

ATTR BETWEEN 2 AND 4 -> matches any values greater than 2 and less than 4

ATTR NOT BETWEEN 10 AND 50 -> matches any values less than 10 and greater 50

LIKE

NOT LIKE

These operators perform SQL wildcard matching. You can use _ (underscore) as the placeholder for exactly one character, or % (percent) to match any combination of characters.

Examples:

ATTR LIKE 'X%' matches any value beginning with X.

ATTR LIKE 'B_M' matches any three character value beginning with B and having M as the third character.

ATTR NOT LIKE ‘A%’ matches any value not beginning with A.

IN

When using this operator, you must define a list of values to be included in the selection as the corresponding value.

Example:

ATTR IN ('XX', 'YY', 'ZZ') -> will only fetch records with the assigned values ‘XX’, ‘YY’, or ‘ZZ’.

NOT IN

When using this operator, you must define a list of values to be excluded from the selection as the corresponding value.

Example:

ATTR NOT IN ('AB', 'AC', 'AD') -> will only fetch records not having the values ‘AB’, ‘AC’, or ‘AD’ assigned.

IS

This operator checks for empty fields. You must define either NULL or NOT NULL as the corresponding value.

Example:

ATTR IS NOT NULL

INPUT

Can be used to create parameters for formulas. This property will become a field in the view definition. Refer to the online help of the A.60.08 Define Formulas page for a detailed explanation.

OUTPUT

Can be used to create an updateable property for use in a formula. Internally, this will include the ROWID of the base table record in the result set of the view. Refer to the online help of the A.60.08 Define Formulas page for a detailed explanation.

DISPLAY

Can be used to include an property for display only as a column in the view definition.

Use quotes and parentheses for values of type VARCHAR2.

The predefined columns are IDENT, LN_ID, LP_ID and RLI_ID and refer to the primary key columns of tables M_IDENTS (Ident Data), M_LIST_NODES (BOM structure data), M_LIST_POS (BOM positions data) and M_REQ_LINE_ITEMS (Requisition line items data), respectively.

Predefined column

Linked table

Primary key

Comment

IDENT

M_IDENTS

IDENT

Ident data

LN_ID

M_LIST_NODES

LN_ID

BOM structure data

LP_ID

M_LIST_POS

LP_ID

BOM positions data

RLI_ID

M_REQ_LINE_ITEMS

RLI_ID

Requisition line items data

You must map at least one of these columns, which from a logical point. This specifies that that you are selecting specific data from one of the mentioned tables. The other properties that are not involved in mapping serve to restrict this data according to the selection criteria.

You are not free to choose any mapping, however. Instead, specific modules need a specific column mapped, because their functionality depends on the presence of a primary key column of a specific table in the result set of the view. You must pay attention to the following requirements:

Requisition templates [R.10.41]

Requisition templates must have the column IDENT mapped, because template views perform a selection based on the kind of material for a particular requisition.

Requisition split criteria [R.10.42]

Requisition split conditions must have the column LP_ID mapped, because split sets define how BOM position data is split up so that they make up different requisitions.

BOM Rules [B.10.05]

These rules define how assembly structures are exploded in different stages of a project. You need to map the column IDENT for conditions that you wish to assign to these rules, because the parts that make up an assembly are identified by their idents.

Conditions for other BOM tools [B.20.01.*]

The BOM tools operate on the table M_LIST_POS, so in order to define the data they should operate upon, you must have the column LP_ID mapped.

The MAPPED_TO record in the second block will automatically be generated when saving the new where condition, based on the assigned module and in relation to the definition of the A.60.05 Where Condition Modules page, as described above in the Where Condition Header Definition section.

There must be at least one column mapping defined for the where condition. Duplicate mappings are not allowed.

Using the Boolean field, you can connect two conditions with a Boolean operator. Only the operators ‘AND’ and ‘OR’ are allowed; the negation NOT is handled using the comparison operator.

Select the Case check cell to define this condition as a case-sensitive condition.