infa_函数

UPPER(IPF_NAME) = 'X-SELLING & PRICE STABILITY' AND PERIOD_ID = :LKP.LKP_PERIOD_Q('Calculate Employee IPF %', 'Pending Calculation')

将字符转换成大写 ceil(NEXTVAL/5000)+100进位的意思，ceil(20.21)=21,ceil(20.98)=21,ceil(-21.87)=-21

Character Functions The transformation language includes the following character functions: • ASCII ( ITEMS )返回ASCII码值 • CHR ( ITEM_ID )返回数值的对应字母：for ex:

'Joan' || (39) || 's car' returns Joan's car • CHRCODE • CONCAT

CONCAT( CONCAT( FIRST_NAME, ' ' ), LAST_NAME ) FIRST_NAME LAST_NAME RETURN VALUE John Baer John Baer

• INITCAP ( FIRST_NAME )将第一个字母大写 • INSTR

• LENGTH • LOWER • LPAD • LTRIM • METAPHONE • REPLACECHR • REPLACESTR • RPAD • RTRIM • SOUNDEX • SUBSTR • UPPER

Conversion Functions The transformation language includes the following conversion functions: • TO_CHAR(Date) • TO_CHAR(Number) • TO_DATE • TO_DECIMAL • TO_FLOAT • TO_INTEGER

Data Cleansing Functions The transformation language includes a group of functions to help you eliminate data errors. With data cleansing functions you can:

• Test input value. • Convert the datatype of an input value. • Trim string values. • Replace characters in a string. • Encode strings.

The transformation language includes the following data cleansing functions: • INSTR • IS_DATE • IS_NUMBER • IS_SPACES • ISNULL • LTRIM • METAPHONE • REPLACECHR • REPLACESTR • RTRIM • SOUNDEX • SUBSTR • TO_CHAR • TO_DATE • TO_DECIMAL • TO_FLOAT • TO_INTEGER

Date Functions The transformation language includes a group of date functions to help you round, truncate, or compare dates, extract one part of a date, or perform arithmetic on a date.

You can pass any value with a date datatype to any of the date functions. However, if you want to pass a string to a date function, you must first use the TO_DATE function to convert it to a transformation Date/Time datatype.

The transformation language includes the following date functions: • ADD_TO_DATE • DATE_COMPARE • DATE_DIFF • GET_DATE_PART • LAST_DAY • MAX • MIN • ROUND • SET_DATE_PART • TRUNC

Numeric Functions The transformation language includes the following numeric functions: • ABS • CEIL • CUME • EXP • FLOOR • LN • LOG • MOD • MOVINGAVG • MOVINGSUM • POWER • ROUND • SIGN • SQRT • TRUNC

Scientific Functions The transformation language includes the following scientific functions: • COS • COSH • SIN • SINH • TAN • TANH

Special Functions The transformation language includes the following special functions: • ABORT • DECODE • ERROR • IIF • LOOKUP

Generally, you use special functions in Expression, Filter, and Update Strategy transformations. You can nest other functions within special functions. You can also nest a special function in an aggregate function.

Test Functions The transformation language includes the following test functions: • ISNULL • IS_DATE • IS_NUMBER • IS_SPACES

Variable Functions The transformation language includes a group of variable functions to help you update the current value of a mapping variable throughout the session. When you run a workflow, the PowerCenter Server evaluates the start and current value of a variable at the beginning of the session based on the final value of the variable from the last session run. You can use the following variable functions:

• SetCountVariable • SetMaxVariable • SetMinVariable • SetVariable

Numeric Functions

Function Syntax Description ABS ABS( numeric_value ) Returns the absolute value of a numeric value. Available in the Designer and the Workflow Manager.

• CEIL CEIL( numeric_value )

Returns the smallest integer greater than or equal to the specified numeric value. Available in the Designer and the Workflow Manager.

CUME CUME( numeric_value [, filter_condition ] ) Returns a running total of all numeric values. Available in the Designer.

EXP EXP( exponent ) Returns e raised to the specified power (exponent), where e=2.71828183. Available in the Designer and the Workflow Manager.

FLOOR FLOOR( numeric_value ) Returns the largest integer less than or equal to the specified numeric value. Available in the Designer and the Workflow Manager.

LN LN( numeric_value ) Returns the natural logarithm of a numeric value. Available in the Designer