Preface
Introduction to Transformations
- Introduction to Transformations Overview
- Transformations in the Native and Non-native Environments
- Handling Transformation Data Types
- Developing a Transformation
- Multi-Group Transformations
  - Rules and Guidelines for Multi-Group Transformations
- Expressions in Transformations
- Local Variables
- Default Values for Ports
- Tracing Levels
- Reusable Transformations
- Non-Reusable Transformations
  - Editor Views for a Non-Reusable Transformation
- Creating a Transformation
Transformation Ports
- Transformation Ports Overview
- Create Ports
- Configure Ports
- Linking Ports
- Propagating Port Attributes
- Copying Ports from Excel
Transformation Caches
- Transformation Caches Overview
- Cache Types
- Cache Files
  - Cache File Directory
- Cache Size
  - Auto Cache Size
    - Maximum Memory for Auto Cache Size
  - Specific Cache Size
- Cache Size Increase by the Data Integration Service
- Cache Size for Partitioned Caches
- Cache Size Optimization
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
- Aggregator Transformations in Dynamic Mappings
- Developing an Aggregator Transformation
- Aggregator Transformation Ports
- Aggregate Expressions
- Group By Ports
- Aggregator Caches
- Sorted Input for an Aggregator Transformation
  - Sorted Input Conditions
  - Sorting Data in an Aggregator Transformation
- Aggregator Transformation Advanced Properties
- Creating a Reusable Aggregator Transformation
- Creating a Non-Reusable Aggregator Transformation
- Tips for Aggregator Transformations
- Troubleshooting Aggregator Transformations
- Aggregator Transformation in a Non-native Environment
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
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
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 Overview
- Masking Techniques
- Masking Rules
- Special Mask Formats
- Default Value File
- Data Masking Transformation Configuration
- Data Masking Transformation Runtime Properties
- Data Masking Example
- Data Masking Transformation Advanced Properties
- Data Masking Transformation in a Non-native Environment
  - Data Masking Transformation on the Blaze Engine
  - Data Masking Transformation on the Spark Engine
    - Data Masking Transformation in a Streaming Mapping
Data Processor Transformation
- Data Processor Transformation Overview
- Data Processor Transformation Views
- Data Processor Transformation Ports
- Startup Component
- References
- Data Processor Transformation Settings
- Events
  - Event Types
  - Data Processor Events View
- Logs
- Data Processor Transformation Development
- Data Processor Transformation Export and Import
- Data Processor Transformation Validation
  - Using a Speed-enhanced Data Transformation Engine for VRL Validations
- Data Processor Transformation in a Non-native Environment
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
Expression Transformation
- Expression Transformation Overview
- Expression Transformation Ports
- Test Expressions
  - Date Format Strings for Sample Data
  - Testing an Expression
- Port Selectors
- Windowing
  - Windowing Configuration
- Dynamic Expressions
  - Output Port Settings
  - Creating a Dynamic Expression
- Flatten a Dynamic Struct
- Expression Transformation Advanced Properties
- Expression Transformation in a Non-native Environment
Filter Transformation
- Filter Transformation Overview
- Filter Transformations in Dynamic Mappings
- Filter Condition
  - Parameterize the Filter Condition
  - Filtering Rows with Null Values
- Filter Transformation Advanced Properties
- Filter Transformation Performance Tips
- Filter Transformation in a Non-native Environment
  - Filter Transformation on the Blaze Engine
Hierarchical to Relational Transformation
- Hierarchical to Relational Transformation Overview
- Example - Hierarchical to Relational Transformation
- Output Relational Ports and the Overview View
- Hierarchical to Relational Transformation Ports
- Schema References
- Port Configuration
- Hierarchical to Relational Transformation Development
Java Transformation
- Java Transformation Overview
- Designing a Java Transformation
- Java Transformation Ports
  - Creating Ports
  - Setting Default Port Values
    - Input and Output Ports
- Java Transformation Advanced Properties
  - Configuring the Classpath for the Developer Tool Client
  - Configuring the Classpath for the Data Integration Service
