Preface
Working with Transformations
- Transformations Overview
- Creating a Transformation
- Configuring Transformations
  - Renaming Transformations
- Transformation Ports
- Multi-Group Transformations
- Working with Expressions
- Local Variables
- Default Values for Ports
- Configuring Tracing Level in Transformations
- Reusable Transformations
Address Validator Transformation
- Address Validator Transformation Overview
- Address Reference Data
  - Types of Address Reference Data
- Modes and Templates
- Port Groups and Port Selection
- Address Validator Transformation Input Port Groups
- Address Validator Transformation Output Port Groups
- Multiple-Instance Ports
- Address Validation Projects
- Formatted Addresses and Mail Carrier Standards
- Partial Address Completion
- Address Validator Status Ports
  - Element Status Code Definitions
  - Address Resolution Code Output Port Values
  - Element Input Status Output Port Values
  - Element Relevance Output Port Values
  - Element Result Status Output Port Values
  - Extended Element Result Status Output Port Values
  - Mailability Score Output Port Values
  - Match Code Output Port Values
  - Geocoding Status Output Port Values
- Address Validator Transformation General Settings
- Address Validation Properties in the Preferences Window
  - Address Validation Data Properties
  - Address Validation License Properties
  - Address Validation Engine Properties
- Address Validation Advanced Properties
  - Alias Locality
  - Alias Street
  - Casing Style
  - Country of Origin
  - Country Type
  - Default Country
  - Dual Address Priority
  - Element Abbreviation
  - Execution Instances
  - Flexible Range Expansion
  - Geocode Data Type
  - Global Max Field Length
  - Global Preferred Descriptor
  - Input Format Type
  - Input Format With Country
  - Line Separator
  - Matching Alternatives
  - Matching Extended Archive
  - Matching Scope
  - Max Result Count
  - Mode
  - Optimization Level
  - Output Format Type
  - Output Format With Country
  - Preferred Language
  - Preferred Script
  - Ranges To Expand
  - Standardize Invalid Addresses
  - Tracing Level
- Certification Reports
  - AMAS Report Fields
  - CASS Report Fields
  - SendRight Report
  - SERP Report Fields
- Configuring an Address Validator Transformation
- Adding Ports to the Address Validator Transformation
- Creating User-Defined Templates
- Defining Address Validator Models
- Defining a Certification Report
- Address Validator Transformation in a Non-native Environment
  - Address Validator Transformation on the Blaze Engine
  - Address Validator Transformation on the Spark Engine
    - Address Validator Transformation in a Streaming Mapping
  - Address Validator Transformation on the Databricks Spark Engine
Aggregator Transformation
- Aggregator Transformation Overview
- Components of the Aggregator Transformation
  - Configuring Aggregator Transformation Properties
  - Configuring Aggregator Transformation Ports
- Configuring Aggregate Caches
- Aggregate Expressions
- Group By Ports
  - Non-Aggregate Expressions
  - Default Values
- Using Sorted Input
  - Sorted Input Conditions
  - Sorting Data
- Creating an Aggregator Transformation
- Tips for Aggregator Transformations
- Troubleshooting Aggregator Transformations
Association Transformation
- Association Transformation Overview
- Memory Allocation
- Association Transformation Advanced Properties
Bad Record Exception Transformation
- Bad Record Exception Transformation Overview
- Bad Record Exception Output Record Types
- Bad Record Exception Management Process Flow
- Bad Record Exception Mappings
  - Bad Record Exception Quality Issues
  - Human Tasks
- Bad Record Exception Ports
  - Bad Record Exception Transformation Input Ports
  - Bad Record Exception Transformation Output
- Bad Record Exception Configuration View
  - Generating the Bad Records Table and the Issues Table
- Bad Record Exception Issue Assignment
  - Assigning Ports to Quality Issues
- Exception Transformation Advanced Properties
- Configuring a Bad Record Exception Transformation
- Bad Record Exception Mapping Example
Case Converter Transformation
- Case Converter Transformation Overview
- Case Strategy Properties
- Configuring a Case Converter Strategy
- Case Converter Transformation Advanced Properties
- Case Converter Transformation in a Non-native Environment
Classifier Transformation
- Classifier Transformation Overview
- Classifier Models
- Classifier Algorithms
- Classifier Transformation Options
- Classifier Strategies
- Classifier Transformation Advanced Properties
- Configuring a Classifier Strategy
- Classifier Analysis Example
- Classifier Transformation in a Non-native Environment
Cleanse transformation
- Cleanse transformation configuration
  - Cleanse asset considerations
  - Synchronizing data quality assets
