Max value for datatype column.

Database
Enthusiast

Max value for datatype column.

Can we write a SQl to determine the max value of a datatype ?

For ex: if we have a column emp_id as INTEGER, the SQL would have emp_id as input and the result would be the max value for integer datatype...

Excuse me if I sound stupid !!

Tags (1)
2 REPLIES
Senior Supporter

Re: Max value for datatype column.

"the SQL would ..." 

can you explain in a bit more detail?

You might be able to query dbc.columns.

select columnname,

         case when datatype = 'I' then '2147483647'

                       when datatype = 'I2' then '32767'

                       when datatype = 'DA' then '9999-12-31'

.... --- one rule per datatype, 

         else 'unkown' end as max value 

from dbc.columns 

where databasename = 'your db'

and tablename = 'your table'

decimal and char / varchar, Byte, time, timestamp etc. would also require to consider columnlength and DecimalTotalDigits, DecimalFractionalDigits consideration. Unicode and and Latin might also require special considerations...

So it can become an interesting case statement if you need all datatypes...


Enthusiast

Re: Max value for datatype column.

Thanks a ton, the suggest approach got me my result !!