Archives of the TeradataForum

Message Posted: Wed, 25 Jun 2008 @ 14:48:33 GMT

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

Hi,

The select is within a macro and produces a list of call statements for every column within a paramatised table within paramatised database and a paramatised sample.

The problem is in the store procedure, namely the formats within the case statement. I am trying to change the format as its converted to a Varchar(5000), so that i dont get comma's in the smallint, integer defined columns (ie 1,000), or the date truncated on the year (ie 08/06/25) on the date defined columns.

So I would like to see 1000 or 2008/06/25 from the above.

Is a Store Procedure the best way to do this ?

     select 'call systec.MVCcol_count
     (''' || trim(databasename) || ''',
       ''' || trim(tablename) || ''',
       ''' || trim(columnname) || ''',
         ' || mvcsample ||');'
       From Systec.MVCReqtable
     order by 1
     ;


     replace procedure  systec.MVCcol_count
     ( in databasename varchar(30)
     , in tablename    varchar(30)
     , in columnname   varchar(30)
     , in mvcsample    byteint
     )

     begin

     call dbc.sysexecsql (
     'locking table '||trim(:databasename)||'.'||trim(:tablename)||' for access
       insert into systec.MVCAnalysis
       select top '||:mvcsample||' percent '''|| trim(:databasename) || ''',
       '''|| trim(:tablename) || ''',
       '''|| trim(:columnname) || ''' as cname,
         '||' case when columntype = ''DA'' then CAST(( ' || :COLUMNNAME ||
     '(Format ''YYYY/MM/DD'')) AS VARCHAR(5000))'||
            '     when columntype = ''D''  then CAST(( ' || :Columnname ||
     '(Format ''-(17)9.9999999'')) as varchar(5000))'||
            '     when columntype = ''I''  then CAST(( ' || :COLUMNNAME ||
     '(format ''-(10)9'')) AS VARCHAR(5000))'||
         '     when columntype = ''I2'' then CAST(( ' || :COLUMNNAME ||
     '(format ''-(5)9'')) AS VARCHAR(5000))'||
            ' else CAST( ' ||:COLUMNNAME || ' AS VARCHAR(5000))'||
            ' end '||' as xval,
       count(*) as xcnt,
       sum(xcnt) over (partition by cname),
       rank() over (partition by cname order by xcnt desc)
       from '|| trim(:databasename)|| '.'|| trim(:tablename)|| ' join
               systec.MVCReqtable
       on   '''||:databasename||''' = Systec.MVCReqtable.databasename
       and  '''||:tablename   ||''' = Systec.MVCReqtable.tablename
       and  '''||:columnname  ||''' = Systec.MVCReqtable.columnname
       group by 4;');

       end;

Regards

Trevor Price - Certified Teradata Master V2R5

Operations Services �- Database & Middleware Mgmt.
Global Technology | Global Operations

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