Decimal Formating issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vp - 09 Feb 2006 15:26 GMT

Hi all,

I have a field AMOUNT(Decimal 13,2) and when I check the length its
coming as 7.

AMOUNT LENGTH
15.00 7
118.00 7
0.00 7
10.00 7

I need to format theAMOUNT field so that when I do a length of it I
should get 14(this includes a decimal)
Example
AMOUNT LENGTH
12345678911.58 14

if the amount is 15.00 I have to LPAD appropriate spaces so that when i
do lengthg of the amount it should come like ' 15.00' ...
I tried doing
RIGHT(' .00'||RTRIM(char(dec(AMOUNT,13,2))),14)

but I am getting like 00000000015.00 and I have 5 other amount fields
and when I use it in SQL getting SQL1585N A system temporary table
space with sufficient page
size does not exist. SQLSTATE=54048

please advice me.....

Reply to this Message

Phil Sherman - 09 Feb 2006 17:36 GMT

The length of a Decimal 13,2 column is seven (7) bytes, exactly what the
length function reports. Standard formatting by the clp places the data
in a 15 print position wide field with leading blanks. the two extra
bytes in the output field are to provide space for the decimal point and
a possible sign.

Decimal data is stored placing two digits en each byte, with one nibble
(half a byte) reserved for the sign. 13+1 = 14; 14/2 = 7. The two (2) in
the column definition describes the placement of the decimal point.

Multiplying the length by two for decimal columns will not give you the
defined length either. A column defined as 14,2 takes up the same
physical space as one defined as 15,2.

The catalog tables contain information about column definitions and can
be used to retrieve the size of the decimal column. When you set up a
field to display the data, don't forget to provide space for a sign and
the decimal point.

Phil Sherman

> Hi all,
>
[quoted text clipped - 24 lines]
>
> please advice me.....

Reply to this Message

Tonkuma - 10 Feb 2006 02:37 GMT

1) There may be more simple way. But, anyway this will satisfy your
requirement.
SUBSTR(' '||TRANSLATE(LTRIM(TRANSLATE(CHAR(AMOUNT),'
','0')),'0',' '),LENGTH(LTRIM(TRANSLATE(CHAR(AMOUNT),' ','0')))-3,14)

2) The reason of SQL1585N must be that function RIGHT is a DB2 supplied
UDF (schema is SYSFUN) and the attribute of returned data is
VARCHAR(4000).
So, if you use 5 such expressions, one row will exceeds at least 20,000
byte. This meas that 32K page temporary table is required.
One way to reduce the size is using SUBSTR.
Like this:
SUBSTR(RIGHT(.........), 1, 14)

Reply to this Message

Tonkuma - 10 Feb 2006 03:04 GMT

1) There may be more simple way.
But, anyway this will meet your requirement.
SUBSTR(' '||TRANSLATE(LTRIM(TRANSLATE(CHAR(AMOUNT),'
','0')),'0',' '),LENGTH(LTRIM(TRANSLATE(CHAR(AMOUNT),' ','0')))-3,14)

2) The reason of SQL1585N is that function RIGHT is a DB2 supplied
UDF(schema is SYSFUN). So, attribute of returned value is
VARCHAR(4000). If you use 5 such expressions, size of one row exceeds
20,000.

Reply to this Message