(Optional) Creating Expressions

This section provides an overview of expressions and discusses how to create expression statements.

Page Used to Create Expressions

Page Name	Definition Name	Navigation	Usage
Expressions	EOEW_SRCFIELDEXPR	select Enterprise Components, then select Data Transformation, then select Define Source Data Objects, then select Source Data Object, then select Expressions	Create expression statements by selecting operators, fields, and constant values.

Understanding Expressions

Expressions enable you to create virtual columns that are made up of mathematical calculations based on actual fields in a record. Expressions are resolved at runtime so that duplicate information is not stored in the database. Expressions can be numeric, string, date, time, or datetime. Numeric expressions can combine any number of fields, constant values, and math operators, and results are rounded based on the decimal position that is selected. String expressions can be concatenations of any number of character fields and constant values.

Built-in Functions

PeopleSoft delivers built-in functions that you can use to make expressions powerful and flexible. You can also nest built-in functions and expressions; for example:

Concatenate two fields, separate them with a comma, drop trailing blanks, and limit this field to 40 characters:

Substring(RTRIM(FirstName) | , | RTRIM(LastName),1,40)
Sum the multiplication of two fields:

SUM(PROD_COST * PROD_RATE)

This table lists all of the delivered built-in functions with supported meta-SQL and aggregate functions:

