Progress query - derive table

Are you usin Crystal Reports tool for SQL -queries? Here is something that may help you.

Environment: Crystal Reports 8.5
SYMPTOM(s): ODBC error is generated when displaying or previewing a Crystal Report

The keywords LEFT OUTER JOIN were not found in an outer join escape
sequence

[DataDirect-Technologies][ODBC PROGRESS driver][PROGRESS]The keywords
LEFT OUTER JOIN were not found in an outer join escape sequence

Crystal Reports 8.5 used to create and preview the report

An Outer Join is used in the report

SQL query generated by Crystal Reports 8.5 contains "{oj(...)...}"
notation

Sample Crystal-generated SQL statement:

SELECT Customer1."cust-num", order1."order-num"
FROM {oj "PUB"."customer" customer1 INNER JOIN "PUB"."order1" order1
    ON customer1."cust-num" = order1."order-num"}

CAUSE:
The syntax for Outer Joins used in the SQL statement generated by
Crystal is not understood by the Progress ODBC driver. In this case,
Crystal Reports is adding quotation marks to the hyphens in the table
name. For Crystal Reports 8.5 to interpret the expected join syntax
for the Progress ODBC driver, the driver name must be referenced in a
Registry Key.

FIX:
Please note that Progress does not provide support for Crystal Reports
8.5 or lower.

Business Objects/Crystal Decisions provide the article "How to resolve
join syntax errors in Crystal Reports 8.5" that details the manner in
which this issue can be resolved. The following steps have been
extracted from this article to show the resolution from a
Progress-only perspective:

1) Close down Crystal Reports

2) Make a backup of the Registry.

3) Run Regedit

4) Create the following Key in the Registry:
HKEY_CURRENT_USER\Software\Seagate
Software\CrystalReports\DatabaseOptions\OuterJoin

5) Determine file name of the ODBC driver that was used to create the
ODBC Data Source:
i)  Start ODBC Data Source Administrator and select the Drivers tab
ii) Select the ODBC driver and check the File column

The file name will determine the type of driver and therefore the
required Outer Join syntax:
IVPRO***.DLL refer to SQL-89 drivers, which use OpenIngres syntax
PGPRO***.DLL/PGPRO****.DLL refer to SQL-92 drivers, which use
PlusEqual syntax

6) Create the appropriate Registry value based upon the ODBC driver
file name:

IVPRO***.DLL
Create a new String value called OpenIngres in the Outer Join Key
created in Step 4
Double-click on OpenIngres and enter the name of the driver file minus
".DLL" into the Value Data section

For example, the name of the MERANT 3.5 Progress SQL-89 driver is
IVPRO914.DLL so the Registry value will appear as:
Value name: OpenIngres
Value data: IVPRO914

PGPRO***.DLL/PGPRO****.DLL
Create a new String value called PlusEqual in the Outer Join Key
created in Step 4
Double-click on PlusEqual and enter the name of the driver file minus
".DLL" into the Value Data section

For example, the name of the DataDirect 4.20 32-BIT OpenEdge SQL
v10.0B driver is PGPRO1019.DLL so the Registry value will appear as:
Value name: PlusEqual
Value data: PGPRO1019

7) Close Regedit

8) Restart Crystal Reports