Network
Data Engineering
Data Engineering Integration
Enterprise Data Catalog
Enterprise Data Preparation
Cloud Integration
Cloud Application Integration
Cloud Data Integration
Cloud Customer 360
DiscoveryIQ
Cloud Data Wizard
Informatica for AWS
Informatica for Microsoft
Cloud Integration Hub
Complex Event Processing
Proactive Healthcare Decision Management
Proactive Monitoring
Real-Time Alert Manager
Rule Point
Data Integration
B2B Data Exchange
B2B Data Transformation
Data Integration Hub
Data Replication
Data Services
Data Validation Option
Fast Clone
Informatica Platform
Metadata Manager
PowerCenter
PowerCenter Express
PowerExchange
PowerExchange Adapters
Data Quality
Axon Data Governance
Data as a Service
Data Explorer
Data Quality
Data Security Group (Formerly ILM)
Data Archive
Data Centric Security
Secure@Source
Secure Testing
Master Data Management
Identity Resolution
MDM - Relate 360
Multidomain MDM
MDM Registry Edition
Process Automation
ActiveVOS
Process Automation
Product Information Management
Informatica Procurement
MDM - Product 360
Ultra Messaging
Ultra Messaging Options
Ultra Messaging Persistence Edition
Ultra Messaging Queuing Edition
Ultra Messaging Streaming Edition
Edge Data Streaming
Knowledge Base
Resources
PAM (Product Availability Matrices)
Support TV
Velocity (Best Practices)
Mapping Templates
Debugging Tools
User Groups
Documentation
English
English
English
Español
Spanish
Deutsch
German
Français
French
日本語
Japanese
한국어
Korean
Português
Portuguese
中文
Chinese
Log Out
PowerCenter
10.4.0
10.5.2
10.5.1
10.5
10.4.1
10.4.0
10.2 HotFix 2
10.2 HotFix 1
10.2
H2L
Transformation Language Reference
PowerCenter 10.4.0
PowerCenter 10.4.0
All Products
Rename Saved Search
Name
* This field is required
Overwrite saved search
Confirm Deletion
Are you sure you want to delete the saved search?
Table of Contents
Search
No Results
Preface
The Transformation Language
The Transformation Language Overview
Transformation Language Components
Internationalization and the Transformation Language
Expression Syntax
Expression Components
Ports and Return Values
String and Numeric Literals
Rules and Guidelines for Expression Syntax
Adding Comments to Expressions
Reserved Words
Constants
DD_DELETE
Example
DD_INSERT
Examples
DD_REJECT
Examples
DD_UPDATE
Examples
FALSE
Example
NULL
Working with Null Values in Boolean Expressions
Null Values in Comparison Expressions
Null Values in Aggregate Functions
Null Values in Filter Conditions
Nulls with Operators
TRUE
Example
Operators
Operator Precedence
Arithmetic Operators
String Operators
Nulls
Example
Comparison Operators
Logical Operators
Nulls
Variables
Built-in Variables
$PM<SourceName>@TableName, $PM<TargetName>@TableName
$PMFolderName
$PMIntegrationServiceName
$PMMappingName
$PMRepositoryServiceName
$PMRepositoryUserName
$PMSessionName
$PMSessionRunMode
$PMWorkflowName
$PMWorkflowRunId
$PMWorkflowRunInstanceName
SESSSTARTTIME
Example
SYSDATE
Example
WORKFLOWSTARTTIME
Example
Transaction Control Variables
Local Variables
Dates
Dates Overview
Date/Time Datatype
Julian Day, Modified Julian Day, and the Gregorian Calendar
Dates in the Year 2000
RR Format String
Example
Difference Between the YY and RR Format Strings
Dates in Relational Databases
Dates in Flat Files
Default Date Format
Date Format Strings
TO_CHAR Format Strings
Examples
J Format String
SSSSS Format String
RR Format String
YY Format String
TO_DATE and IS_DATE Format Strings
Rules and Guidelines for Date Format Strings
Example
J Format String
RR Format String
YY Format String
SSSSS Format String
Understanding Date Arithmetic
Functions
Function Categories
Aggregate Functions
Aggregate Functions and Nulls
Filter Conditions
Binary Functions
Character Functions
Conversion Functions
Data Cleansing Functions
Date Functions
Encoding Functions
Financial Functions
Numeric Functions
Scientific Functions
Special Functions
String Functions
Test Functions
Variable Functions
Window Functions
ABORT
ABS
ADD_TO_DATE
AES_DECRYPT
AES_ENCRYPT
ASCII
AVG
BINARY_COMPARE
BINARY_CONCAT
BINARY_LENGTH
BINARY_SECTION
CEIL
CHOOSE
CHR
CHRCODE
COMPRESS
CONCAT
CONVERT_BASE
COS
COSH
COUNT
CRC32
CUME
DATE_COMPARE
DATE_DIFF
DEC_BASE64
DEC_HEX
DECODE
DECOMPRESS
EBCDIC_ISO88591
ENC_BASE64
ENC_HEX
ERROR
EXP
FIRST
FLOOR
FV
GET_DATE_PART
GREATEST
IIF
IN
INDEXOF
INITCAP
INSTR
ISNULL
IS_DATE
IS_NUMBER
IS_SPACES
LAG
LAST
LAST_DAY
LEAD
LEAST
LENGTH
LN
LOG
LOOKUP
LOWER
LPAD
LTRIM
MAKE_DATE_TIME
MAX (Dates)
MAX (Numbers)
MAX (String)
MD5
MEDIAN
METAPHONE
MIN (Dates)
MIN (Numbers)
MIN (String)
MOD
MOVINGAVG
MOVINGSUM
NPER
PERCENTILE
PMT
POWER
PV
RAND
RATE
REG_EXTRACT
REG_MATCH
REG_REPLACE
REPLACECHR
REPLACESTR
REVERSE
ROUND (Dates)
ROUND (Numbers)
RPAD
RTRIM
SETCOUNTVARIABLE
SET_DATE_PART
SETMAXVARIABLE
SETMINVARIABLE
SETVARIABLE
SHA256
SIGN
SIN
SINH
SOUNDEX
SQRT
STDDEV
SUBSTR
SUM
SYSTIMESTAMP
TAN
TANH
TIME_RANGE
TO_BIGINT
TO_CHAR (Dates)
TO_CHAR (Numbers)
TO_DATE
TO_DECIMAL
TO_FLOAT
TO_INTEGER
TRUNC (Dates)
TRUNC (Numbers)
UPPER
VARIANCE
Creating Custom Functions
Creating Custom Functions Overview
Steps to Create Custom Functions
Installing Custom Functions
Step 1. Get Repository ID Attributes
Step 2. Create a Header File
Step 3. Create an Implementation File
Step 4. Build the Modules
Building the Module on Windows
Building the Module on UNIX
Step 5. Create the Repository Plug-in File
The PLUGIN Element
The FUNCTION_GROUP Element
Determining a Namespace
The FUNCTION Element
The LIBRARY Element
Sample Plug-in XML File
Step 6. Test Custom Functions
Validating the Repository Plug-in File
Viewing Plug-in Metadata Details
Verifying Function Accuracy
Installing Custom Functions
Step 1. Copy Custom Function Libraries to PowerCenter
Step 2. Register the Plug-in
Creating Expressions with Custom Functions
Custom Function API Reference
Custom Function API Reference Overview
Common APIs
Validation Handle
User Interface Validation Function
Custom Function Validation Function
Custom Function Description Function
Custom Function Prototype Function
Custom Function Pushdown Function
INFA_EXPR_OPD_METADATA Structure
Get Plug-in Version Function
Destroy String Function
Run-time APIs
Module-Level Functions
Get User Interface Module-Level Function
Module-Level Initialization Function
Module-Level Deinitialization Function
Function-Level Functions
Get User Interface Function-Level Function
Function-Level Initialization Function
Function-Level Deinitialization Function
Function Instance-Level Functions
Get User Interface Function-Level Function
Function Instance-Level Initialization Function
Function Instance Row Processing Function
Function Instance-Level Deinitialization Function
Transformation Language Reference
Transformation Language Reference
10.4.0
10.5.1
10.5
10.2 HotFix 2
10.2
Back
Next
Scientific Functions
Scientific Functions
The transformation language includes the following scientific functions:
COS
COSH
SIN
SINH
TAN
TANH
Function Categories
Updated December 12, 2019
Download Guide
Send Feedback
Resources
Communities
Knowledge Base
Success Portal
Back to Top
Back
Next