- Cleanse transformation field mappings
- Cleanse transformation output fields
- Advanced properties
Comparison Transformation
- Comparison Transformation Overview
- Field Matching Strategies
- Identity Matching Strategies
- Configuring a Comparison Strategy
- Comparison Transformation Advanced Properties
- Comparison Transformation in a Non-native Environment
Custom Transformation
- Custom Transformation Overview
- Creating Custom Transformations
  - Rules and Guidelines for Custom Transformations
  - Custom Transformation Components
- Working with Groups and Ports
- Working with Port Attributes
  - Editing Port Attribute Values
- Custom Transformation Properties
  - Setting the Update Strategy
  - Working with Thread-Specific Procedure Code
- Working with Transaction Control
- Blocking Input Data
- Working with Procedure Properties
- Creating Custom Transformation Procedures
Custom Transformation Functions
- Custom Transformation Functions Overview
  - Working with Handles
- Function Reference
- Working with Rows
  - Rules and Guidelines for Row-Based and Array-Based Data Access Mode
- Generated Functions
- API Functions
- Array-Based API Functions
Consolidation Transformation
- Consolidation Transformation Overview
- Consolidation Mappings
- Consolidation Transformation Ports
- Consolidation Transformation Views
- Simple Strategies
- Row-Based Strategies
- Advanced Strategies
- Simple Consolidation Functions
- Row-Based Consolidation Functions
- Consolidation Mapping Example
- Configuring a Consolidation Transformation
- Consolidation Transformation in a Non-native Environment
Data Masking Transformation
- Data Masking transformation
- Masking Properties
  - Locale
  - Masking Types
  - Repeatable Output
  - Seed
  - Mapping Parameters
  - Associated O/P
- Key Masking
  - Masking String Values
  - Masking Numeric Values
  - Masking Datetime Values
- Substitution Masking
  - Dictionaries
  - Storage Tables
    - Encrypting Storage Tables for Substitution Masking
  - Substitution Masking Properties
  - Relational Dictionary
  - Connection Requirements
  - Rules and Guidelines for Substitution Masking
- Dependent Masking
  - Dependent Masking Example
  - Repeatable Dependent Masking
- Random Masking
  - Masking Numeric Values
  - Masking String Values
  - Masking Date Values
- Applying Masking Rules
  - Mask Format
  - Source String Characters
    - Source String Example
  - Result String Replacement Characters
    - Result String Replacement Characters Example
  - Range
  - Blurring
    - Blurring Numeric Values
    - Blurring Date Values
- Expression Masking
  - Repeatable Expression Masking
  - Rules and Guidelines for Expression Masking
- Format Preserving Encryption
- Special Mask Formats
- Social Security Number Masking
  - Social Security Number Format
  - Area Code Requirement
  - Repeatable Social Security Number Masking
- Credit Card Number Masking
- Phone Number Masking
- Email Address Masking
  - Advanced Email Masking
    - Configuration Parameters for an Advanced Email Address Masking Type
    - Expressions for the Advanced Email Address Masking Type
- Social Insurance Number Masking
  - SIN Start Digit
  - Repeatable SIN Numbers
- IP Address Masking
- URL Address Masking
- Default Value File
- Data Masking Transformation Session Properties
- Rules and Guidelines for Data Masking Transformations
Data Masking Examples
- Name and Address Lookup Files
- Substituting Data with the Lookup Transformation
- Masking Data with an Expression Transformation
Decision Transformation
- Decision Transformation Overview
- Decision Transformation Functions
- Decision Transformation Conditional Statements
- Decision Transformation Operators
- Decision Transformation NULL Handling
- Configuring a Decision Strategy
- Decision Transformation Advanced Properties
- Decision Transformation in a Non-native Environment
  - Decision Transformation on the Spark Engine
  - Decision Transformation on the Databricks Spark Engine
Duplicate Record Exception Transformation
- Duplicate Record Exception Transformation Overview
- Duplicate Record Exception Process Flow
- Duplicate Record Exceptions
- Duplicate Record Exception Configuration View
  - Generating a Duplicate Records Table
- Ports
- Duplicate Record Exception Transformation Advanced Properties
- Duplicate Record Exception Mapping Example
- Creating a Duplicate Record Exception Transformation
Dynamic Lookup Cache
- Dynamic Lookup Cache Overview
- Dynamic Lookup Properties
- Lookup Transformation Values
- Dynamic Lookup Cache Updates
  - Insert Else Update
  - Update Else Insert
- Mappings with Dynamic Lookups
- Conditional Dynamic Cache Updates
  - Session Processing
  - Configuring a Conditional Dynamic Cache Lookup
