|
Archives of the TeradataForumMessage Posted: Wed, 25 Jun 2008 @ 14:48:33 GMT
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.
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||