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.