Sunday, February 19, 2012

Beware of case statement in Informix

I just realize there was a difference between Informix and Oracle when using case statement. Let's take a look at the sample code below:

      when Col1 = 'A' then "Blank"
      when Col1 = 'B' then "Portfolio"
   end Channel
from table_A;

Need to be aware that the length of the Channel column will follow the value with longest length stored inside the particular column. Thus those values with shorter length will append with white-space at the end of the string.

In this case, the length of Portfolio is 9, then the length of Blank is 5, as a result, the actual string for blank is [Blank    ]. Do take note that if you want to do string comparison, you have to trim the string from Channel in order to get the actual string. If not, the result will always return false.

Good luck.

No comments: