select

c.databasenamei as DatabaseName,

b.tvmnamei as TableName,

a.fieldname as ColumnName,

cast((case when substr(fieldstatistics,1,1) = 'D5'XB

then '2005'

when substr(fieldstatistics,1,1) = 'D6'XB

then '2006-'

when substr(fieldstatistics,1,1) = 'D7'XB

then '2007-'

when substr(fieldstatistics,1,1) = 'D8'XB

then '2008-'

when substr(fieldstatistics,1,1) = 'D9'XB

then '2009-'

when substr(fieldstatistics,1,1) = 'DA'XB

then '2010-'

when substr(fieldstatistics,1,1) = 'DB'XB

then '2011-'

when substr(fieldstatistics,1,1) = 'DC'XB

then '2012-'

else NULL

end)||

(case when substr(fieldstatistics,3,1) = '01'XB

then '01-'

when substr(fieldstatistics,3,1) = '02'XB

then '02-'

when substr(fieldstatistics,3,1) = '03'XB

then '03-'

when substr(fieldstatistics,3,1) = '04'XB

then '04-'

when substr(fieldstatistics,3,1) = '05'XB

then '05-'

when substr(fieldstatistics,3,1) = '06'XB

then '06-'

when substr(fieldstatistics,3,1) = '07'XB

then '07-'

when substr(fieldstatistics,3,1) = '08'XB

then '08-'

when substr(fieldstatistics,3,1) = '09'XB

then '09-'

when substr(fieldstatistics,3,1) = '0A'XB

then '10-'

when substr(fieldstatistics,3,1) = '0B'XB

then '11-'

when substr(fieldstatistics,3,1) = '0C'XB

then '12-'

else 'xx-'

end)||

(case when substr(fieldstatistics,4,1) = '01'XB

then '01'

when substr(fieldstatistics,4,1) = '02'XB

then '02'

when substr(fieldstatistics,4,1) = '03'XB

then '03'

when substr(fieldstatistics,4,1) = '04'XB

then '04'

when substr(fieldstatistics,4,1) = '05'XB

then '05'

when substr(fieldstatistics,4,1) = '06'XB

then '06'

when substr(fieldstatistics,4,1) = '07'XB

then '07'

when substr(fieldstatistics,4,1) = '08'XB

then '08'

when substr(fieldstatistics,4,1) = '09'XB

then '09'

when substr(fieldstatistics,4,1) = '0A'XB

then '10'

when substr(fieldstatistics,4,1) = '0B'XB

then '11'

when substr(fieldstatistics,4,1) = '0C'XB

then '12'

when substr(fieldstatistics,4,1) = '0D'XB

then '13'

when substr(fieldstatistics,4,1) = '0E'XB

then '14'

when substr(fieldstatistics,4,1) = '0F'XB

then '15'

when substr(fieldstatistics,4,1) = '10'XB

then '16'

when substr(fieldstatistics,4,1) = '11'XB

then '17'

when substr(fieldstatistics,4,1) = '12'XB

then '18'

when substr(fieldstatistics,4,1) = '13'XB

then '19'

when substr(fieldstatistics,4,1) = '14'XB

then '20'

when substr(fieldstatistics,4,1) = '15'XB

then '21'

when substr(fieldstatistics,4,1) = '16'XB

then '22'

when substr(fieldstatistics,4,1) = '17'XB

then '23'

when substr(fieldstatistics,4,1) = '18'XB

then '24'

when substr(fieldstatistics,4,1) = '19'XB

then '25'

when substr(fieldstatistics,4,1) = '1A'XB

then '26'

when substr(fieldstatistics,4,1) = '1B'XB

then '27'

when substr(fieldstatistics,4,1) = '1C'XB

then '28'

when substr(fieldstatistics,4,1) = '1D'XB

then '29'

when substr(fieldstatistics,4,1) = '1E'XB

then '30'

when substr(fieldstatistics,4,1) = '1F'XB

then '31'

else 'xx'

end)as date ) as CollectionDate,

cast(substr(cast(a.lastaltertimestamp as char(32))

,1,10) as date) as LastAlter,

date - collectiondate as FromCurrent,

lastalter - collectiondate as FromAlter

 

from

dbc.tvfields a

Left outer join

dbc.tvm b

on a.tableid = b.tvmid

Left outer join

dbc.dbase c


on b.databaseid = c.databaseid
where c.databasename='yourdbname' and 
b.tvmname='yourtablename ' and 
 b.tablekind = 'T'