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