1976296 - Crystal Reports 2008 is generating a SQL-statement which is not compatible to Sybase databases

SAP Knowledge Base Article - Public

1976296 - Crystal Reports 2008 is generating a SQL-statement which is not compatible to Sybase databases

Symptom

Crystal Reports 2008 generates SQL statement  

SELECT CUSTOMER.C_CUSTKEY, ORDERS.O_ORDERKEY, ORDERS.O_ORDERSTATUS
FROM   tpcsf.tpcsf000067.CUSTOMER CUSTOMER LEFT OUTER JOIN tpcsf.tpcsf000067.ORDERS ORDERS ON CUSTOMER.C_CUSTKEY=ORDERS.O_CUSTKEY
WHERE  ORDERS.O_ORDERSTATUS='F'

which incorrectly combines a WHERE clause with a JOIN in the FROM clause

Environment

  • SAP Crystal Reports 2008
  • SAP Sybase ASE ODBC driver

Reproducing the Issue

  1. Launch Crystal Reports 2008
  2. Connect to Sybase ASE via ODBC
  3. Select two tables for the report
  4. Join the tables with a LEFT OUTER JOIN
  5. Add a record selection which filters on the right table in the LEFT OUTER JOIN
  6. Notice that a WHERE clause is generated

Cause

Crystal Reports 2008 is not recognizing the Sybase ODBC driver

Resolution

CAUTION

The following resolution involves editing the registry. Using Registry Editor incorrectly can cause serious problems that may require you to reinstall the Windows operating system. Use Registry Editor at your own risk. For Information on How to Edit the Registry Key View the Changing Keys And Values online Help topic in Registry Editor (Regedit.exe).It is strongly advised that you make a backup copy of the registry files before you edit the registry.

  1. Launch the Registry Editor via Start | Run > regedit
  2. Open registry key HKEY_CURRENT_USER\Software\Business Objects\Suite 12.0\Crystal Reports\Database
  3. Create new key HKEY_CURRENT_USER\Software\Business Objects\Suite 12.0\Crystal Reports\Database\QueryBuilder
  4. Create new key HKEY_CURRENT_USER\Software\Business Objects\Suite 12.0\Crystal Reports\Database\QueryBuilder\JoinBuilder
  5. Create new String Value StarEqualJoinBuilder
  6. Set the value of StarEqualJoinBuilder to ODBC3Sybase
  7. Restart Crystal Reports 2008
  8. Notice that the following type of query is now generated which contains both the join and filter in the WHERE clause

 SELECT CUSTOMER.C_CUSTKEY, ORDERS.O_ORDERKEY, ORDERS.O_ORDERSTATUS
 FROM   tpcsf.tpcsf000067.CUSTOMER CUSTOMER, tpcsf.tpcsf000067.ORDERS ORDERS
 WHERE  (CUSTOMER.C_CUSTKEY*=ORDERS.O_CUSTKEY) AND ORDERS.O_ORDERSTATUS='F'


 

 

Keywords

  • CR
  • Syntax
  • Query
  • Standard Query Language
  • Sybase Adaptive Server Enterprise
, KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

Crystal Reports 2008 V1