All Forums Database
goldminer 118 posts Joined 05/09
29 Apr 2010
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!

dnoeth 4628 posts Joined 11/04
02 May 2010

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

Dieter

Dieter

goldminer 118 posts Joined 05/09
05 May 2010

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.

You must sign in to leave a comment.