Subject Re: [firebird-support] IIF bug?
Author Mark Rotteveel
On Wed, 23 Jan 2013 04:49:17 -0800 (PST), "Mr. John" <mr_johnmr@...>
> Hi !
> I'm using FB 2.5.2 on XP
> I've discovered a strange behavior for IIF function: 
> SELECT '11'||(IIF(1>2,'00000000000000',''))||'.22'  FROM rdb$database
> as it can see condition is always false,IIF should result an empty
> but the result is not 
>                    11.22
> ,the result is 11              .22
> why?

The return type of IIF in this case is CHAR(14) (the length of the longest
argument). String literals in Firebird are CHAR (and not VARCHAR). The CHAR
datatype is always padded with spaces upto its declared length, so '' is
also CHAR(14) and actually returned as ' ' (14 spaces). For
the behavior that you want, you need to use
TRIM(IIF(1>2,'00000000000000','')), or TRIM(IIF(1>2,CAST('00000000000000'
AS VARCHAR(20)),'')) to force IIF to have a returntype of VARCHAR.