Archives of the TeradataForum

Message Posted: Tue, 13 Aug 2002 @ 18:15:23 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I was given this a while back

sel
c.databasenamei as DatabaseName,
b.tvmnamei as TableName,
a.fieldname as ColumnName,
cast((case when substr(fieldstatistics,1,1) = 'D2'XB
           then '2002-'
           when substr(fieldstatistics,1,1) = 'D1'XB
           then '2001-'
          when substr(fieldstatistics,1,1) = 'D0'XB
          then '2000-'
          when substr(fieldstatistics,1,1) = 'CF'XB
          then '1999-'
          when substr(fieldstatistics,1,1) = 'CE'XB
          then '1998-'
           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,
dbc.tvm b,
dbc.dbase c
where a.tableid = b.tvmid
and b.tablekind = 'T'
and  b.databaseid = c.databaseid
and upper(trim(c.databasenamei)) in
('DBA')

and a. fieldstatistics is not null
order by 1,2,3
;

	<Prev	Next>		<<First	<Prev	Next>	Last>>