All Forums Database
ppad001 18 posts Joined 06/11
13 Aug 2012
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 !!

ulrich 816 posts Joined 09/09
13 Aug 2012

"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...



feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ppad001 18 posts Joined 06/11
13 Aug 2012

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

You must sign in to leave a comment.