How to make date logic/math use a date partition

Database

How to make date logic/math use a date partition

We have a report that needs to automatically look for the past weeks data. Normally you would just use date logic (Where date between date -7 and date -1) However, this is on a very large table with a date PPI. But the sql will not use the partition and is doing a full table scan. Is there a way to work around this and get the logic to use the PPI?
3 REPLIES
Enthusiast

Re: How to make date logic/math use a date partition

Hi,

can u please post the PPI statement used in table DDL.
Also post the column info of PI And SI on the table.
And is there any join index or has index on th table ????
Enthusiast

Re: How to make date logic/math use a date partition

Why not write an sql statement which will dynamically output the sql statement you need to execute (with the date range hardcoded in order to make use of the PPI), this dynamic SQL can then be run as a BTEQ EXPORT statement to a file with can be then executed using the BTEQ .RUN FILE command.

Example:

SELECT
'SELECT ID, REC_DT FROM DBase.Tbl WHERE REC_DT BETWEEN ' || CAST((DATE - 7) AS INTEGER)|| ' AND ' || CAST(DATE AS INTEGER) || ';'
;

Good luck.
Junior Contributor

Re: How to make date logic/math use a date partition

Hi Dennis,
you must be running a release pre-TD12, because this version should work the way you want it.

You might try a Derived Table/View with calculated columns, maybe the optimizer does a product join "enhanced by dynamic partition elimination":
SELECT *
FROM tab, (SELECT DATE-7 AS datefrom, DATE AS dateto) dt
WHERE tab.datecol BETWEEN datefrom AND dateto

Dieter