- Dynamic Cache Update with Expression Results
- Synchronizing Cache with the Lookup Source
  - NewLookupRow
  - Configuring Dynamic Cache Synchronization
- Dynamic Lookup Cache Example
- Rules and Guidelines for Dynamic Lookup Caches
Expression Transformation
- Expression Transformation Overview
- Expression Transformation Components
- Configuring Ports
  - Calculating Values
- Creating an Expression Transformation
External Procedure Transformation
- External Procedure Transformation Overview
- Configuring External Procedure Transformation Properties
- Developing COM Procedures
- Developing Informatica External Procedures
- Distributing External Procedures
  - Distributing COM Procedures
    - Distributing a COM Visual Basic Procedure
    - Distributing a COM Visual Basic Procedure Manually
  - Distributing Informatica Modules
- Development Notes
- Service Process Variables in Initialization Properties
- External Procedure Interfaces
Filter Transformation
- Filter Transformation Overview
- Filter Transformation Components
  - Configuring Filter Transformation Ports
- Filter Condition
  - Filtering Rows with Null Values
- Steps to Create a Filter Transformation
- Tips for Filter Transformations
HTTP Transformation
- HTTP Transformation Overview
  - Authentication
  - Connecting to the HTTP Server
- Creating an HTTP Transformation
- Configuring the Properties Tab
- Configuring the HTTP Tab
- Examples
Identity Resolution Transformation
- Identity Resolution Transformation Overview
- Create and Configure the Transformation
- Identity Resolution Transformation Tabs
- Groups and Ports
  - Input Groups and Ports
  - Output Groups and Ports
Java Transformation
- Java Transformation Overview
- Using the Java Code Tab
- Configuring Ports
  - Creating Groups and Ports
  - Setting Default Port Values
    - Input and Output Ports
    - Input/Output Ports
- Configuring Java Transformation Properties
  - Working with Transaction Control
    - Transformation Scope
    - Generate Transaction
  - Setting the Update Strategy
- Developing Java Code
- Configuring Java Transformation Settings
- Compiling a Java Transformation
- Fixing Compilation Errors
  - Locating the Source of Compilation Errors
    - Locating Errors in the Code Entry Tabs
    - Locating Errors in the Full Code Window
  - Identifying the Source of Compilation Errors
    - User Code Errors
    - Non-User Code Errors
Java Transformation API Reference
- Java Transformation API Methods Overview
- commit
- defineJExpression
- failSession
- generateRow
- getInRowType
- getMetadata
- incrementErrorCount
- invokeJExpression
- isNull
- logError
- logInfo
- resetNotification
- rollBack
- setNull
- setOutRowType
- storeMetadata
Java Expressions
- Java Expressions Overview
  - Expression Function Types
- Using the Define ExpressionDefine Function Dialog Box to Define an Expression
- Working with the Simple Interface
  - invokeJExpression
  - Simple Interface Example
- Working with the Advanced Interface
- JExpression Class API Reference
Java Transformation Example
- Java Transformation Example Overview
- Step 1. Import the Mapping
- Step 2. Create Transformation and Configure Ports
- Step 3. Enter Java Code
- Import Packages Tab
- Helper Code Tab
- On Input Row Tab
- Step 4. Compile the Java Code
- Step 5. Create a Session and Workflow
  - Sample Data
Joiner Transformation
- Joiner Transformation Overview
  - Working with the Joiner Transformation
- Joiner Transformation Properties
- Defining a Join Condition
- Defining the Join Type
- Using Sorted Input
- Joining Data from a Single Source
- Blocking the Source Pipelines
  - Unsorted Joiner Transformation
  - Sorted Joiner Transformation
    - Caching Master Rows
- Working with Transactions
- Creating a Joiner Transformation
- Tips for Joiner Transformations
Key Generator Transformation
- Key Generator Transformation Overview
- Soundex Strategy
  - Soundex Strategy Properties
- String Strategy
  - String Strategy Properties
- NYSIIS Strategy
- Key Generator Output Ports
- Configuring a Grouping Strategy
- Key Creation Properties
- Key Generator Transformation Advanced Properties
- Key Generator Transformation in a Non-native Environment
Labeler Transformation
- Labeler Transformation Overview
- When to Use a Labeler Transformation
- Reference Data Use in the Labeler Transformation
- Labeler Transformation Strategies
  - Character Labeling Operations
  - Token Labeling Operations
