Example: Creating a Join Index with a Multicolumn CASE Expression in its Select List - Teradata Database

This example uses table t1 as defined in Example: Creating a Join Index With a Period Column in its Select List as well as the following tables to create a join index with a multicolumn CASE expression in its select list.

     CREATE TABLE t2 (
       a2 INTEGER,
       b2 INTEGER,
       c2 INTEGER,
       d2 PERIOD(DATE),
       e2 DATE,
       f2 CHARACTER(100))
     UNIQUE INDEX(b2);
     CREATE TABLE t3 (
       a3 INTEGER,
       b3 INTEGER,
       c3 INTEGER);
     CREATE JOIN INDEX ji_case AS 
       SELECT a1,b1,c1,CASE 
                         WHEN t1.b1=t2.b2 
                         THEN t2.a2 
                         ELSE t3.a3 
                       END AS a
       FROM t1,t2,t3 
       WHERE a1=a2 
       AND a2=a3;

If you then submit a HELP JOIN INDEX request on ji_case, the system returns a report like the following:

     HELP JOIN INDEX ji_case;

Result:

*** Help information returned. 3 rows.
*** Total elapsed time was 1 second.
Column Name                Type      Comment
-------------------------- --------- ---------------------
a1                         I         ?
b1                         I         ?
c1                         I         ?
a                          I         ?

As was done for column b in Example: Creating a Join Index With a BEGIN Bound Function in its Select List, column a in this example can be used to map an identical query expression to the join index.

Multicolumn expressions in a join index definition affect its maintenance. For ji_case, the value of column a depends on t1.b1 , t2.a2 , t2.b2, and t3.a3 , so join index maintenance is required whenever any of those columns is updated, and the join index update uses a spool file to resolve the expression. For example, a simple update on base table t1 like the following request cannot be mapped to a direct update on ji_case, although the delete condition a1=10 can be evaluated directly on the join index.

     UPDATE t1 
       SET b1=10 
       WHERE a1=10;