I am exploring the possibility of an expression-based index to improve query response time. What I would like to do is index on the maximum setup-date of a record, and have tried the following:
CREATE INDEX "TESTDB"."X1E_SMASTER"
USING STOGROUP GTTEST01
PIECESIZE 2097152 K;
However, when I try to execute this (in Data Studio) I receive this message:
"AN AGGREGATE FUNCTION IS NOT VALID IN THE CONTEXT IN WHICH IT WAS INVOKED. SQLCODE=-120,..."
I know MAX is considered to be an aggregate function, although I was thinking of it as a "column function." I was hoping it would be allowed in this context .
The table is used by hundreds of applications, and in most of
them to get the most recent record the WHERE clause contains a
sub-SELECT requesting the MAX SETUP-DATE for that ID number and the
state of readiness. Is there an alternate path to my goal of
eliminating the sub-SELECT and improving response time?
John Unger[Organization Members] @ Mar 06, 2017 - 04:25 PM (America/Central)