- Labeler Transformation Ports
- Character Labeling Properties
- Token Labeling Properties
- Configuring a Character Labeling Strategy
- Configuring a Token Labeling Strategy
- Labeler Transformation Advanced Properties
- Labeler Transformation in a Non-native Environment
Lookup Transformation
- Lookup Transformation Overview
- Lookup Source Types
- Connected and Unconnected Lookups
  - Connected Lookups
  - Unconnected Lookups
- Lookup Components
- Lookup Properties
  - Configuring Lookup Properties in a Session
- Lookup Query
- Lookup Condition
- Lookup Caches
- Return Multiple Rows
  - Rules and Guidelines for Returning Multiple Rows
- Configuring Unconnected Lookup Transformations
- Database Deadlock Resilience
- Creating a Lookup Transformation
  - Creating a Reusable Pipeline Lookup Transformation
  - Creating a Non-Reusable Pipeline Lookup Transformation
- Tips for Lookup Transformations
Lookup Caches
- Lookup Caches Overview
  - Cache Comparison
- Building Connected Lookup Caches
  - Sequential Caches
  - Concurrent Caches
- Using a Persistent Lookup Cache
- Working with an Uncached Lookup or Static Cache
- Sharing the Lookup Cache
  - Sharing an Unnamed Lookup Cache
    - Guidelines for Sharing an Unnamed Lookup Cache
  - Sharing a Named Lookup Cache
    - Guidelines for Sharing a Named Lookup Cache
- Tips for Lookup Caches
Match Transformation
- Match Transformation Overview
- Match Analysis
- Match Score Calculations
- Master Data Analysis
  - Mapping Reuse
- Identity Match Analysis and Persistent Index Data
  - Rules and Guidelines for Persistent Index Data
- Match Mapping Performance
  - Viewing Match Cluster Analysis Data
  - Viewing Match Performance Analysis Data
- Match Performance in Identity Analysis
  - Creating a Data Store for Identity Index Data
  - Using the Index Data Store in Single-Source Analysis
- Match Transformation Views
- Match Transformation Ports
- Match Mapplets
  - Creating a Match Mapplet
  - Using a Match Mapplet
- Configuring a Match Analysis Operation
- Match Transformation in a Non-native Environment
Match Transformations in Field Analysis
- Field Match Analysis
- Process Flow for Field Match Analysis
- Field Match Type Options
- Field Match Strategies
  - Field Match Algorithms
  - Field Match Strategy Properties
- Field Match Output Options
  - Match Output Types
  - Match Output Properties
- Field Match Advanced Properties
- Field Match Analysis Example
Match Transformations in Identity Analysis
- Identity Match Analysis
- Process Flow for Identity Match Analysis
- Identity Match Type Properties
  - Index Directory and Cache Directory Properties
  - Persistence Method Parameters
- Identity Match Strategies
  - Identity Match Algorithms
  - Identity Match Strategy Properties
- Identity Match Output Options
  - Match Output Types
  - Match Output Properties
    - Match Property Configuration
    - Output Property Configuration
- Identity Match Advanced Properties
- Persistent Index Case Study
- Identity Match Analysis Example
Merge Transformation
- Merge Transformation Overview
- Configuring a Merge Strategy
- Merge Transformation Advanced Properties
- Merge Transformation in a Non-native Environment
Normalizer Transformation
- Normalizer Transformation Overview
- Normalizer Transformation Components
- Normalizer Transformation Generated Keys
  - Storing Generated Key Values
  - Changing the Generated Key Values
- VSAM Normalizer Transformation
- Pipeline Normalizer Transformation
- Using a Normalizer Transformation in a Mapping
  - Generating Key Values
- Troubleshooting Normalizer Transformations
Parser Transformation
- Parser Transformation Overview
- Parser Transformation Modes
- When to Use a Parser Transformation
- Reference Data Use in the Parser Transformation
- Token Parsing Operations
- Token Parsing Ports
- Token Parsing Properties
- Pattern-Based Parsing Mode
  - Pattern-Based Parsing Ports
- Configuring a Token Parsing Strategy
- Configuring a Pattern Parsing Strategy
- Parser Transformation Advanced Properties
- Parser Transformation in a Non-native Environment
Rank Transformation
- Rank Transformation Overview
- Ports in a Rank Transformation
  - Rank Index
- Defining Groups
- Creating a Rank Transformation
Router Transformation
- Router Transformation Overview
- Working with Groups
- Working with Ports
- Connecting Router Transformations in a Mapping
- Creating a Router Transformation
Sequence Generator Transformation
- Sequence Generator Transformation Overview
- Sequence Generator Ports
- Sequence Generator Transformation Properties
- Sequence Generator Advanced Properties
  - Reset
  - Maintain Row Order
