Substring : Right to Left

Informatica does not have this documented but you could use SUBSTR function to go from Right to Left as well. Just use a negative start.

IIF(SUBSTR(FILENM,-4)='.PDF', 'PORTABLE')

 

Regular Expressions in Informatica

Informatica supports PERL like regex syntax. So any parsing you would do in shell, can be done in Informatica as well.

REG_EXTRACT('Fiction__JK_ROWLING__HARRY_POTTER__MAX07','(\w+)(__\w+__)(\w+)(__.*)',3)

In the code above, __ are the delimiters in the string.

The regext part
\w looks for any alphanumeric character including underscore. Adding + to it makes it look for multiple alphanum characters.
() are to group the patterns
. looks for any character and adding & * makes it look for all the occurrences of any characters.
So we have four groups and the output is the 3rd group.

OUTPUT

HARRY_POTTER

Can you figure the other groups? Now can you figure how many groups we need if we just need the number of books in series or just the genre?

Router Transformation Groups

Router Transformation's Groups are not the same as CASE statement conditions or what you may also call waterfall logic.

Router transformation routes data to all the groups that evaluate to TRUE unlike the CASE statement that executes the code only for the first true condition.

Creating SQL statement for a Target Definition

I learned two things -
1. Don't blindly believe when people say it can't be done.
2. Informatica lets you create SQL statement to create target table.

I've been working to load this massive VSAM file with more than 1500 fields. Creating the source definition from the COBOL copybook was simple but creating ORACLE table manually for these many fields dampened my spirits.

I asked one of the more senior guys around here if he knew how to do it since he has been working on the same project with me. He said it wasn't possible and that I needed to do it manually.

I came back crushed and started reading Informatica Manual to delay my nemesis (so dramatic but seriously 1500 fields!).

It's then I learned that once the target definition has been created, Target Designer lets you "Generate/Execute SQL" for the target. BINGO! I was done in 2 minutes.

PS - To create target definition, I just dragged and dropped the source definition to target designer.