- Developing Java Code
- Java Transformation Java Properties
- Filter Optimization with the Java Transformation
  - Early Selection Optimization with the Java Transformation
  - Push-Into Optimization with the Java Transformation
- Creating a Java Transformation
  - Creating a Reusable Java Transformation
  - Creating a Non-Reusable Java Transformation
- Compiling a Java Transformation
- Troubleshooting a Java Transformation
  - Finding the Source of Compilation Errors
    - Finding an Error on a Code Entry Tab or the Full Code Tab
  - Identifying the Source of Compilation Errors
    - User Code Errors
    - Non-User Code Errors
- Converting to Struct Data Example
- Java Transformation in a Non-native Environment
  - Java Transformation on the Blaze Engine
  - Java Transformation on the Spark Engine
    - Java Transformation in a Streaming Mapping
Java Transformation API Reference
- Java Transformation API Methods Overview
- defineJExpression
- failSession
- generateRow
- getInRowType
- getMetadata
- incrementErrorCount
- invokeJExpression
- isNull
- logError
- logInfo
- resetNotification
- setNull
- storeMetadata
Java Expressions
- Java Expressions Overview
  - Expression Function Types
- Using the Define 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
Joiner Transformation
- Joiner Transformation Overview
- Joiner Transformation Advanced Properties
- Joiner Caches
- Joiner Transformation Ports
- Joiner Transformations in Dynamic Mappings
- Port Selectors in a Joiner Transformation
  - Selection Rules
  - Creating a Port Selector
- Defining a Join Condition
- Join Types
- Sorted Input for a Joiner Transformation
- Joining Data from the Same Source
- Blocking the Source Pipelines
  - Unsorted Joiner Transformation
  - Sorted Joiner Transformation
    - Caching Master Rows
- Joiner Transformation Performance Tips
- Rules and Guidelines for a Joiner Transformation
- Joiner Transformation in a Non-native Environment
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
- Connected and Unconnected Lookups
  - Connected Lookups
  - Unconnected Lookups
- Developing a Lookup Transformation
- Lookup Query
- Lookup Source Filter
  - Filtering Source Rows in a Lookup
- Lookup Condition
  - Configure the Lookup Condition
  - Rules and Guidelines for Lookup Transformation Conditions
- Lookup Cache
- Query Properties
- Lookup Transformations in Dynamic Mappings
- Define Dynamic Ports
- Change the Lookup Source
- Port Selectors
- Run-time Properties
- Advanced Properties
- Creating a Reusable Lookup Transformation
- Creating a Non-Reusable Lookup Transformation
- Creating an Unconnected Lookup Transformation
- Unconnected Lookup Example
- Lookup Transformation in a Non-native Environment
Lookup Caches
- Lookup Caches Overview
- Lookup Cache Types
- Uncached Lookup
- Static Lookup Cache
- Persistent Lookup Cache
  - Rebuilding a Persistent Lookup Cache
- Dynamic Lookup Cache
- Shared Lookup Cache
  - Rules and Guidelines for Sharing a Lookup Cache
- Cache Comparison
- Cache Partitioning for Lookups
Dynamic Lookup Cache
- Dynamic Lookup Cache Overview
- Uses for a Dynamic Lookup Cache
- Dynamic Lookup Cache Properties
- Dynamic Lookup Cache and Output Values
- Lookup Transformation Values
  - Lookup Transformation Values Example
- SQL Override and Dynamic Lookup Cache
- Mapping Configuration for a Dynamic Lookup Cache
- Conditional Dynamic Lookup Cache Updates
  - Conditional Dynamic Lookup Cache Processing
  - Configuring a Conditional Dynamic Lookup Cache
- Dynamic Cache Update with Expression Results
- Dynamic Lookup Cache Example
- Rules and Guidelines for Dynamic Lookup Caches
Macro Transformation
- Macro Transformation Overview
- Macro Instruction
- Macro Transformation Parameters
- Macro Transformations in Dynamic Mappings
- Macro Transformation Example
- Macro Transformation in a Non-native Environment
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
Normalizer Transformation
- Normalizer Transformation Overview
- Multiple-Occurring Fields
  - Generated Column ID
