Dear All, Need help with below situation....
Data
id        date
100   6/6/2015
100   8/1/2015
100   9/30/2015
200   8/11/2015
300   7/18/2015
300   1/10/2015
300   3/5/2015
300   6/15/2015

Does anybody have a way to create a join index that includes table aliases in the join index definition? For example:

CREATE JOIN INDEX JI_TABLE_ALIASES, NO FALLBACK, CHECKSUM = DEFAULT AS
SELECT
ALIAS1.FIELD1,
ALIAS2.FIELD1,
SUM(TABLEB.AMT) AS AMT
FROM TABLEB,
TABLE A ALIAS1,
TABLEA ALIAS2
WHERE TABLEB.KEY1 = ALIAS1.KEY1
AND TABLEB.KEY2 = ALIAS2.KEY1;

When I try to create a multi table join index similar in design to the one illustrated above, I get the following error:

5464: Error in Join Index DDL, Self joins are not allowed