Archives of the TeradataForum
Message Posted: Fri, 21 Mar 2003 @ 16:27:57 GMT
The table is:
create table MyTable (MyID Int, MyCol varchar(26)) primary index(MyId);
There is no index on MyCol.
Three million rows of flat file data is received from an outside source and placed in this table.
This is valid:
Select cast(MyCol as timestamp(0)) From MyTable Where MyId = 1
If the predicate in the Where clause is removed the query is:
Select cast(MyCol as timestamp(0)) From MyTable
The query returns error 7451 "Invalid Timestamp" in less than a second.
Through a process of discovery, a convention is discovered: The originating system recognizes the first second of a day as: '2003-03-31 24:00:00'. Although we know this is equivalent to '2003-04-01 00:00:00' Teradata takes the more intuitive route and would display '2003- 04-01 00:00:00'; it also complains about '2003-03-31 24:00:00'. This is the source of the 7451 error code.
This select is created to counter the unconventional '2003-03-31 24:00:00'
Select case substring(MyCol,12,2) -- Correctly extracts a two digit hour when '24' then cast(substring(MyCol,1,10) || ' 23:59:59' as timestamp(0)) -- The brief but wrong! workaround else cast(MyCol as timestamp(0)) end From MyTable
If the Where clause includes a predicate [substring(MyCol,12,2) <> '24'] then the query runs.
The Case function does not trap the error. How does one trap an error with Case? Is there another more general method to trap errors?
How can the database return the 7451 error in less than a second when it takes two minutes to scan the entire table?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|