Jeff's Cluttered Mind

Session Override Properties for ODBC

When connecting to DB2 iSeries and the Session reads some data but does not complete. A Function Sequence error is given, use these overrides.

OptimizeODBCWrite=No;BlkDtmNumRowsMax=1;OptimizeODBCRead=No;

Informatica

Remove number sign (#) from Informatica Flat File Target Header Row, add this Custom Property on the Session Config Object tab, (in the Advanced section):

RemoveOutputHeaderHash = Yes

Informatica Output & Numeric Excel Columns

Situation: Numeric flat file columns in Excel are treated as a numeric. Excel treats the column as scientific notation. To have the data in Excel formated with leading zeros CHR(160) || RTRIM(LTRIM(PLANKEY)) . The CHR(160) represents a non-breaking space in ASCII.

Removing Non-breaking space from a String

REPLACECHR( 0, Port_Name, CHR(160), NULL )

REPLACECHR( 0, Port_Name, CHR(160), '' )

Searching Strings for pattern, similar to SQL LIKE function

INSTR returns the starting position of the search value, zero if unsuccessful
SIGN returns Returns whether a numeric value is positive, negative, or 0. I found this tip on the ITToolbox

SIGN(INSTR(MAPPING_NAME, 'Extract'))

1 is returned if pattern found
0 is returned if pattern not found

Target table PRE SQL to delete current date rows. Add this to the session, target pre-sql option

DELETE FROM schema.table WHERE RUN_DATE = TIMESTAMP(CURRENT DATE, '00:00:00')

Parse Characters From Within A String, leaving only the Numbers

Sample input: 780 (Case Pack)
Sample output: 780

REPLACECHR(0, Port_Name, REPLACECHR(0, Port_Name, '0123456789', ''), '')

I found this tip on the Informatica Community Forum

This Excel function will format an SAP material number

To get the full, zero padded material number, use:

=REPT("0",18-LEN(B1))&B1

How to remove ASCII New Line-CHR(10) and Carriage Returns-CHR(13) from a String

REPLACECHR( 0, REPLACECHR( 0, Port_Name, CHR(10), '  '), CHR(13), '  ')

Complicated way to convert string column to numeric

IIF( SIGN(INSTR(LB, '-')) = 1, TO_DECIMAL(LB) * -1, TO_DECIMAL(LB) )

Remove old integration service from db so you don't see it in WF Manager

DELETE FROM OPB_SERVER_INFO WHERE SERVER_ID = ?

Split Name into First, Middle and Last

Found this on the Marketplace, Split First and Last Name using RegE

How to compare the current value of a port with its previous value

Name	Type	Expression	Comment
V_LASTVALUE	Variable	I_VALUE	Stores value of the GROUP_ID from previous row. This port is set first.
I_VALUE	Variable	GROUP_ID	Stores value of GROUP_ID from current row
GROUP_ID	Input/Output		Input value to be stored.
O_VALUE	Output	IIF(V_LASTVALUE=I_VALUE,'1','0')	Output value to compare these value of GROUP_IDfrom previous and current row