× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.