Kbase 18979: ODBC error when attempting to display a report containing an Outer Join created in Crystal Reports 8
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
5/16/2005 |
|
Status: Verified
FACT(s) (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. Please refer to solution P29232 for information on what versions of Crystal Reports are supported by Progress.
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