Problem solve Get help with specific problems with your technologies, process and projects.

Use CURRENT DATE to process a table

Use "CURRENT DATE - 30 DAYS" in a Where causes SQL to process a table one row at a time.

In SQL using "CURRENT DATE - 30 DAYS" in a Where causes SQL to process a table one row at a time. By creating a one-row file with Current Date, 30 days as a field and Join on the date field, you are selecting SQL and will use an Index on that date field. Go immediately to the first record, the current date, and 30 days in your temporary file. When dealing with millions of records, significant performance improvements result.

CREATE TABLE TEMPDATE
(BDATE DATE NOT NULL WITH DEFAULT);
INSERT INTO TEMPDATE (BDATE) VALUES(CURRENT DATE - 30 DAYS);
select * from ANYTABLE join TEMPDATE on DATEfield > bdate

==================================
MORE INFORMATION ON THIS TOPIC
==================================

The Best Web Links: tips, tutorials and more.

Ask your programming questions--or help out your peers by answering them--in our live discussion forums.

Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.


This was last published in November 2003

Dig Deeper on RPG iSeries programming

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close