This may have been handled before in this forum...but as I am new here I would have missed it...here goes.

I am trying to get an SQL statement to output numbers from an existing table.

If I use round(field,2) I get the following:-

Raw data............Rounded data

0.00.........................0.0
12.256..................12.26
12.5.......................12.5
12..........................12.0

What I really want is everything to have 2 dec places IE 0.00 or 12.50

I have tried TRUNC - same results.

Is there a way to get 2 dec places regardless ?

Nic

Ps Thanks in advance.

Hi,

Please test defining DBFORMAT variable environment.

Gustavo

Sorry - I did not make myself clear - I dont want to change the data stored as it would effect a huge pre existing database - what I want to do is FORMAT the data I output in a SQL query - we dont have 4GL so I am limited to SQL.

I am having trouble as the data is a floating point and sometimes there is only 1 or even 0 decimal places...but I want to show 2 - so I need the equivalent of rpad with 0 of r the decimal element.

Nic

Hi Nic,

Excuse me, the environment variable DBFORMAT and DBMONEY, they permit you formatear the exit of the data. i understand that you want this.

ie

Value Format String DBFORMAT Setting Displayed Result
1234.56 $$#,###.## $:,:.: $1,234.56
1234.56 $$#,###.## :.:,M 1.234,56
1234.56 #,###.##@@ $:,:.: 1,234.56
1234.56 #,###.##@@ :.:,M 1.234,56DM

Gustavo.