Archives of the TeradataForum

Message Posted: Mon, 02 Jul 2001 @ 18:14:06 GMT

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

Ruth,

Here is an SQL that was written by Frank Slovac from NCR to help me answer the same question that you have:

     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
     ('AFFINITY','CLAIMS_ECOMM','CLICKSTREAM','PCDW_AURORA','PCDW_AUTO'
     ,'PCDW_AUTOCS','PCDW_AUTOQB',

     'PCDW_CBR','PCDW_CLAIMS','PCDW_DSS','PCDW_ICAR','PRODUCERS','WEB_TEST')

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

NOTE: This will check for fieldstatistics. You can also check indexstatistics by changing the column.

Hope this is what you were looking for!

Tobi Zappe
Nationwide Insurance

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