All Forums Database
HateOra 17 posts Joined 10/13
05 May 2016
Bad data in table , need suggestion to isolate the problem

Hello,
I have a unique situation here and I need your suggestions help to achieve it.
I have close to 10000 tables where I have a timestamp fields which (probably) has bad data(long story behind this).
Problem is : If I run select on any of the table which has bad timestamp value it fails with a  error.
I want a suggestion , how to find out all the tables which has issues , I cna not go through each and every table and run a select. 

dnoeth 4628 posts Joined 11/04
05 May 2016

You should contact Teradata support immediately and tell them the long story :-)

Dieter

yuvaevergreen 93 posts Joined 07/09
06 May 2016

Timestamp columns have invalid timestamp values?...

ToddAWalter 316 posts Joined 10/11
06 May 2016

Like the previous post, I wonder what data type the timestamps are stored in. 
 
if there is any kind of naming convention(s) that allows selection of the column names which contain the timestamps, then a query can be written against the dictionary views such that the query result is a set of SQL select statements against those columns. That result can be stored and then executed to accomplish the requirement. If no naming convention, then a list will have to be made of table and column names, then the SQL statements can be generated from that list.

Fred 1096 posts Joined 08/04
06 May 2016

It's possible that data can be stored as a valid TIMESTAMP value but it's so close to the min/max allowable that applying the local / session time zone offset causes the result to be out of range. (For example, you can't display the UNTIL_CHANGED timestamp value AT LOCAL when the session time zone is ahead of UTC.)
 
Depending on how you got to that point, ALTER TABLE FROM TIME ZONE might be used if all rows need time zone shift, or you can set session time zone offset to 00:00 to run queries to find (and potentially to fix) just the offending rows. 
 
But you still have to check each table individually, there's no way around that.

You must sign in to leave a comment.