top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Informatica decode returns a disallowed value

0 votes
477 views

I have the following decode statement in an expression transformation in informatica:

(DECODE
(TRUE
, OPERATION1='I' and NOT ISNULL(a_new),'YES'
, OPERATION1='D'and NOT ISNULL(a_old),'YES'
, OPERATION1='U'and ( (
     (a<>b) 
  or (ISNULL(a_new) and NOT ISNULL(a_old)) 
  or (NOT ISNULL(a_new) and ISNULL(a_old))
      ) 
),'YES','NO CHANGE')
)

Where a_new and a_old are both integers (when they appear at all).

Here's the weird part:

This decode statement, when run, constantly returns a value of 0 (zero). I can't for the life of me figure out why. When I run this in the debugger and evaluate the decode (right click on the expression, click on 'evaluate expression', paste the decode in) it evaluates to either YES or NO CHANGE correctly. But when it actually runs, both in the debugger and in production, it still evaluates to zero. Does anybody know why?

posted Sep 16, 2014 by Sunil

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

You should check the definition of the field that uses it. Either it is declared as integer (or some other numeric data type) or it has a default value assigned.

answer Sep 18, 2014 by Shweta Singh
Similar Questions
0 votes

Could anyone help me with writing case statements in Informatica PowerCenter Designer? I am fairly new to Informatica, and based on my limited experience I feel case statements aren't supported. There is a decode function with similar functionality, but I am unable to find any good examples on the syntax.

I would really appreciate if anyone could give me some specific examples on how to use case statements/decode function in Informatica.

Thanks much for your help

0 votes

I'm new to Informatica and like to ask a question regarding infa ports. I found an example which show how to create an update flag in expression transform for updating data. The code in v_UPDATE_FLAG port looks like this:

IIF(NOT ISNULL(PREV_ITEM_KEY)
AND
(
DECODE(BONUS_FLAG,PREV_BONUS_FLAG,1,0) = 0 
OR
DECODE(DISCOUNT,PREV_DISCOUNT,1,0) = 0 
OR
DECODE(PRICE,PREV_PRICE,1,0) = 0 
),'TRUE','FALSE')

Can you explain what this does? What the DECODE does here??

DECODE(BONUS_FLAG,PREV_BONUS_FLAG,1,0) = 0 
+1 vote

Could anyone help me with writing case statements in Informatica PowerCenter Designer? I am fairly new to Informatica, and based on my limited experience I feel case statements aren't supported. There is a decode function with similar functionality, but I am unable to find any good examples on the syntax.

I would really appreciate if anyone could give me some specific examples on how to use case statements/decode function in Informatica.

Thanks much for your help!

+1 vote

Could anyone help me with writing case statements in Informatica PowerCenter Designer? What I feel is that case statements aren't supported. There is a decode function with similar functionality, but I am unable to find any good examples on the syntax.

I would really appreciate if anyone could give me some specific examples on how to use case statements/decode function in Informatica.

...