- Multiple-Occurring Records
- Input Hierarchy Definition
- Normalizer Transformation Output Groups and Ports
  - Create an Output Group
  - Update an Output Group
- Key Generation for Output Groups
- Normalizer Transformation Advanced Properties
  - Generate First Level Output Groups
- Creating a Normalizer Transformation
- Creating a Normalizer Transformation from an Upstream Source
- Normalizer Mapping Example
- Normalizer Transformation in a Non-native Environment
Merge Transformation
- Merge Transformation Overview
- Configuring a Merge Strategy
- Merge Transformation Advanced Properties
- Merge Transformation in a Non-native Environment
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
Python Transformation
Rank Transformation
- Rank Transformation Overview
  - Ranking String Values
  - Rank Transformation Properties
- Rank Transformations in Dynamic Mappings
- Rank Transformation Ports
  - Rank Index
- Rank Port
- Define Group By Ports
  - Group By Parameters
- Rank Caches
- Rank Transformation Advanced Properties
- Rank Transformation in a Non-native Environment
Read Transformation
- Read Transformation Overview
- Read Transformation Properties
- Synchronize Relational Data Objects
- Change the Source Data Object
  - Parameterize the Read Transformation
- Read Transformation Parameters
- Constraints
- Create a Read Transformation
  - Creating a Read Transformation in the Mapping Editor
Relational to Hierarchical Transformation
- Relational to Hierarchical Transformation Overview
- Example - Relational to Hierarchical Transformation
- Input Relational Ports and the Overview View
- Relational to Hierarchical Transformation Ports
- Schema References
- Relational to Hierarchical Transformation Development
  - Creating the Relational to Hierarchical Transformation
  - Creating the Ports
REST Web Service Consumer Transformation
- REST Web Service Consumer Transformation Overview
  - REST Web Service Consumer Transformation Process
- REST Web Service Consumer Transformation Configuration
  - Message Configuration
  - Resource Identification
- HTTP Methods
- REST Web Service Consumer Transformation Ports
- REST Web Service Consumer Transformation Input Mapping
  - Rules and Guidelines to Map Input Ports to Elements
  - Mapping Input Ports to the Method Input
- REST Web Service Consumer Transformation Output Mapping
- REST Web Service Consumer Transformation Advanced Properties
- REST Web Service Consumer Transformation Creation
  - Creating a REST Web Service Consumer Transformation
- Parsing a JSON Response Message that Contains Arrays
  - Example JSON Response Message
  - Unnamed Arrays in a Response Message
Router Transformation
- Router Transformation Overview
- Router Transformations in Dynamic Mappings
- Working with Groups
- Working with Ports
- Connecting Router Transformations in a Mapping
- Router Transformation Advanced Properties
- Router Transformation in a Non-native Environment
Sequence Generator Transformation
- Sequence Generator Transformation Overview
- Sequence Generator Ports
  - Pass-Through Ports
  - NEXTVAL Port
    - Create Keys
    - Replace Missing Values
- 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
- Frequently Asked Questions
- Sequence Generator Transformation in a Non-native Environment
  - Sequence Generator Transformation on the Blaze Engine
  - Sequence Generator Transformation on the Spark Engine
Sorter Transformation
- Sorter Transformation Overview
- Sorter Transformations in Dynamic Mappings
- Developing a Sorter Transformation
- Sorter Transformation Ports
- Sort Tab
- Configure Sort Keys
  - Parameterize the Sort Keys
- Sorter Transformation Advanced Properties
- Sorter Cache
  - Optimizing the Sorter Cache
- Creating a Sorter Transformation
  - Creating a Reusable Sorter Transformation
  - Creating a Non-Reusable Sorter Transformation
- Sorter Transformation Example
- Sorter Transformation in a Non-native Environment
SQL Transformation
- SQL Transformation Overview
- SQL Transformation Ports
- SQL Transformation Advanced Properties
- SQL Transformation Query
  - Define the SQL Query
    - Parameter Binding
    - String Substitution
