Kbase 17095: Work Around for Actuate Joins
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
5/10/1998 |
|
Work Around for Actuate Joins
Remarks:
=======
* The Progress example places the outer joins before the inner joins,
this should be avoided as much as possible as it slows down the
query. Always try to put all inner joins before the outer joins
* in the example given the WhereClause is explicitly blanked out,
which is not alway possible as the WherClause includes
conditions with parameters to be received from a Progress program.
In that case the database error -2- remains until all joins
designed in the Query Editor are removed (thus leaving only the
tables that have no links).
* Linking Aliasses (also referred to as Correlation-name) is a bit
tricky since the original name and the alias name have to be
included (see example line with gl and with gl_sal_gl_job)
Workaround for inner/outer joins:
================================
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.
* First add all necessary tables in the SQL Query editor. Do NOT make
joins manually !
* Add all database fields to the column tab (fields for the report
NOT fields needed for the joins !)
* Add the parameter conditions
* Adapt the ObtainSelectStatement() to get the correct join statement.
Example:
=======
Function ObtainSelectStatement( ) As String
fromclause = "equipment JOIN eqpm_counter ON "
+ + "equipment.co_code = eqpm_counter.co_code and "
+ + "equipment.equipment = eqpm_counter.equipment "
+ + "JOIN system ON "
+ + "equipment.co_code = system.co_code "
+ + "LEFT OUTER JOIN eqpm_service_plan ON "
+ + "equipment.co_code = eqpm_service_plan.co_code and "
+ + "equipment.equipment = eqpm_service_plan.equipment"
+ + "LEFT OUTER JOIN gl gl_sal_gl_job ON "
+ + "equipment.co_code = gl_sal_gl_job.co_code and "
+ + "equipment.sal_gl_job = gl_sal_gl_job.code "
ObtainSelectStatement = Super::ObtainSelectStatement( )
[ Insert your code here ]
End Function
Progress Software Technical Support Note # 17095