Archives of the TeradataForum

Message Posted: Thu, 23 Feb 2006 @ 15:22:47 GMT

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

I'm running the following query and running into issues.

     Select b.daypartdesc, Count(*)
     >From FactTable a Join DayPartBase b
     On cast (a.TimeStampCol As time(0)) >= b.starttime
     And cast (a.TimeStampCol As time(0)) <= b.endtime
     Where a.ReportingDate In (DATE '2006-01-21')
     Group By b.daypartdesc

DayPartBase is a table with 48 records, one for each 30 minute division of the day (i.e. 1200 - 1230, 1231 - 1300, etc). We are trying to do a simple count of records for a single ReportingDate broken down in 30 minute increments. The fact table is fairly large, though not the largest we have (1.1billion records).

I can't figure out why the query above won't run. I've tried it in a number of formats (putting the join in the WHERE clause instead of using a JOIN clause, using a BETWEEN). If I only use one of the comparisons (either after b.StartTime or prior to b.EndTime) the query runs fine (though returning the wrong data as the JOIN is incorrect). However, with both components it fails and gives an error stating Invalid operation on an ANSI Date/Time.

Any ideas? Am I missing something simple here?

Thanks,

Mike

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