- Input Row to Output Row Cardinality
- Filter Optimization with the SQL Transformation
  - Early Selection Optimization with the SQL Transformation
    - Enabling Early Selection Optimization with the SQL Transformation
  - Push-Into Optimization with the SQL Transformation
    - Push-Into Optimization with the SQL Transformation Example
    - Enabling Push-Into Optimization with the SQL Transformation
- SQL Transformation Example with an SQL Query
- Stored Procedures
- SQL Transformation Connection
  - Creating a Connection Name Parameter
- Manually Creating an SQL Transformation
- Creating an SQL Transformation from a Stored Procedure
Standardizer Transformation
- Standardizer Transformation Overview
- Standardization Strategies
- Standardization Properties
- Configuring a Standardization Strategy
- Standardizer Transformation Advanced Properties
- Standardizer Transformation in a Non-native Environment
Union Transformation
- Union Transformation Overview
- Groups and Ports
- Union Transformation Advanced Properties
- Union Transformation Processing
- Creating a Union Transformation
  - Creating a Reusable Union Transformation
  - Creating a Non-Reusable Union Transformation
- Union Transformation in a Non-native Environment
  - Union Transformation in a Streaming Mapping
  - Union Transformation on the Databricks Spark Engine
Update Strategy Transformation
- Update Strategy Transformation Overview
  - Setting the Update Strategy
- Update Strategy Transformations in Dynamic Mappings
- Flagging Rows Within a Mapping
- Specifying Update Options for Individual Targets
- Update Strategy Transformation in a Non-native Environment
Web Service Consumer Transformation
- Web Service Consumer Transformation Overview
- WSDL Selection
- Web Service Consumer Transformation Ports
  - HTTP Header Input Ports
  - Other Input Ports
- Web Service Consumer Transformation Input Mapping
- Web Service Consumer Transformation Output Mapping
- Web Service Consumer Transformation Advanced Properties
- Filter Optimizations
  - Enabling Early Selection Optimization with the Web Service Consumer Transformation
  - Push-Into Optimization with the Web Service Consumer Transformation
    - Push-Into Optimization with Web Service Consumer Transformation Example
    - Enabling Push-Into Optimization with the Web Service Consumer Transformation
- Creating a Web Service Consumer Transformation
- Web Service Consumer Transformation Example
Parsing Web Service SOAP Messages
- Parsing Web Service SOAP Message Overview
- Transformation User Interface
- Multiple-Occurring Output Configuration
- Parsing anyType Elements
- Parsing Derived Types
- Parsing QName Elements
- Parsing Substitution Groups
- Parsing XML Constructs in SOAP Messages
Generating Web Service SOAP Messages
- Generating Web Service SOAP Messages Overview
- Transformation User Interface
  - Input Ports Area
  - Operation Area
- Port and Hierarchy Level Relationships
- Keys
- Map Ports
- Pivoting Multiple-Occurring Ports
- Map Denormalized Data
- Derived Types and Element Substitution
- Generating XML Constructs in SOAP Messages
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
Window Transformation
Write Transformation
- Write Transformation Overview
- Write Transformation Properties
- Create a Write Transformation
Appendix A: Transformation Delimiters
- Transformation Delimiters Overview

Developer Transformation Guide

10.5.2
- 10.5
- 10.4.1
- 10.4.0

Back Next

Define the SQL Query

Define an SQL query that runs the same query statements for each input row. You can change the query columns or table based on input port values in the row. You can also change the values in the WHERE clause based on input port values.

To change the data values in the WHERE clause for each input row, configure parameter binding.

To change the query columns or to change the table based on input port values, use string substitution.

SQL Transformation Query

Parameter Binding

String Substitution

Download Guide

Watch

Comments

Communities

Knowledge Base

Success Portal

0 COMMENTS

We’d like to hear from you! Log in to comment.

Rename Saved Search

Table of Contents

Developer Transformation Guide

Developer Transformation Guide

Define the SQL Query

Define the SQL Query