Using the OPTIONAL keyword with parentheses ( )

You can control the processing of the OPTIONAL keyword with parentheses. For example, in the following query the tables CUSTOMERS and ORDERS can be dropped:

SELECT ITEMS.ORDERID, ITEMS.PRICEQUOTE, ITEMS.QUANTITY

FROM "CUSTOMERS.sma" AS CUSTOMERS INNER JOIN "ORDERS.sma" AS ORDERS ON (CUSTOMERS.CUSTID = ORDERS.CUSTID) LEFT OPTIONAL INNER JOIN "ITEMS.sma" AS ITEMS ON
(ORDERS.ORDERID = ITEMS.ORDERID)

In the following query, however, only the ORDERS table can be dropped because the join that includes the LEFT OPTIONAL keywords is enclosed in parentheses:

SELECT ITEMS.ORDERID, ITEMS.PRICEQUOTE, ITEMS.QUANTITY

FROM "CUSTOMERS.sma" AS CUSTOMERS INNER JOIN ("ORDERS.sma" AS ORDERS LEFT OPTIONAL INNER JOIN "ITEMS.sma" AS ITEMS ON (ORDERS.ORDERID = ITEMS.ORDERID) ) ON
(CUSTOMERS.CUSTID = ORDERS.CUSTID)

In the following examples, A, B, C, and D are tables.

Consider the following query that includes the RIGHT OPTIONAL keywords:

A RIGHT OPTIONAL JOIN B RIGHT OPTIONAL JOIN C RIGHT OPTIONAL JOIN D

The Actuate SQL compiler interprets this query as:

((A RIGHT OPTIONAL JOIN B) RIGHT OPTIONAL JOIN C) RIGHT OPTIONAL JOIN D

Tables B, C, and D can be dropped from the query.

Consider the following query that includes the LEFT OPTIONAL keywords without parentheses:

A LEFT OPTIONAL JOIN B LEFT OPTIONAL JOIN C LEFT OPTIONAL JOIN D

The Actuate SQL compiler interprets this query as:

((A LEFT OPTIONAL JOIN B) LEFT OPTIONAL JOIN C) LEFT OPTIONAL JOIN D

Tables A, B, and C can be dropped from the query. It is not possible, however, to drop table C without dropping tables A and B, or to drop table B without dropping table A, without using parentheses.

Consider the following query that includes the LEFT OPTIONAL keywords with parentheses:

A LEFT OPTIONAL JOIN (B LEFT OPTIONAL JOIN (C LEFT OPTIONAL JOIN D))

Table C can be dropped from the query without dropping tables A and B. Table B can be dropped from the query without dropping table A.

Consider the following query that includes the OPTIONAL keyword without the LEFT or RIGHT modifier:

A OPTIONAL JOIN B OPTIONAL JOIN C OPTIONAL JOIN D

The Actuate SQL compiler interprets this query as:

((A OPTIONAL JOIN B) OPTIONAL JOIN C) OPTIONAL JOIN D

Any table or set of tables can be dropped from the query.

Copyright Actuate Corporation 2012