Consultor Eletrônico

Mixing inner and outer joins in Actuate

There is a bug in Actuate 2.010b when trying to design a report which
uses a combination of inner and outer joins in the same query.
This is a known issue which is being addressed in a future release.
The problem is caused by Actuate generating SQL which is not
compatible with Progress SQL syntax. You may get errors such as:

Basic Error 1011 Module: c:\Actuate\DEVWB\afc\db.bas Line 144
Database error
2 The error which occurred is database specific.

There is a workaround for this problem. You must override the SQL
generated by Actuate with the SQL that Progress expects. To do this
you must modify the ObtainSelectStatement() function of the data
stream as follows:

1. From the Developer Workbench structure pane, double-click on the
SQL DataStream component to bring up the Component Editor.
2. Choose the Methods tab.
3. Select function ObtainSelectStatemnt() As String and click
override. This will bring up the Method Editor.
4. You may insert the Progress SQL after the function declaration
using the variables FromClause and WhereClause.

For example:

Function ObtainSelectStatement( ) As String
FromClause = "Order LEFT OUTER JOIN Customer ON Customer.Cust-Num =
Order.Cust-Num INNER JOIN Order-Line ON Order.Order-Num =
Order-Line.Order-Num"
WhereClause = ""
ObtainSelectStatement = Super::ObtainSelectStatement( )
End Function

For examples of correct Progress SQL syntax, please see the Progress
book "SQL Guide and Reference", particularly the section on specifying
explicit joins beginning on page 3-18.
For more info on joining tables in general, see page 8-39 of the
Progress Programming Handbook. Note in particular section 8.10.5
which discusses mixing inner and outer joins.

Note that outer join functionality was not added to the Progress
database until version 8. The above example will not work against a
version 7 database.

Progress Software Technical Support Note # 17018