Setting Up Expressions

Expressions enable you to create virtual columns that are made up of mathematical calculations based on actual fields on a table. Because expressions are resolved at runtime, duplicate information is not stored in the database. Expressions are user-defined columns that you add to a datamap. After you add them as columns to the underlying datamap, you can use them in filters just like record fields. An expression can be either numeric or a string. Numeric expressions can combine any number of record fields, value objects, and math operators. String expressions can be concatenations of any number of character record fields and value objects.

Note: On DB2 UDB for OS/390 and z/OS, you should compose your expressions so that multiplication occurs before division; otherwise, decimal precision may be affected as values may be truncated. Use parentheses where necessary to control the order of calculation to ensure correct decimal precision.

This topic discusses how to define expressions.

Page Used to Set Up Expressions

Page Name	Definition Name	Navigation	Usage
Expression	PF_EXPR_DEFN	select EPM Foundation, then select Business Metadata, then select Constraint and Expressions, then select Expression	Define an expression.

Expression Page

Use the Expression page (PF_EXPR_DEFN) to define an expression.

Navigation

select EPM Foundation, then select Business Metadata, then select Constraint and Expressions, then select Expression

Image: Expression page

This example illustrates the fields and controls on the Expression page. You can find definitions for the fields and controls later on this page.

DataMap Code

Select the datamap that you want to build your expression on. You cannot change the datamap code after you have associated a datamap with an expression.

Rounding

For numeric expressions, the result of the expression is rounded based on the precision that is selected. For all the other types of expressions, the rounding factor is not applicable.

Type

Controls the set of operators, record fields, and value objects that can be selected. Values are:

Numeric: These expressions can combine any number of record fields, value objects, and math operators.

String: These expressions can be concatenations of any number of character record fields and value objects.

Date: These expressions can be a constant data or a date field that is derived from the datamap or built-in functions.

Expression Statement

Displays the expression that you build by selecting operators, fields, or value objects in the group boxes below. This is where you build your expression logic. You cannot type directly in the Expression Statement field.

First, select a data source. The data source that you select presents you with different options below it. To add an element to the expression statement, select it from the data source, and then click the Insert button. The system inserts your selection into the text area. Use the arrow buttons at the bottom to move in the text area. You can see what element is being specified by the arrows surrounding it, for example >>Effective Date<<.

Note: >>Effective Date<< is also displayed in the Current Element group box.

Operators

Select from the operators buttons to add the indicated value to the expression.

DataMap Column

Select to display a datamap column drop-down list box. Select the appropriate datamap column from the list and use the Insert button to place your selection in the text area of the page.

Built-In Function

Select to display a built-in function drop-down list box. Select the appropriate function from the list and use the Insert button to place your selection in the text area of the page. Built-in functions enable you to define numeric calculations, for example sum, average, or end of month, and minimum and maximum values.

Constant Value

Select to make available the constants options and, depending on your choice, a drop-down list box. The Ad Hoc option enables you to enter your own values for the character, numeric, and date field. The From List drop-down list box contains valid value objects. Use the Insert button to place your selection in the text area of the page.

Note: It is important to consider how an expression will be used when you want to specify date-related value objects. If the expression is to be used in a WHERE clause, then the %CurrentDateIn value object must be used. If the expression is to be used in a SELECT clause, then the %CurrentDateOut value object must be used.

Clear

Click to erase all of the text in the Expression Statement box.

First, Previous, Next, Last

Use these arrow buttons to move in the Expression Statement area of the page and identify the text that you want to replace, insert, or delete. When you click either > or < , you move one element forward or backwards. If you use the double arrows >> or <<, you move either to the beginning of the expression text or to the end. You can see what element is being specified by the arrows surrounding it, for example >>Effective Date<<.

Replace

Use the arrow buttons to specify the element that you want to replace, select another element, and then click Replace.

Insert

After you have selected a data source, use this button to insert it into the Expression Statement text area. The system inserts the element at the position designated by the work area, pushing everything else to the back.

Delete

Use the arrow buttons to specify the element that you want to delete, and then click Delete.

When you have completed your expression, click the Compile button to compile the expression.

SQL Functions That Are Available to the Expression Builder

The following table lists the SQL functions that are available to build expressions.

SQL Object ID	Expression Function	Return Value	Description
PF_FUNCLIB_DATE_DAYADD_UPD	AddtoDays (Date, Integer)	Date	Increase date by adding days (Integer).
PF_FUNCLIB_DATE_DAYDIFF_UPD	DiffDates (Date, Date)	Integer	Calculate difference between two dates.
PF_FUNCLIB_DATE_DAY_UPD	GetDay(Date)	Integer	Returns numeric day of the month from date.
PF_FUNCLIB_DATE_MONADD_UPD	AddtoMonth(Date, Integer)	Date	Increase date by adding months (Integer).
PF_FUNCLIB_DATE_MONBEG_UPD	BOM(Date)	Date	Returns the date value for the beginning of the month.
PF_FUNCLIB_DATE_MONTH_UPD	GetMonth(Date)	Integer	Returns numeric month from date.
PF_FUNCLIB_DATE_YEARBEG_UPD	BOY(Date)	Date	Returns date for the beginning of the year.
PF_FUNCLIB_DATE_YEAREND_UPD	EOY(Date)	Date	Returns date for the end of the year.
PF_FUNCLIB_DATE_YEAR_UPD	GetYear(Date)	Integer	Returns numeric year from date.
PF_FUNCLIB_DATE_YYMMDD_UPD	YYMMDD(Date)	Character	Formats date YYMMDD.
PF_FUNCLIB_DIFF_H(DTTM,DTTM)	DTTMDIFF_H(Date, Date)	Integer	Date time difference in hours.
PF_FUNCLIB_DTTM_DTTMDIFF_M_UPD	DTTMDIFF_M(Date, Date)	Integer	Date time difference in minutes.
PF_FUNCLIB_DIFF_S(DTTM,DTTM)	DTTMDIFF_S(Date, Date)	Integer	Date time difference in seconds.
PF_FUNCLIB_MATH_ABS_UPD	ABS(Integer)	Integer	Absolute value.
PF_FUNCLIB_MATH_MOD_UPD	MOD(Integer, Integer)	Integer	Modulus.
PF_FUNCLIB_MATH_TO_NUMB_UPD	TO_NUM(Character)	Integer	Convert to number.
PF_FUNCLIB_RTRIM	RTRIM(Character)	Character	RTrim blanks.
PF_FUNCLIB_TO_CHAR_UPD	TO_CHAR(Integer)	Character	Convert to character.

Note: All expression functions are operating system independent and database dependent.