Informatica – Do not use the ‘!=’ in expression

July 13, 2014

This was something i had faced couple of times in my development adding a logic to my mapping and i have always done the same mistake. Now this is why i have decided to share this with other developers, avoid using the ‘!='(Not Equal) operator in your expression as much as you can.

Let me quickly put down the real issue i had faced with my experience getting to use ‘!=’ operator and this was some real interesting learning experience getting to understand what and how really your data is being processed by the Informatica’s Integration Service.

I had a requirement, where in i had to populate 2 targets satisfying 2 different logic based on a column. So as an illustration i am taking the below records,

SID_No  Group_Nm Amount
1 A    1000
2 B  1500
3 A  2000
4 NULL 1200
5 NULL 1000
6 C 2000
7 C 3000

Now my need for the day is to populate to 2 targets as,

Target_A -> where Group_Nm=’A’
Target_B -> where Group_Nm !=’A’ and Group_Nm !=’B’ (expecting all other records other than those in Target_A and are not

So as the above logic says, i had straight away gone to put down my expressions as below, which would take all Group-A as Flag=1 and remaining records with Group name != ‘B’ would be expected to go to Flag=2.

IIF(Group_Nm = ‘A’,1,
IIF(Group_Nm != ‘B’,2,0))

I am expecting
1,3 to be tagged as Flag=1
4,5,6,7 to be tagged as Flag=2

Now what have i got in my target is,
1,3 tagged as Flag=1 flows to Target_A
6,7 tagged as Flag=2 flows to Target_B.

So it was quite clear that the issue was with the records having Group_Nm as NULL. That clearly gave a clue that the expression was not handling the NULL values as expected. So the root caus ebeing the second IF condition checking for Group_Nm != ‘B’ and hence scanning the left out records 2,4,5,6,7 it had got 6,7 satisfying the condition so what really happened to 4,5 was ‘as it happened to have NULL values these were ignored for a check and so were dropped out’ and so i had them flagged as ‘0’.

So quickly understanding all i had a change in the expression was to keep it as below,

IIF(Group_Nm = ‘A’,1,
IIF(Group_Nm = ‘B’,0,2))

So finally i had my results as expected with the right records flowing to the respective targets. Hope this made so good understanding to you. Please drop in your questions or suggestions if any.


2 Responses to “Informatica – Do not use the ‘!=’ in expression”

  1. dude Says:

    Checks on columns that may be null are always special. From theory we know that NULL != NULL. What you could also do is create a variable port and mask the nulls: IIF(ISNULL(Group_Nm),’ItIsNull’,Group_Nm)
    Then instead, use this variable throughout the rest of your expressions.

  2. kanz Says:

    What version you are using? I also faced the same problem in V 9.6. Is it a bug & any fix for this ? [i know the workaround though]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: