Table of Contents

Search

  1. Preface
  2. Working with Transformations
  3. Aggregator Transformation
  4. Custom Transformation
  5. Custom Transformation Functions
  6. Data Masking Transformation
  7. Data Masking Examples
  8. Expression Transformation
  9. External Procedure Transformation
  10. Filter Transformation
  11. HTTP Transformation
  12. Identity Resolution Transformation
  13. Java Transformation
  14. Java Transformation API Reference
  15. Java Expressions
  16. Java Transformation Example
  17. Joiner Transformation
  18. Lookup Transformation
  19. Lookup Caches
  20. Dynamic Lookup Cache
  21. Normalizer Transformation
  22. Rank Transformation
  23. Router Transformation
  24. Sequence Generator Transformation
  25. Sorter Transformation
  26. Source Qualifier Transformation
  27. SQL Transformation
  28. Using the SQL Transformation in a Mapping
  29. Stored Procedure Transformation
  30. Transaction Control Transformation
  31. Union Transformation
  32. Unstructured Data Transformation
  33. Update Strategy Transformation
  34. XML Transformations

Transformation Guide

Transformation Guide

Informatica Join Syntax

Informatica Join Syntax

When you enter join syntax, use the Informatica or database-specific join syntax. When you use the Informatica join syntax, the Integration Service translates the syntax and passes it to the source database during the session.
Always use database-specific syntax for join conditions.
When you use Informatica join syntax, enclose the entire join statement in braces ({Informatica syntax}). When you use database syntax, enter syntax supported by the source database without braces.
When using Informatica join syntax, use table names to prefix column names. For example, if you have a column named FIRST_NAME in the REG_CUSTOMER table, enter “REG_CUSTOMER.FIRST_NAME” in the join syntax. Also, when using an alias for a table name, use the alias within the Informatica join syntax to ensure the Integration Service recognizes the alias.
The following table lists the join syntax you can enter, in different locations for different Source Qualifier transformations, when you create an outer join:
Transformation
Transformation Setting
Description
Source Qualifier Transformation
User-Defined Join
Create a join override. The Integration Service appends the join override to the WHERE or FROM clause of the default query.
Source Qualifier Transformation
SQL Query
Enter join syntax immediately after the WHERE in the default query.
Application Source Qualifier Transformation
Join Override
Create a join override. The Integration Service appends the join override to the WHERE clause of the default query.
Application Source Qualifier Transformation
Extract Override
Enter join syntax immediately after the WHERE in the default query.
You can combine left outer and right outer joins with normal joins in a single source qualifier. Use multiple normal joins and multiple left outer joins.
When you combine joins, enter them in the following order:
  1. Normal
  2. Left outer
  3. Right outer
Some databases limit you to using one right outer join.

0 COMMENTS

We’d like to hear from you!