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
thanks Dieter... you are the man! I also got a response from Teradata on this limitation. They are going to allow self joins in the creation of a join index in a future release... don't know if it will be 12 or 14... just an FYI.