Table of Contents

Search

  1. Preface
  2. Transformations
  3. Source transformation
  4. Target transformation
  5. Aggregator transformation
  6. Cleanse transformation
  7. Data Masking transformation
  8. Deduplicate transformation
  9. Expression transformation
  10. Filter transformation
  11. Hierarchy Builder transformation
  12. Hierarchy Parser transformation
  13. Hierarchy Processor transformation
  14. Input transformation
  15. Java transformation
  16. Java transformation API reference
  17. Joiner transformation
  18. Labeler transformation
  19. Lookup transformation
  20. Mapplet transformation
  21. Normalizer transformation
  22. Output transformation
  23. Parse transformation
  24. Python transformation
  25. Rank transformation
  26. Router transformation
  27. Rule Specification transformation
  28. Sequence Generator transformation
  29. Sorter transformation
  30. SQL transformation
  31. Structure Parser transformation
  32. Transaction Control transformation
  33. Union transformation
  34. Velocity transformation
  35. Verifier transformation
  36. Web Services transformation

Transformations

Transformations

Custom queries

Custom queries

Create a custom query when you want to use a database source that you cannot configure using the single- or multiple-object source options. You might create a custom query to perform a complicated join of multiple tables or to reduce the number of fields that enter the data flow in a very large source.
To use a custom query as a source, select
Query
as the source type, and then click
Define Query
. When you define the query, use SQL that is valid for the source database. You can use database-specific functions in the query.
You can also use a custom query as a lookup source. For information about using a custom query in a Lookup transformation, see Custom queries.
When you create a custom query, enter an SQL SELECT statement to select the source columns you want to use.
Data Integration
uses the SQL statement to retrieve source column information. You can edit the datatype, precision, or scale of each column before you save the custom query.
For example, you might create a custom query based on a TRANSACTIONS table that includes transactions from 2016 with the following SQL statement:
SELECT TRANSACTION_ID, TRANSACTION_TOTAL, TRANSACTION_TIMESTAMP from dbo.TRANSACTIONS WHERE TRANSACTION_TIMESTAMP>’0:0:0:0 01/01/2016’
Data Integration
ensures that custom query column names are unique. If an SQL statement returns a duplicate column name,
Data Integration
adds a number to the duplicate column name as follows:
<column_name><number>
When you change a custom query in a saved mapping, at design time
Data Integration
replaces the field metadata with metadata using the revised query. Typically, this is the desired behavior. However, if the mapping uses a relational source and you want to retain the original metadata, use the
Retain existing field metadata
option. When you use this option,
Data Integration
doesn't refresh the field metadata during design time.
Data Integration
maps the existing fields with the fields from the revised query at run time. Fields that can't be mapped will cause run time failure.
Test the SQL statement you want to use on the source database before you create a custom query.
Data Integration
does not display specific error messages for invalid SQL statements.