It depends on what you are using to present your data. Cognos or BO will do this type of thing directly.
If you are using Queryman or Bteq, you will have to format the string as a character to achieve this - which makes it messy if you want subtotals or totals. To make it a character:
Create volatile table t1 (pk smallint not null, decfld decimal(18,2) ) Unique Primary Index (PK) On Commit preserve rows;
Insert Into t1 values (0,0.00); Insert Into t1 values (2,2.00); Insert Into T1 values (4,4.5); Insert Into T1 Values (5,5.51);
Sel * From T1 order by 1;
Select PK , Cast(DecFld as Char(20)) , CASE WHEN DecFld = 0 Then ' 0' WHEN DecFld Mod 1 = 0 THEN ' '||Substring(DecFld From 1 for 17) WHEN DecFld Mod 0.1 = 0 THEN ' '||Substring(DecFld From 1 For 19) ELSE DecFld END (Char(20)) As NDec From T1 Order By 1 ;
Select PK ,DecFld as a , Cast(DecFld as Char(20)) as b ,TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM Cast(CAST(DecFld as DECIMAL(18,2) FORMAT '----------------9.99') as varchar(20)))) as c From T1 Order By 1 ;