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 )

Additional Links:

Copyright Actuate Corporation 2012