Archives of the TeradataForum

Message Posted: Fri, 21 Mar 2003 @ 16:27:57 GMT

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

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?

TIA,

Jack

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