Table of Contents

Search

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

Transformations

Transformations

Custom queries

Custom queries

You can create a custom query for database lookups. You might create a custom query to reduce the number of columns to query.
To use a custom query as a lookup source, select
Query
as the source type, and then define the query. When you define the query, enter an SQL SELECT statement to select the source columns that you want to use.
Data Integration
uses the SQL statement to retrieve source column information.
When you use a custom query in a lookup transformation, use the following format for the SQL statement:
  • For a relational database connection, use an alias for each column in the SQL statement, for example:
    SELECT COL1 AS COL1, COL2 AS COL2, COL3 AS COL3 from TABLE_NAME
  • For other types of database connections, use SQL that is valid for the source database. You can use database-specific functions in the query.
To use a custom query as a lookup source, you must enable lookup caching.
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, which is available in the following connectors:
  • JDBC V2 Connector
  • PostgreSQL Connector
  • MongoDB V2 Connector
  • Couchbase Connector
When you use the
Retain existing field metadata
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.

0 COMMENTS

We’d like to hear from you!