Archives of the TeradataForum

Message Posted: Thu, 08 Jul 2010 @ 17:01:41 GMT

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

It's a bug. Timestamp-based PPIs will use partition elimination only for a single equality predicate. No OR, IN or BETWEEN. I think it's fixed in 13. We just went to 12 and it's still there.

There is a work-around if you're not actually using the time portion of the timestamp (which might be your case since the field is named xxx_DT): create a table based on sys_calendar.calendar with UPI of calendar_date and a new field based on CAST(calendar_date as timestamp). You can then join to this on the PPI timestamp and use BETWEEN/IN/OR criteria on calendar_date. I don't know of anything you can do if you have time portions other than 00:00:00.

So you would do something like

     SELECT COUNT(*)
             FROM "V_WOFDWVW"."F_VOL_DCSN" "Volume_Measures"
             INNER JOIN some_database.new_calendar cal  -- your new table here
               on "Volume_Measures"."DW_END_DT" = cal.calendar_date_timestamp
            WHERE cal.calendar_date  BETWEEN '2010-12-31' AND '9999-12-31'

Dave

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