1875946 - SQL Query different after migrating reports based off an Oracle data source to a newer version of Crystal Reports

• SQL Query is different.
• SQL Query syntax changes after the report is migrated from an older version of Crystal Reports to a newer version, and this causing performance issues.
• Before, reports based off an Oracle data source added the tables linking in the WHERE clause, but in a newer version of Crystal Reports, the table linking is performed in the FROM clause. How to change the way the SQL Query is generated?

• SAP Crystal Reports 2008
• SAP Crystal Reports 2011
• SAP Crystal Reports 2013
• SAP Crystal Reports 2016
     
• Oracle 11g
• Oracle 12c

1. In an older version of Crystal Reports, create a report off Oracle database.
            
2. Notice the table linking is performed in the WHERE clause of the SQL Query.
   2.1 To see the SQL Query generated, select "Show SQL Query", under the menu "Database".
   2.2 Notice the in the SQL Query, the table linking is done in the WHERE clause like:
       
         SELECT "DEPT"."DNAME", "EMP"."ENAME"
         FROM   "SCOTT"."EMP" "EMP", "SCOTT"."DEPT" "DEPT"
         WHERE  ("EMP"."DEPTNO"="DEPT"."DEPTNO")       
                        
3. Open the same report in Crystal Reports 2008 and above.
            
4. Notice the table linking is added to the FROM clause of the SQL Query, instead of the WHERE clause.
   4.1 To see the SQL Query generated, select "Show SQL Query", under the menu "Database"
   4.2 Notice the in the SQL Query, the table linking is done in the FROM clause like:
   
         SELECT "DEPT"."DNAME", "EMP"."ENAME"
         FROM   "SCOTT"."EMP" "EMP", "SCOTT"."DEPT" "DEPT"     

• From Crystal Reports 9 and above, Crystal Reports complies to the standard ANSI SQL Syntax to link tables when generating the SQL Query.
• This is why the table linking is performed in the FROM clause of the SQL Query generated.
• In older version of SQL Query standard, the linking was performed in the WHERE clause.

• To modify how Crystal Reports generates the table linking in the SQL Query from the FROM clause to the WHERE clause, add the registry key: OracleJoinBuilder, sand set the value to the Oracle ODBC driver name used, or the native Oracle driver name: oci.dll
      
• Warning: This resolution involves making a modification to the Microsoft Registry. This is not an operation supported under normal procedures of SAP BusinessObjects. Therefore we recommend to create a backup of the System Registry before making any changes.

1. Open the Microsoft Registry Editor ( Regedit )
• In MS Windows, under the menu "Start", select "Run"
• Launch the Registry Editor by typing the command: Regedit.exe
         
2. Go to the following registry path depending on the version of Crystal Reports used:
        
• Crystal Reports 2008:
• HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Business Objects\Suite 12.0\Crystal Reports\Database
     
• Crystal Reports 2011, 2013, 2016
• HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database
             
• Note: For 32bit version of MS Windows, the path will be like: 
• HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\Crystal Reports\Database
       
3. Add to the Database key, the following keys: QueryBuilder, and JoinBuilder
• Right click on "Database", and select "New - Key", then enter the name of the key: QueryBuilder
• Right click on "QueryBuilder", and select "New - Key", then enter the name of the key: JoinBuilder
     
  Now, the registry path will be like: 
  
  HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\JoinBuilder
        
4. Add the string value: OracleJoinBuilder
• Right click on JoinBuilder, and select "New - String Value", and type the name: OracleJoinBuilder
       
5. Double-click on the "OracleJoinBuilder" string value and the enter the ODBC driver name for Oracle (SQORA32) as Value data for the string.
          
• For an ODBC Connection:
• Add the ODBC driver name under which the ODBC DSN created for the report was created.
         
  For example, if the ODBC DSN used for the report uses the Oracle client ODBC driver, then enter the driver name: SQORA32.DLL
  The ODBC driver name can be found in the Microsoft ODBC Administrator, under the tab "Drivers"
      
• For Oracle Native Connection:
• Add: OCI.DLL
       
• Note: If you add multiple ODBC driver names, seperate each driver name by a comma like:  SQORA32.DLL, OCI.DLL
      
6. Click OK
      
7. Now, when refreshing a report based off an Oracle data source in Crystal Reports, it will generates the link in the WHERE clause.

SQL Syntax, sql changes migrated crystal report, migrated crystal reports performance issue, performance issues crystal reports, CR 2008 SP5, crystal reports , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016