All Forums Database
padupuis 3 posts Joined 10/10
11 Oct 2010
Help on decimal display format

Hi, could anyone help me with formating a decimal ?

I need to format a decimal(18,2) field with the following rule :

"diplay only significant digits, when the decimal part is 0, the decimal separator should not appear
here are a few examples :
4.50 -> displayed 4.5
2.00 -> displayed 2
0.00 -> displayed 0"

the following expression I currently use does not give the right result on the decimal part
TRIM(CAST(MOY as DECIMAL(18,2) FORMAT '----------------Z.ZZ'))

anyone knows the right format string ?


Jimm 298 posts Joined 09/07
11 Oct 2010

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)
END (Char(20)) As NDec
From T1 Order By 1

padupuis 3 posts Joined 10/10
12 Oct 2010

The output is a file produced using fastexport.
Thank you for your help !

padupuis 3 posts Joined 10/10
12 Oct 2010

here is an alternate solution :

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

best regards

You must sign in to leave a comment.