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

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