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

Good catch - and that leads me to a simplified but perhaps not so intuitive version -

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

Added a couple parentheses, I think operator hierarchy would take care of things (exponentiation is always first) but who needs what happens when you assume?

Actually I kinda like this version better - it's not intuitively obvious why you subtract 1 in the earlier version, perhaps.

Vern

On 8/8/2010 8:48 AM, Mark Murphy/STAR BASE Consulting Inc. wrote:

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

On 8/6/2010 5:52 PM, Gundermann, Glenn / Kuehne + Nagel / Tor ZI-A wrote:

Hi Darryl,

Two things come to mind:

First:

Look at the table SYSCOLUMNS. E.g.
Select length from syscolumns where system_column_name = 'column-name' and system_table_name = 'table_name'

Second:

Length(column-name) will return the length in bytes, which will work for you if the numeric field is zoned.
It will retrieve the length on the column for every record so you must use " Fetch First Row Only".
To get the maximum value, I think this would work: 10**length(column-name)-1
Something like this perhaps:
select 10**length(column-name)-1 from file-name
Fetch First Row Only

If your column is not zoned, I don't know.

I could be wrong but I seem to recall Birgitta writing about this a while back but I don't remember the specifics.
It might not be in the RPG list though since this is SQL.

Sorry I couldn't help more.

Yours truly,

Glenn Gundermann
Manager, IT Application Management (Tor ZI-A)
Kuehne + Nagel Ltd.
77 Foster Crescent
Mississauga, ON L5R 0K1
Tel: (905) 501-6596
Cell: (416) 317-3144
glenn.gundermann@xxxxxxxxxxxxxxxx


To learn more about Kuehne + Nagel Ltd, please visit our website at: www.kuehne-nagel.com For the Kuehne + Nagel email disclaimer, visit: http://www.kn-portal.com/material/Electronic_Email_Disclaimer_English_French.pdf



Visitez le site internet de Kuehne + Nagel Lt?e: www.kuehne-nagel.com Notre d?charge de responsabilit? est disponible comme suit: http://www.kn-portal.com/material/Electronic_Email_Disclaimer_English_French.pdf