Informatica decode returns a disallowed value

Question

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?

+1 for using [my favourite DECODE(TRUE, ...) trick](http://stackoverflow.com/a/9979906/95)! — Marek Grzenkowicz, Sep 28 '13 at 11:18

score 0 · Accepted Answer · answered Sep 26 '13 at 06:49

0

The expression looks fine to me. 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.

answered Sep 26 '13 at 06:49

Marek Grzenkowicz

17,024
9
81
111

1

I finally found the problem. The field that uses it was cast correctly (as a string) but waaaay downstream, later in the mapping, the value was being cast as an integer. The cast propagated back through the mapping to my original decode and was fouling everything up. Pretty much what you were thinking – bigbenbt Oct 02 '13 at 17:55

Informatica decode returns a disallowed value

1 Answers1