When you create an information object, you indicate that a table in a join is optional using the OPTIONAL keyword. If you indicate that a table is optional and none of its columns appear in the query created by a report developer or business user (except in a join condition), the table is dropped from the optimized query.
The OPTIONAL keyword has no effect in queries created in the Information Object Query Builder.
For example, consider the following information object CustomersOrders:
Because no column from the Customers table appears in the query, and because the join in CustomersOrders includes the LEFT OPTIONAL keywords, the Customers table is dropped from the optimized query:
Now consider another Actuate SQL query created by a report developer or business user using CustomersOrders:
SELECT Customers.custid, Customers.contact_last
FROM CustomersOrders.iob
WHERE Customers.city = 'NYC'
No column from the Orders table appears in the query. But because the Orders table is not optional, it is not dropped from the query:
SELECT Customers.custid, Customers.contact_last
FROM Customers.sma INNER JOIN Orders.sma
ON (Customers.custid = Orders.custid)
WHERE Customers.city = 'NYC'
If you use the OPTIONAL keyword without the LEFT or RIGHT qualifier, it applies to both tables in the join.
The OPTIONAL keyword is ignored when it applies to:
A table whose columns appear in the query created by a report developer or business user, for example in the SELECT list or in the ORDER BY, GROUP BY, HAVING, or WHERE clauses.
The middle table in an information object, for example:
LEFT OPTIONAL INNER JOIN Items ON (Orders.orderid = Items.orderid)
In this information object, Orders is the middle table.
An information object that uses the OPTIONAL keyword cannot be joined to another information object. Therefore, an Actuate SQL query created by a report developer or business user cannot include more than one information object if that information object uses the OPTIONAL keyword.
Using the OPTIONAL keyword with a computed field
Do not define a computed field in an information object that contains the OPTIONAL keyword. Instead, define the computed field in a lower level information object.
For example, consider the information object MyInformationObject:
SELECT dbo_CUSTOMERS.CUSTID AS CUSTID, dbo_CUSTOMERS.CONTACT_FIRST AS CONTACT_FIRST, dbo_CUSTOMERS.CONTACT_LAST AS CONTACT_LAST, dbo_CUSTOMERS.CITY AS CITY, dbo_ORDERS.SHIPBYDATE AS SHIPBYDATE, dbo_ORDERS.FORECASTSHIPDATE AS FORECASTSHIPDATE, dbo_CUSTOMERS.ADDRESS AS ADDRESS, ( dbo_ITEMS.PRICEQUOTE * dbo_ITEMS.QUANTITY ) AS Total
FROM "dbo.CUSTOMERS.sma" AS dbo_CUSTOMERS
OPTIONAL INNER JOIN "dbo.ORDERS.sma" AS dbo_ORDERS
ON ( dbo_CUSTOMERS.CUSTID=dbo_ORDERS.CUSTID )
OPTIONAL INNER JOIN "dbo.ITEMS.sma" AS dbo_ITEMS
ON ( dbo_ORDERS.ORDERID=dbo_ITEMS.ORDERID )
MyInformationObject defines the computed field Total and also contains the OPTIONAL keyword.
Now consider the following Actuate SQL query created by a report developer or business user using MyInformationObject:
SELECT MyInformationObject.CUSTID AS CUSTID, MyInformationObject.CONTACT_FIRST AS CONTACT_FIRST, MyInformationObject.CITY AS CITY, MyInformationObject.CONTACT_LAST AS CONTACT_LAST
FROM "MyInformationObject.iob" AS MyInformationObject
The ORDERS and ITEMS tables are not dropped from the query even though the OPTIONAL keyword is applied to both tables in MyInformationObject and the SELECT clause does not contain columns from either table. The tables are not dropped because in MyInformationObject the columns ITEMS.PRICEQUOTE and ITEMS.QUANTITY are used in a computation outside the join condition.
To avoid this situation, define the computed field in a lower level information object such as ITEMS.iob. MyInformationObject then contains the following query:
SELECT dbo_CUSTOMERS.CUSTID AS CUSTID, dbo_CUSTOMERS.CONTACT_FIRST AS CONTACT_FIRST, dbo_CUSTOMERS.CONTACT_LAST AS CONTACT_LAST, dbo_CUSTOMERS.CITY AS CITY, dbo_ORDERS.SHIPBYDATE AS SHIPBYDATE, dbo_ORDERS.FORECASTSHIPDATE AS FORECASTSHIPDATE, dbo_CUSTOMERS.ADDRESS AS ADDRESS, ITEMS.Total AS Total
FROM "dbo.CUSTOMERS.sma" AS dbo_CUSTOMERS
OPTIONAL INNER JOIN "dbo.ORDERS.sma" AS dbo_ORDERS
ON ( dbo_CUSTOMERS.CUSTID=dbo_ORDERS.CUSTID )
OPTIONAL INNER JOIN "ITEMS.iob" AS ITEMS
ON ( dbo_ORDERS.ORDERID=ITEMS.ORDERID )
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:
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:
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.
Using the OPTIONAL keyword with aggregate functions
If a query created by a report developer or business user contains the function COUNT(*), the OPTIONAL keyword, if it appears in the information object, is ignored. If a query contains another aggregate function, for example SUM or COUNT(column), the value returned by the aggregate function depends on whether the information object includes the OPTIONAL keyword. For example, consider the following Actuate SQL query created by a report developer or business user using the CustomersOrders information object:
SELECT COUNT(Customers.custid) AS CustomerCount
FROM CustomersOrders.iob
In the first case, consider the following information object CustomersOrders, which applies the OPTIONAL keyword to the Orders table:
FROM Customers.sma RIGHT OPTIONAL INNER JOIN Orders.sma
ON (Customers.custid = Orders.custid)
Because no column from the Orders table appears in the query and because the join in CustomersOrders includes the RIGHT OPTIONAL keywords, the Orders table is dropped from the optimized query:
SELECT COUNT(Customers.custid) AS CustomerCount
FROM Customers.sma
In the second case, consider the following information object CustomersOrders, which does not apply the OPTIONAL keyword to the Orders table:
A table whose columns appear in the query created by a report developer or business user, for example in the SELECT list or in the ORDER BY, GROUP BY, HAVING, or WHERE clauses.