Help on decimal display format

Database

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 ?

regards
Tags (1)
3 REPLIES
Enthusiast

Re: Help on decimal display format

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
;

Re: Help on decimal display format

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

Re: Help on decimal display format

hi,
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