MTJI self joins

Database
Enthusiast

MTJI self joins

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

Any suggestions on alternatives?

Thanks!
Tags (2)
2 REPLIES
Junior Contributor

Re: MTJI self joins

AFAIK there's no way to use the same table more than once in a Join Index.

Dieter
Enthusiast

Re: MTJI self joins

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.