Archives of the TeradataForum
Message Posted: Thu, 23 Feb 2006 @ 15:22:47 GMT
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?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|