Working with Multiple Sources

If you are importing multiple relational tables with primary-foreign key relationships from the same database server into your mappings, you can use one Source Qualifier to join them. Joining the data in a single Source Qualifier allows the Informatica server to read the data in all the tables in a single pass and helps increase session performance.

To do this, import each of the source tables into the Source Analyzer and create the primary-foreign key relationships by linking matching columns in the different tables (that is, the Alt_Customer_ID field in each table). Next, add the source definitions to your mapping and link the columns into one Source Qualifier.

When you include columns from multiple tables in one Source Qualifer, the Informatica server generates a SELECT statement for all columns used in the mapping. If you determine that you need to override the default statement, you may enter the contents of the WHERE clause that specifies the join into your custom query.

You cannot use the Source Qualifier to join tables in different databases or flat files. To join these sources, you must add a Joiner transformation to the mapping, set up the input sources, and configure the transformation with a condition and join type. The join condition contains ports from both input sources that must match in order for the server to join two rows. Depending on the type of join selected, the Joiner transformation either adds the row to the result set or discards the row.

Using Functions to Transform Data

Informatica provides over 60 SQL-type functions allowing you to change data in a mapping. In the Informatica Mapping Designer’s Expression Editor you can view a list of available transformation functions and select a function to incorporate into your Expression, Aggregator, Update Strategy, Rank, or Filter transformation. Some of the functions you may find useful are:

1. TO_CHAR(Numbers) – Converts numeric values to text strings. TO_CHAR also converts dates to strings.

   Syntax: TO_CHAR( numeric_value )

2. TO_CHAR(Dates) – Converts dates to character strings.

   Syntax: TO_CHAR( date [,format] )

3. DECODE – Searches a port for a value you specify. If the function finds the value, it returns a result value, which you define. You can build an unlimited number of searches within a DECODE function.

   Syntax: DECODE( value, first_search, first_result [, second_search, second_result]…[,default] )

   Example: DECODE(Education_in, 1, 'Some/No High School', 2, 'High School', 3, 'Some College', 4, '2 Year College', 5, '4 Year College', 6, 'Masters', 7, 'Doctoral', 8, 'Other')

   Education_in	Return Value
   2	High School
   5	4 Year College

4. IIF – Returns one of two values that you specify, based on the results of a condition.

   Syntax: IIF( condition, value1 [,value2] )

   Example: IIF(Income_in < 30000, 'Under 30K', 'Over 40K')

   Income_in	Return Value
   24000	Under 30K
   40000	Over 40K

5. SUBSTR – Returns a portion of a string. SUBSTR counts all characters, including blanks, starting at the beginning of the string.

   Syntax: SUBSTR( string, start [,length] )

   Example: SUBSTR( PHONE, 0, 3 )

   PHONE	Return Value
   809-555-3915	809
   357-687-6708	357