lenght of Numeric field 1

[PatelRam]

How do I find lenght of Numeric field in db2...???


For eg.

Amount Length (Expected Result)

-10202.343 10
19192222.44 11
-292.392 8

Thanks in advance

Ram

 

[DBAJr]

According to your need, function length(char(col-name)) may work for you.

 

[PatelRam]

It's not working because it will prefix '0' at start of number.

NOTE : INT_AMOUNT = NUMERIC(13,2)

Select AMOUNT,char(amount),length(char(amount)) from TABLE_NAME

RESULT


-1200000.00,-00001200000.00,15
-766799.03,-00000766799.03,15
-400000.00,-00000400000.00,15
-343663.97,-00000343663.97,15
-300000.00,-00000300000.00,15


Can anybody help me in this ???

Thanks,

Ram

 

[blom0344]

CASE WHEN AMOUNT < 0 
THEN SMALLINT(LOG10(AMOUNT))+5
ELSE SMALLINT(LOG10(AMOUNT))+4 END

Untested....

Ties Blom
Information analyst

 

[MarcLodge]

Don't think that LOG10 function works on a negative value, try this instead:

CASE WHEN AMOUNT < 0             
THEN SMALLINT(LOG10(AMOUNT*-1))+5 
ELSE SMALLINT(LOG10(AMOUNT))+4 END

 

[blom0344]

Ah Marc,

You got me there. But it is 25 years ago that I worked with LOG/LN. Completely forgot that aspect

Ties Blom
Information analyst

 

[MarcLodge]

Ties,
I was fortunate enough to be in a position to try it out, and therefore discovered the minor flaw. It's a great solution and deserves a star.
Marc