1217820 - Registry settings to configure how the SQL Query is generated in Crystal Reports

SAP Knowledge Base Article - Public

1217820 - Registry settings to configure how the SQL Query is generated in Crystal Reports

Symptom

  • How to change how the SQL Query is generated in Crystal Reports?
  • What registry key settings are available to configure how the SQL Query is generated when connecting to a data source via an ODBC or Native connection in Crystal Reports?

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016

Resolution

  • The following registry key control how Crystal Reports generates the SQL Query when connecting to a data source using an ODBC connection or a Native connection. 
     
    WARNING The following resolution involves editing the Microsoft Registry. Using the MS Registry Editor incorrectly can cause serious problems. Use the MS Registry Editor at your own risk. For more information see the SAP Knowledge Base Article 1323322
         
           
  • QUERY BUILDER
    Generates Tables JOIN syntax.
      
    • The registry key is located in the following path:
        
      • For Crystal Reports 2008: 
          
        HKEY_CURRENT_USER\SOFTWARE\Business Objects\Suite12.0\Crystal Reports\Database\QueryBuilder\JoinBuilder
              
      • For Crystal Reports 2011, 2013, 2016:
           
        HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\JoinBuilder
               
          
    • SQLServerJoinBuilder
      List of the drivers that support SQL Server ANSI join syntax (FROM A LEFT OUTER B ON A.a = B.b)
         
    • InformixJoinBuilder
      List of the drivers that support Informix join syntax ( FROM A, OUTER B WHERE A.a = B.b)
        
    • OracleJoinBuilder
      List of the drivers that support Oracle join syntax ( FROM A, B WHERE A.a(+) = B.b)
           
    • OracleJoinBuilder2
      List of the drivers that support Oracle ANSI join syntax (FROM A LEFT OUTER B ON A.a = B.b) except the FULL OUTER join            
           
    • SybaseJoinBuilder
      List of the drivers that support Sybase ANSI join syntax (FROM A LEFT OUTER B ON A.a = B.b) except the FULL OUTER join.
              
    • DB2JoinBuilder
      List of the drivers that support DB2 ANSI join syntax (FROM A LEFT OUTER B ON A.a = B.b)
          
    • LotusNotesJoinBuilder
      List of the drivers that support Lotus Notes join syntax (inner joins: FROM A, B WHERE A.a = B.b - left outer joins: FROM A LEFT OUTER B ON A.a = B.b - right outer joins: FROM B LEFT OUTER A ON A.a = B.b)
              
    • StarEqualJoinBuilder
      List of the drivers that support Sybase Star-Equal join syntax (FROM A, B WHERE A.a *= B.b)
                  
    • Other than the ODBC driver names, below is a list of other valid values for the JoinSyntax.
      NativeSQLServer, ODBC3SQLServer, NativeSQLServer65, ODBC3SQLServer65, NativeOracle, ODBC3Oracle, ODBC3Oracle9, NativeOracle9, ODBC3Oracle10, NativeOracle10, NativeDB2, ODBC3DB2, NativeLotusNotes, ODBC3LotusNotes, ADODefault, ADODOTNET, ODBC2Default, ODBC3Default, ODBC3PostgreSQL, ODBC3MySQL, JDBCOracle, JDBCOracle9, JDBCOracle10, JDBCTeraData, JDBCLiquidData   
                
              
           
  • NAME BUILDER
    Generates the syntax for specific naming conventions of ODBC drivers.
           
    • The registry key is located in the following path:

      • For Crystal Reports 2008: 
          
        HKEY_CURRENT_USER\SOFTWARE\Business Objects\Suite12.0\Crystal Reports\Database\QueryBuilder\NameBuilder
              
      • For Crystal Reports 2011, 2013, 2016:
           
        HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\NameBuilder
                
            
    • SQLServerNameBuilder
      List of the drivers that support SQL Server naming conventions ("<StoredProcedureName>";<Number>)
          
    • InformixNameBuilder
      List of the drivers that support Informix naming conventions (Table qualifier appears at the beginning of the fully qualified table name)
          
    • OracleNameBuilder
      List of the drivers that support Oracle naming conventions ("<Package Name>"."<Stored Procedure Name>")
        
    • SybaseNameBuilder
      List of the drivers that support Sybase naming conventions.
         
    • AccessNameBuilder
      List of the drivers that support Access naming conventions (Quotations are allowed in table names and kept as they are)
         
    • DB2NameBuilder
      List of the drivers that support DB2 naming conventions.
         
    • DoubleQuotation
      List of the drivers that use double quotations " " to quote names.
         
    • BackQuotation
      List of the drivers that use back quotations ` ` to quote names.
         
    • SquareBracket
      List of the drivers that use square brackets [ ] to quote names.
          
    • Dot
      List of the drivers that use dot as qualifier separator character.
           
    • AtSign
      List of the drivers that use At sign as qualifier separator character.
        
    • Colon
      List of the drivers that use colon as qualifier separator character.
        
        
        
  • VALUE BUILDER
    Generates the syntax for specific unicode drivers as well as other syntax related to Boolean values, date-time and wild card search characters of ODBC drivers.
        
    • The registry key is located in the following path:

      • For Crystal Reports 2008: 
          
        HKEY_CURRENT_USER\SOFTWARE\Business Objects\Suite12.0\Crystal Reports\Database\QueryBuilder\ValueBuilder
              
      • For Crystal Reports 2011, 2013, 2016:
           
        HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\ValueBuilder
                   
            
    • SQLServerValueBuilder
      List of the drivers that support SQL Server value building conventions (Unicode strings prefixed with "N")
         
    • SQLServer65ValueBuilder
      List of the drivers that support SQL Server 6.5 value building conventions (Unicode strings are not prefixed with "N")
            
    • InformixValueBuilder
      List of the drivers that support Informix value building conventions (Boolean values are 'T' and 'F')
         
    • OracleValueBuilder
      List of the drivers that support Oracle value building conventions (Using TO_DATE function to convert date to string)
          
    • OracleValueBuilder2
      List of the drivers that support Oracle value building conventions (Unicode strings are prefixed with "N")
        
    • SybaseValueBuilder
      List of the drivers that support Sybase value building conventions (Unicode strings are prefixed with "N")
          
    • AccessValueBuilder
      List of the drivers that support Access value building conventions (Boolean values are 'TRUE' and 'FALSE')
        
    • DAOAccessValueBuilder
      List of the drivers that support DAO Access value building conventions (Wild cards are * and ?)
         
    • DB2ValueBuilder
      List of the drivers that support DB2 value building conventions.
          
       
         
  • OUTER JOIN ESCAPE SEQUENCE
    Generates the syntax for ODBC drivers using the outer join syntax in its SQL syntax.
          
    • The registry key is located in the following path:

      • For Crystal Reports 2008: 
          
        HKEY_CURRENT_USER\SOFTWARE\Business Objects\Suite12.0\Crystal Reports\Database\QueryBuilder\OuterJoinEscSeq
              
      • For Crystal Reports 2011, 2013, 2016:
           
        HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\OuterJoinEscSeq
         
             
    • MSOuterJoinEscSeq
      List of the drivers that support Microsoft ODBC {oj ..} join escape sequence.
           
    • NoOuterJoinEscSeq
      List of the drivers that do not support any join escape sequence.
       
       
        
  • DATE TIME ESCAPE SEQUENCE
    Generates the syntax for ODBC drivers using date time identifiers like {d"} for date.
        
    • The registry key is located in the following path:

      • For Crystal Reports 2008: 
          
        HKEY_CURRENT_USER\SOFTWARE\Business Objects\Suite12.0\Crystal Reports\Database\QueryBuilder\DateTimeEscSeq   
                   
      • For Crystal Reports 2011, 2013, 2016:
           
        HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\DateTimeEscSeq
             
            
    • MSDateTimeEscSeq1
      List of the drivers that support Microsoft ODBC {ts ..}, {d ..} and {t ..} date-time escape sequences.
           
    • MSDateTimeEscSeq2
      List of the drivers that support Microsoft Access # .. # date-time escape sequence.
       
       
        
  • STORED PROCEDURE CLAUSE BUILDER
    Generates the syntax for calling stored procedures based on the ODBC drivers in use.
           
    • The registry key is located in the following path:

      • For Crystal Reports 2008: 
          
        HKEY_CURRENT_USER\SOFTWARE\Business Objects\Suite12.0\Crystal Reports\Database\QueryBuilder\StoredProcedureClauseBuilder   
                   
      • For Crystal Reports 2011, 2013, 2016:
           
        HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\StoredProcedureClauseBuilder
         
           
    • DefaultStoredProcedureClauseBuilder
      List of the drivers that support default {CALL <procedure name>(<parameters list>)} procedure calling syntax.
          
    • SQLServerStoredProcedureClauseBuilder
      List of the drivers that support Microsoft SQL Server <procedure name><parameters list> procedure calling syntax.
          
    • InformixStoredProcedureClauseBuilder
      List of the drivers that support Informix EXECUTE PROCEDURE <procedure name>(<parameters list>) calling syntax.
           
    • OracleStoredProcedureClauseBuilder
      List of the drivers that support Oracle BEGIN <procedure name>(<parameters list>) END calling syntax.
          
    • SybaseStoredProcedureClauseBuilder
      List of the drivers that support Sybase EXEC <procedure name><parameters list> procedure calling syntax.
                 
    • AccessStoredProcedureClauseBuilder
      List of the drivers that support Microsoft Access stored procedure calling syntax.
             
    • DB2StoredProcedureClauseBuilder
      List of the drivers that support DB2 stored procedure calling syntax.
            
    • PSFTStoredProcedureClauseBuilder
      List of the drivers that support PeopleSoft stored procedure calling syntax.
                  
        
            
  • ALWAYS UNICODE CONVERSION
    Used for ODBC Drivers which require Unicode strings to be prefixed with "N".
        
    • The registry key is located in the following path:

      • For Crystal Reports 2008: 
          
        HKEY_CURRENT_USER\SOFTWARE\Business Objects\Suite12.0\Crystal Reports\Database\QueryBuilder\UnicodeConversionOptions   
                   
      • For Crystal Reports 2011, 2013, 2016:
           
        HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\UnicodeConversionOptions
             
                 
    • AlwaysUnicodeConversion
      List of the drivers that always require Unicode strings to be prefixed with "N".
             
         
           
  • SYBASE WHERE CLAUSE BUILDER
    Used for Sybase ODBC Drivers which require the Sybase 'where' clause.
                 
    • The registry key is located in the following path:  
          
      • For Crystal Reports 2008: 
          
        HKEY_CURRENT_USER\SOFTWARE\Business Objects\Suite12.0\Crystal Reports\Database\QueryBuilder\WhereClauseBuilder  
                   
      • For Crystal Reports 2011, 2013, 2016:
           
        HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\WhereClauseBuilder
         
            
    • SybaseWhereClauseBuilder
      List of the drivers that support Sybase 'where' clause building conventions.

Keywords

QUERY BUILDER QUERYBUILDER REGISTRY SETTINGS KEY SUBKEY LIST DRIVERS ODBC Crystal Reports Query Builder registry settings Registry keys , c2016359, Unknown column '...' in field list , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

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