- Sequence Data Object
  - Creating a Sequence Data Object
- Creating a Sequence Generator Transformation
- Creating a Sequence Generator Transformation
- Frequently Asked Questions
Sorter Transformation
- Sorter Transformation Overview
- Sorting Data
- Sorter Transformation Properties
- Creating a Sorter Transformation
Source Qualifier Transformation
- Source Qualifier Transformation Overview
- Source Qualifier Transformation Properties
- Default Query
  - Viewing the Default Query
  - Overriding the Default Query
- Joining Source Data
- Adding an SQL Query
- Entering a User-Defined Join
- Outer Join Support
- Entering a Source Filter
- Using Sorted Ports
- Select Distinct
  - Overriding Select Distinct in the Session
- Adding Pre- and Post-Session SQL Commands
- Creating a Source Qualifier Transformation
  - Creating a Source Qualifier Transformation Manually
  - Configuring Source Qualifier Transformation Options
- Troubleshooting Source Qualifier Transformations
SQL Transformation
- SQL Transformation Overview
- Script Mode
  - Example
  - Rules and Guidelines for Script Mode
- Query Mode
- Connecting to Databases
- Session Processing
- Input Row to Output Row Cardinality
- SQL Transformation Properties
- SQL Statements
- Creating an SQL Transformation
Using the SQL Transformation in a Mapping
- SQL Transformation Example Overview
- Dynamic Update Example
- Dynamic Connection Example
Stored Procedure Transformation
- Stored Procedure Transformation Overview
- Using a Stored Procedure in a Mapping
- Writing a Stored Procedure
  - Sample Stored Procedure
- Creating a Stored Procedure Transformation
- Configuring a Connected Transformation
- Configuring an Unconnected Transformation
  - Calling a Stored Procedure From an Expression
  - Calling a Pre- or Post-Session Stored Procedure
- Error Handling
- Stored Procedure Options
- Expression Rules
- Tips for Stored Procedure Transformations
- Troubleshooting Stored Procedure Transformations
Standardizer Transformation
- Standardizer Transformation Overview
- Standardization Strategies
- Standardization Properties
- Configuring a Standardization Strategy
- Standardizer Transformation Advanced Properties
- Standardizer Transformation in a Non-native Environment
Transaction Control Transformation
- Transaction Control Transformation Overview
- Transaction Control Transformation Properties
  - Properties Tab
  - Example
- Using Transaction Control Transformations in Mappings
  - Sample Transaction Control Mappings with Multiple Targets
- Mapping Guidelines and Validation
- Creating a Transaction Control Transformation
Union Transformation
- Union Transformation Overview
  - Rules and Guidelines for Union Transformations
  - Union Transformation Components
- Working with Groups and Ports
- Creating a Union Transformation
- Using a Union Transformation in a Mapping
Unstructured Data Transformation
- Unstructured Data Transformation Overview
- Configuring the Unstructured Data Option
  - Configuring the Data Transformation Repository Directory
- Data Transformation Service Types
- Unstructured Data Transformation Components
- Unstructured Data Transformation Ports
- Unstructured Data Transformation Service Names
- Relational Hierarchies
  - Exporting the Hierarchy Schema
- Mappings
- Creating an Unstructured Data Transformation
Update Strategy Transformation
- Update Strategy Transformation Overview
  - Setting the Update Strategy
- Flagging Rows Within a Mapping
- Setting the Update Strategy for a Session
  - Specifying an Operation for All Rows
  - Specifying Operations for Individual Target Tables
- Update Strategy Checklist
Weighted Average Transformation
- Weighted Average Transformation Overview
- Configuring a Weighted Average Transformation
- Weighted Match Scores Example
- Weighted Average Transformation Advanced Properties
- Weighted Average Transformation in a Non-native Environment
XML Transformations
- XML Source Qualifier Transformation
- XML Parser Transformation
- XML Generator Transformation

Transformation Guide

Back Next

Maximum Output Row Count

You can limit the number of rows the SQL transformation returns for SELECT queries. Configure the Max Output Row Count property to limit number of rows. When a query contains multiple SELECT statements, the SQL transformation limits total rows from all the SELECT statements.

For example, you set Max Output Row Count to 100. The query contains two SELECT statements:

SELECT * FROM table1; SELECT * FROM table2;

If the first SELECT statement returns 200 rows, and the second SELECT statement returns 50 rows, the SQL transformation returns 100 rows from the first SELECT statement. It returns no rows from the second statement.

Rename Saved Search

Table of Contents

Transformation Guide

Transformation Guide

Maximum Output Row Count

Maximum Output Row Count