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, Vantage returns a report like the following:
HELP JOIN INDEX ji_case; *** 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;