Function	Explanation	Use
ABS(Number)	Returns the absolute value of the specified number.	ABS(-5) returns 5.
AddMonth(Date, Number)	Adds a number of months to the specified date and returns the calculated date.	AddMonth(“2002–01–10”,2) returns 2002–03–10.
Average(Number)	Returns the average value of the specified number (typically a numeric column name).	Average(PRICE) returns the average price of your source data set.
Beginning of Month(Date)	Returns a date representing the first day of the month for the specified date.	Beginning of Month(“2002–10–05”) returns 2002–10–01.
Beginning of Year(Date)	Returns a date representing the first day of the year for the specified date.	Beginning of Month(“2002–10–05”) returns 2002–01–01.
Count(col)	Returns the number of rows that are found.	Count(PRODUCT_ID) returns the number of rows on your source data set.
DateAdd(Date, Days)	Adds a number of days to the specified date and returns the calculated date.	DateAdd(“2002–02–02”,5) returns 2002–02–07.
DateDiff(Date, Date)	Returns the number of days that have elapsed between the first and second specified dates.	DateDiff(“2002–02–02”,”2002–02–10”) returns 8.
DtTmDiff(Datetime,Datetime)	Returns the number of minutes that have elapsed between the first and second specified datetimes.	DtTmDiff(”'2002-01-01 00:00:00.000”,”2002-01-01 10:00:00.000”) returns 600 (10 hours in minutes).
DIFF_H(Datetime, Datetime)	Returns the number of hours that have elapsed between the first and second specified datetimes.	DIFF_H(”'2002-01-01 00:00:00.000”,”2002-01-01 10:00:00.000”) returns 10.
DIFF_S (Datetime, Datetime)	Returns the number of seconds that have elapsed between the first and second specified datetimes.	DIFF_S(”'2002-01-01 00:00:00.000”,”2002-01-01 10:00:00.000”) returns 36000 (10 hours in seconds).
End of Month(Date)	Returns a date representing the last day of the month for the specified date.	End of Month(“2002–10–05”) returns 2002–10–31.
End of Year(Date)	Returns a date representing the last day of the year for the specified date.	End of Year(“2002–10–05”) returns 2002–12–31.
GetDay(Date)	Returns the day of the month for the specified date.	GetDay(“2002–10–05”) returns 5.
GetMonth(Date)	Returns the month of the specified date.	GetMonth(“2002–10–05”) returns 10.
GetYear(Date)	Returns the year of the specified date.	GetYear(“2002–10–05”) returns 2002.
MOD(Number, Number)	Returns the modulus (or remainder) of the first number when it is divided by the second number.	MOD(14,3) returns 2 (14 divides into 3 four times with a remainder of 2).
Max(Number)	Returns the maximum value of the specified number (typically a numeric column name).	Max(PRICE) returns the largest price of your source data set.
Min (Number)	Returns the minimum value of the specified number (typically a numeric column name).	Min(PRICE) returns the smallest price of your source data set.
Sum(Number)	Returns the sum of the specified number (typically a numeric column name).	Sum(PRICE) returns the sum of all prices of your source data set.
RTRIM(String)	Returns the specified string, and trims trailing blank characters.	RTRIM(“Hello “) returns Hello.
Round(Number, Factor)	Returns a number representing the specified number after being rounded to the given factor or precision.	Round(5.582, 1) returns 5.6. Round(5.512, 0) returns 6.
Substring(String, StartPos, Length)	Returns a section of the specified string starting at the specified position and continuing for the specified length.	Substring(“My String Expression“, 4,6) returns String.
TO_CHAR(Number)	Returns a string representation of the specified numeric expression.	TO_CHAR(59) returns 59.
TO_NUM(String)	Returns a numeric representation of the specified string expression.	TO_NUM(“59”) returns 59. Note: Trying to apply a TO_NUM built-in function on a character that does not translate to a numeric value aborts the processing of your map.
TrimSubStr(String, Start, Length)	Returns a section of the specified string starting at the specified position and continuing for the specified length, and trims trailing blank characters.	Substring(“My String Expression“, 4,7) returns String.
Truncate (Number, Factor)	Returns a number representing the specified number after being truncated to the given factor or precision.	Truncate(5.582, 1) returns 5.5. Truncate(5.512, 0) returns 5.
UPPER(String)	Returns the specified string in uppercase characters.	UPPER(“My String Expression“, 4,7) returns MY STRING EXPRESSION.
YYMMDD(Date)	Returns the string representation of a specified date in YYMMDD format.	YYMMDD(“2002–10–01”) returns 021001.

Expressions Page

Use the Expressions page (EOEW_SRCFIELDEXPR) to create expression statements by selecting operators, fields, and constant values.

Navigation

select Enterprise Components, then select Data Transformation, then select Define Source Data Objects, then select Source Data Object, then select Expressions

Image: Expressions page

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

Name

Enter a unique name for this expression.

Expression Field Type

Select to control the set of operators, record fields, and value objects that can be selected when building the expression.

Numeric: Select to combine any number of record fields, value objects, and math operators.

String: Select to concatenate any number of character record fields and value objects.

Date: Select to define a constant value or to create a date field from the source data object or built-in functions.

Expression Statement

Displays your expression as you build it. You cannot type directly into this edit box. Use operators, data source options, and the constant values to create your expressions.

You can see what element is being specified by the arrows surrounding it; the selection is also displayed in the Current Element group box.

Clear

Click to delete all entries in the Expression Statement edit box.

Operators

Select from the operator buttons to add operators to the expression.

Data Source

Data Object Column

Select this option to display a list of data object fields. Select a field and click Insert to have the selected data object appear in the expression statement.

Built-in Function

Select this option to display a list of built-in functions. Select a function and click Insert to have the selected built-in function appear in the expression statement.

Built-in functions let you define numeric calculations like Sum, Average, or End of Month, and Minimum/Maximum values.

Note: All parameters of the selected built-in function must be defined.

Constant Value

Select this option to enter your own character, numeric, or date value. Click Insert to have the value appear in the expression statement.

Note: Consider how an expression will be used when you want to specify date related value objects. If the expression will be in a WHERE clause, then use the %CurrentDateIn value object. If the expression will be in a SELECT clause, then use the %CurrentDateOut value object.

Current Element

Click to move through the expression statement and identify the text you want to replace or delete or to move to the position in the statement where you want to insert text.

Click the Previous or Next button to move one element forward or backward. Click the First or Last button to move to the beginning or end of the expression statement.

Note: The element name will be shown in the Current Element group box as well as surrounded by arrows in the Expression Statement edit box.

Replace

Click to replace the current element item in the expression statement with the selected data source.

Insert

Click to insert the selected data source into the expression statement before the current element item.

Delete

Click to delete the current element item in the expression statement.

Example: Creating Expressions by Using Data Object Column

This section provides an example of creating an expression by using a data object column.

The company wants to award its employees with a bonus of $1000 for each year that they have been employed by the company. To calculate the bonus:

Enter BONUS for the expression name, and then enter a description.
Select the Number expression type.
Select integer and decimal positions.
Select Data Object Column in the Data Source group box.
Select the YEARS_OF_EMP data object column, and click Insert.

The field appears in the Expression Statement edit box.
Click the Multiplication (*) operator to insert it at the end of the expression statement.
Select Constant Value in the Data Source group box.
Enter a numeric value of 1000, and click Insert.

This creates the following expression statement:

YEARS_OF_EMP * 1000
Click Save.

Example: Creating Expressions Using Built-in Functions

This section provides an example of creating an expression by using a built-in function.

You want to define a field called SHORT_DESCR that uses the first 10 characters of the DESCR field.

To define the SHORT_DESCR field:

Enter SHORT_DESCR for the expression name, and then enter a description.
Select the Char expression type.
Enter the length.
Select Built-in Function in the Data Source group box.
Select the Substring (String,Start,Length) built-in function, and click Insert.

The function appears as SUBSTRING ( PARAM1 , PARAM2 , PARAM3 ) in the Expression Statement edit box.
Use the arrow buttons to navigate through the expression statement and identify the PARAM1 element.
Select Data Object Column in the Data Source group box.
Select DESCR, and click Replace.

The DESCR field appears in place of PARAM1 in the expression statement.
Navigate to PARAM2.
Select Constant Value in the Data Source group box, and enter a numeric value of 1.
Click Replace.
Navigate to PARAM3, and replace it with a numeric value of 10.

This creates the following expression statement:

SUBSTRING ( DESCR , 1 , 10 )
Click Save.