Create or Modify an Expression
In Oracle DMW, expressions in both validation checks and transformations are defined in the EXPR_OBJ_TYPE attribute of the DME_MAP_ENTITY_OBJ_TYPE. The EXPR_OBJ_TYPE attribute is of the DME_XFORM_EXPR_OBJ_TYPE object and its required values are:
- EXPRESSION_ID: If you are modifying an expression, enter the expression object ID.
- EXPRESSION_MODE: Enter one:
- $EXPRMODE$CRITERIA if it is a criteria expression.
- $EXPRMODE$EXPR if it is a column expression.
- EXPRESSION_TEXT: Enter the string representation of expression, adhering to
the following rules:
- All the values should be single quoted. Numeric values can be applied without single quotes.
- The column names should be specified in the following format: {ModelName.TableName.ColumnName}. For example, to specify a condition on the 'AGE' column from 'DM' table in 'Source' data model that AGE should be greater than 30, the expression_text should be passed as: {Source.DM.AGE} > 30
- To specify a user-defined functions, the package should be available in the view DME_PUB_XFM_EXPR_STATIC_PKGS_V. Refer to the static reference details for the package that contains the function.
- EXPRESSION_ITEM_COLL - Use this parameter only if you are calling a function
in the expression. This is a parameter of collection type
DME_XFORM_EXPR_ITEM_OBJ_COLL which is a table of DME_XFORM_EXPR_ITEM_OBJ_TYPE object
type.
For each expression_text, the collection must be populated with one record with the following attribute assignments:
PARENT_ROW_ID = -1; ROW_ID=0; ROW_POS=1; ITEM_TYPE_RC=' $EXPITEMTYPE$GROUP'
If user-defined functions are used in expression_text, the collection should be populated with a separate record for each distinct user-defined function used in expression. The following attributes should be assigned for a record used for the user defined function:
- PARENT_ROW_ID = 0;
- ROW_POS = 1;
Note:
The above two values should always be 0 and 1, respectively. - ROW_ID - The row_id must be unique and sequential starting from 1 for each distinct user defined function used in the given expression.
- ITEM_TYPE_RC =' $EXPITEMTYPE$FUNCTION';
Enter values for the following two attributes from the DME_XFM_EXPR_STATIC_PKGS_V view.
- ITEM - Enter the name of the user-defined function.
- STATIC_REFS - Enter the function ID corresponding to the function used in expression.
Parent topic: Introduction to Oracle DMW APIs