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:
SELECT Customers.custid, Customers.customname, Customers.contact_last, Orders.orderid, Orders.custid, Orders.amount, Orders.shipbydate
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:
SELECT Customers.custid, Customers.customname, Customers.contact_last, Orders.orderid, Orders.custid, Orders.amount, Orders.shipbydate
FROM Customers.sma INNER JOIN Orders.sma
ON (Customers.custid = Orders.custid)
In this case, the Orders table is not dropped from the query:
SELECT COUNT(Customers.custid) AS CustomerCount
FROM Customers.sma INNER JOIN Orders.sma
ON (Customers.custid = Orders.custid)
The value of CustomerCount depends on whether the OPTIONAL keyword is applied to the Orders table in the CustomersOrders information object.

Additional Links:

Copyright Actuate Corporation 2012