CODEWITHSUNDEEP
expressiondecodeinformaticaiif
Chinmay Deshpande
Chinmay Deshpande

Reputation: 1

Informatica Expression Transformation IIF to DECODE function

I have following table where in I need to check 6 combinations for the 4 columns and return the unique code assigned to it. Attached is the image and below is the full text.

UNIQUE_CODE,    TYPE,   FLAG,   VALUES, RETURN VALUE 
40031,  F   ,P, ANY ,44570
40031,  V   ,P, Z   ,44572
40031,  V   ,P, EXCEPT Z    ,44574
10110,  F   ,R, ANY ,44571
10110,  V   ,R, Z   ,44573
10110,  V   ,R, EXCEPT Z,   44575

I have achieved this using nested IIF within expression transformation's variable port as per following logic : 

IIF ( UNIQUE_NUMBER = 40031 AND FLAG ='P' ,
IIF( TYPE ='F' ,44070, 
IIF (TYPE = 'V' , 
DECODE (TRUE,IN (VALUES ,'Z'),44072,44074))),
IIF ( UNIQUE_NUMBER = 10110 AND FLAG ='R' ,
IIF( TYPE ='F' ,44071, 
IIF (TYPE = 'V' , 
DECODE (TRUE,IN (VALUES,'Z'), 44073,44075)))))

However now want to achieve the same through DECODE , as I am told that nested IIF expression might effect the performance.

However I am afraid that DECODE might exit as soon as it finds first condition to be true.

Any suggestions ? I think the same can be achieved using nested DECODE however it will be too complex.

Upvotes: 0

Views: 7549

Answers (1)

Maciejg
Maciejg

Reputation: 3353

DECODE is easier to read then nested IIF. It may or may not be faster, that depends. Anyway you can build it using the following pattern:

DECODE (True,
condition, value,
default)

Which in your case should go like this (please double-check):

DECODE(True,
UNIQUE_NUMBER = 40031 AND FLAG ='P' AND TYPE ='F', 44070,
UNIQUE_NUMBER = 40031 AND FLAG ='P' AND TYPE ='V' AND IN(VALUES ,'Z'),44072,
UNIQUE_NUMBER = 40031 AND FLAG ='P' AND TYPE ='V' AND NOT IN(VALUES ,'Z'),44074,
UNIQUE_NUMBER = 10110 AND FLAG ='R' AND TYPE ='F', 44071,
UNIQUE_NUMBER = 10110 AND FLAG ='R' AND TYPE ='V', 44071 AND IN(VALUES,'Z'), 44073,
UNIQUE_NUMBER = 10110 AND FLAG ='R' AND TYPE ='V', 44071 AND NOT IN(VALUES,'Z'), 44075
)

Upvotes: 1

Related Questions