Re: How can one retrieve or establish the max value a numeric field can hold using SQL?

Hi Joe

The gauntlet has been thrown - here's a possible solution -

SELECT decimal(substr(strip(digits(decimal(10**precision - 1)), L, '0'), 1, precision - scale) concat '.' concat substr(strip(digits(decimal(10**precision - 1)), L, '0'), precision - scale + 1, scale), 31, 15) FROM SYSCOLUMNS

This did not work for everything in SYSCOLUMNS - as you mentioned, those pesky constants for precision and scale are problematic. Perhaps a statement could be prepared from a string and do this. I'll not pursue this further, this was interesting enough!

There were also problems with precision over 14 - 15 and 0 did not work here. 18 and anything did not seem to work. Might be defaults on the DECIMAL function when P and S are not specified.

The CAST as DECIMAL is probably no better - probably also requires integer constants for P & S.

Vern

On 8/8/2010 12:42 PM, Joe Pluta wrote:

There's one slight problem: SQL doesn't always do great with precision.
If you execute this code on a field with precision 6 and scale 2, you
get the answer 9999.98.

Try this:

select decimal((10**6-1)/10**2,6,2) from sysibm/sysdummy1

You will not get 9999.99 as you would expect.

With enough rounding and half-adjust, you can make it work. But then
you need to figure out how to round, which isn't always easy with
decimal positions. The other problem is that you won't be able to cast
it to the correct decimal form, since DECIMAL requires constants for the
precision and scale (this also rules out trying to use some spiffy
SUBSTR solutions).

Where's a good *HIVAL when you need one?<grin>

Joe

P.S. As an aside, Excel gets this right, so it's not some mystical issue
with 6,2 fields. I haven't tried it in SQL Server or MySQL, so I can't
say whether it's DB2 specific. I'd be interested to know.

I would make one small change to that formula.

try (10**precision-1)/10**scale

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx

-----rpg400-l-bounces@xxxxxxxxxxxx wrote: -----

To: RPG programming on the IBM i / System i<rpg400-l@xxxxxxxxxxxx>
From: Vern Hamberg<vhamberg@xxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
Date: 08/06/2010 07:24PM
Subject: Re: How can one retrieve or establish the max value a numeric field can hold using SQL?

Glenn

Thinks makes me think - there are 2 columns there - PRECISION and SCALE
- the former is the number of digits, the latter the number of decimals.
So to accomodate decimal places, maybe modify you suggestion to this

To calculate the maximum value possible for packed or zoned, you could
use this formula to get all 9's - I think!

(10**precision-1)/scale

PRECISION is what you need instead of LENGTH, which is only the number
bytes of storage.

I suppose some kind of table-expression could work this out in a
CASE-END CASE construct. I'm not going to figure that out right now.

Hope that makes sense. It's Friday and late, after all!

Vern