1181478 - Crystal Reports XI does not generate the expected SQL query when using indexes

Symptom

Crystal Reports generates the SQL using joins rather than indexes.
Selecting the option 'Use Indexes or Server for Speed' and reporting off 2 tables which contain indexes, Crystal Reports creates a query that, for example, looks like:

SELECT "DEPT"."DEPTNO", "EMP"."EMPNO"
FROM "SCOTT"."EMP" "EMP" INNER JOIN "SCOTT"."DEPT" "DEPT" ON "EMP"."DEPTNO"="DEPT"."DEPTNO"

rather than:

SELECT "DEPT"."DEPTNO", "EMP"."EMPNO"
FROM "SCOTT"."EMP" "EMP", "SCOTT"."DEPT" "DEPT"
WHERE ("EMP"."DEPTNO"="DEPT"."DEPTNO")

Cause

Since Crystal Reports XI, the join syntax for Oracle has been changed to comply with ANSI SQL/99 standard for Oracle versions 9 and 10.

Starting with Oracle9, Oracle supports ANSI compliant join syntax, one of which is the separation of the join condition from the WHERE clause.

For more information, please refer to the Oracle documentation "ANSI/ISO SQL Support In Oracle9i" available on:
http://www.oracle-base.com/articles/9i/ANSIISOSQLSupport.php

Resolution

This is by design. A workaround to the behaviour is to use the "Add Command" within Database Expert to write freehand SQL.

Keywords

Crystal Reports XI does not generate the expected SQL query when using indexes , 1057856 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

SAP Crystal Reports XI ; SAP Crystal Reports XI R2