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

Glenn



Thank you, this is in the direction I need to go. However, for numeric
fields with decimals the result is too big. I converted the packed fields by
multiplying by 2*length(field) -1. This works but I cannot find a way of
getting the number of decimals to subtract from the result. Example for a
packed field (9,3) length = 5. I get 999,999,999. I need to get 999,999.999
or 999,999.000. This would be integers = 6, decimals = 3.



For performance reasons, I do not want to do a join for every file.



Is there a similar command to length that I can use to get the integer and
decimal lengths?



Thanks Darryl.



---- copy of message ---

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