Archives of the TeradataForum

Message Posted: Tue, 05 May 2009 @ 17:09:20 GMT

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

I think you'd want to base your query on dbc.indices for any IndexType in (P,Q).

For example, to get the actual duped rows dumped to a file, you could have a BTEQ script something like

     .EXPORT file=runme.btq
     Select '.EXPORT file=DupeRows.txt' ;

     select 'select * from ' || trim(DatabaseName) || '.' || trim(TableName)
     || ' qualify count(*) over (partition by ' ||
     max(case when ColumnPosition = 1 then trim(ColumnName) else '' end) || max(case when
     ColumnPosition = 2 then ', ' || trim(ColumnName) else '' end) ||
     max(case when ColumnPosition = 3 then ', ' || trim(ColumnName) else '' end) ||
     max(case when ColumnPosition = 4 then ', ' || trim(ColumnName) else '' end) ||
     max(case when ColumnPosition = 5 then ', ' || trim(ColumnName) else '' end) ||
     max(case when ColumnPosition = 6 then ', ' || trim(ColumnName) else '' end) ||
     max(case when ColumnPosition = 7 then ', ' || trim(ColumnName) else '' end) ||
     max(case when ColumnPosition = 8 then ', ' || trim(ColumnName) else '' end) ||
     max(case when ColumnPosition = 9 then ', ' || trim(ColumnName) else '' end) ||
     max(case when ColumnPosition = 10 then ', ' || trim(ColumnName) else '' end) ||
     max(case when ColumnPosition = 11 then ', ' || trim(ColumnName) else '' end) ||
     max(case when ColumnPosition = 12 then ', ' || trim(ColumnName) else '' end) ||
     max(case when ColumnPosition = 13 then ', ' || trim(ColumnName) else '' end) ||
     max(case when ColumnPosition = 14 then ', ' || trim(ColumnName) else '' end) ||
     max(case when ColumnPosition = 15 then ', ' || trim(ColumnName) else '' end) ||
     max(case when ColumnPosition = 16 then ', ' || trim(ColumnName) else '' end) || ') > 1;'
     from dbc.indices
     where IndexType in ('P','Q')
     group by DatabaseName, TableName;

     Select '.EXPORT reset';

     .EXPORT reset
     .RUN file=runme.btq
     .EXIT

I'm not suggesting it would be a good idea to actually do this without knowing something about how many rows you're likely to get, estimated impact on other users, etc., it's just an example of how you to get the SQL you want out of DBC in a runnable format.

For example, it might be worth the effort to re-write this using OLAP functions, which would let you write pretty SQL and not have to hard code up to the max number of columns allowed.

Dave

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