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?
- Note: Images and data in this SAP Knowledge Base Article is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.
Environment
- SAP Crystal Reports 2011
- SAP Crystal Reports 2013
- SAP Crystal Reports 2016
- SAP Crystal Reports 2020
Resolution
-
In Crystal Reports, you can modify how the syntax of the SQL Query is generated for: Query Join, Outer Join, Boolean Values, Date Time, Stored Procedure Call, Unicode Conversion, ... by adding the ODBC Driver Name in the registry key corresponding to the syntax you want to change.
WARNING The following resolution involves editing the Microsoft Registry. Using the Microsoft Registry Editor incorrectly can cause serious problems. Use the Microsoft Registry Editor at your own risk. For more information see the SAP Knowledge Base Article 1323322 - In the List of Registry Keys Table located at the end of this article, find the registry key that can change the desired SQL Query syntax.
- Open the Microsoft ODBC Data Source Administrator.
- Under the tab "System", find the ODBC DSN used to connect to the database, and under the column "Driver," take note of the ODBC Driver name.
- Under the tab "Drivers", find the ODBC Driver Name, and scroll to the right to take note of the File name. ( ODBC Driver File Name )
Note: This is the ODBC Driver File name you will need to add to the registry key corresponding to the syntax you want to change.
- Open the Microsoft Registry Editor. ( regedit )
- Navigate to the Registry Key path corresponding to the syntax you want to change.
Note: The end of the path will most likely not exist. If it is the case, add the additional keys.
For example navigate to: HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\JoinBuilder
- Add a String Value and enter the Registry Key Name corresponding to the syntax you want to change.
For example: Add the registry key: SQLServerJoinBuilder
- Add to the Registry Key, the ODBC Driver File Name you took note of in step 4. ( without the .DLL extension )
Note: If there are multiple ODBC Driver File Name, separate each of the driver name by a comma.
For example: Add the ODBC Driver File Name: SQLNCLI11 ( This is the MS SQL Server Native ODBC Driver 11 File Name )
- Close the Microsoft Registry Editor.
- Start Crystal Reports, and when creating, or refreshing a report, the syntax generated for the SQL Query will change according to the registry key added.
- The table below list all the registry keys that control how Crystal Reports generates the SQL Query when connecting to a data source using an ODBC connection or a Native connection.
Query Builder Generates Tables JOIN syntax. Registry path HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\JoinBuilder
Registry Keys For drivers that support SQLServerJoinBuilder SQL Server ANSI join syntax (FROM A LEFT OUTER B ON A.a = B.b) InformixJoinBuilder Informix join syntax ( FROM A, OUTER B WHERE A.a = B.b) OracleJoinBuilder Oracle join syntax ( FROM A, B WHERE A.a(+) = B.b) OracleJoinBuilder2 Oracle ANSI join syntax (FROM A LEFT OUTER B ON A.a = B.b) except the FULL OUTER join SybaseJoinBuilder Sybase ANSI join syntax (FROM A LEFT OUTER B ON A.a = B.b) except the FULL OUTER join. DB2JoinBuilder DB2 ANSI join syntax (FROM A LEFT OUTER B ON A.a = B.b) LotusNotesJoinBuilder 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 Sybase Star-Equal join syntax (FROM A, B WHERE A.a *= B.b) Valid values beside ODBC driver names
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. Registry path HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\NameBuilder Registry Keys For drivers that support SQLServerNameBuilder SQL Server naming conventions ("<StoredProcedureName>";<Number>) InformixNameBuilder Informix naming conventions (Table qualifier appears at the beginning of the fully qualified table name) OracleNameBuilder Oracle naming conventions ("<Package Name>"."<Stored Procedure Name>") SybaseNameBuilder Sybase naming conventions. AccessNameBuilder Access naming conventions (Quotations are allowed in table names and kept as they are) DB2NameBuilder DB2 naming conventions. DoubleQuotation Use double quotations to quote names. ( " " ) BackQuotation Use back quotations to quote names. ( ` ` ) SquareBracket Use square brackets to quote names. ( [ ] ) Dot Use dot as qualifier separator character. ( . ) AtSign Use At sign as qualifier separator character. ( @ ) Colon 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. Registry path HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\ValueBuilder Registry Keys For drivers that support SQLServerValueBuilder SQL Server value building conventions (Unicode strings prefixed with "N") SQLServer65ValueBuilder SQL Server 6.5 value building conventions (Unicode strings are not prefixed with "N") InformixValueBuilder Informix value building conventions (Boolean values are 'T' and 'F') OracleValueBuilder Oracle value building conventions (Using TO_DATE function to convert date to string) OracleValueBuilder2 Oracle value building conventions (Unicode strings are prefixed with "N") SybaseValueBuilder Sybase value building conventions (Unicode strings are prefixed with "N") AccessValueBuilder Access value building conventions (Boolean values are 'TRUE' and 'FALSE') DAOAccessValueBuilder DAO Access value building conventions (Wild cards are * and ?) DB2ValueBuilder DB2 value building conventions. Outer Join Escape Sequence Generates the syntax for ODBC drivers using the outer join syntax in its SQL syntax. Registry path HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\OuterJoinEscSeq Registry Keys For drivers MSOuterJoinEscSeq That support Microsoft ODBC {oj ..} join escape sequence. NoOuterJoinEscSeq That does not support any join escape sequence. Date Time Escape Sequence Generates the syntax for ODBC drivers using date time identifiers like {d"} for date. Registry path HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\DateTimeEscSeq Registry Keys For drivers that support MSDateTimeEscSeq1 Microsoft ODBC {ts ..}, {d ..} and {t ..} date-time escape sequences. MSDateTimeEscSeq2 Microsoft Access # .. # date-time escape sequence. Stored Procedure Clause Builder Generates the syntax for calling stored procedures based on the ODBC drivers in use. Registry path HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\StoredProcedureClauseBuilder Registry Keys For drivers that support DefaultStoredProcedureClauseBuilder Default procedure calling syntax: {CALL <procedure name>(<parameters list>)} SQLServerStoredProcedureClauseBuilder Microsoft SQL Server <procedure name><parameters list> procedure calling syntax. InformixStoredProcedureClauseBuilder Informix EXECUTE PROCEDURE <procedure name>(<parameters list>) calling syntax. OracleStoredProcedureClauseBuilder Oracle BEGIN <procedure name>(<parameters list>) END calling syntax. SybaseStoredProcedureClauseBuilder Sybase EXEC <procedure name><parameters list> procedure calling syntax. AccessStoredProcedureClauseBuilder Microsoft Access stored procedure calling syntax. DB2StoredProcedureClauseBuilder DB2 stored procedure calling syntax. PSFTStoredProcedureClauseBuilder PeopleSoft stored procedure calling syntax. Always Unicode Conversion Used for ODBC Drivers which require Unicode strings to be prefixed with "N". Registry path HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\UnicodeConversionOptions Registry Key For drivers that support AlwaysUnicodeConversion Unicode strings to be prefixed with "N". Sybase Where Clause Builder Used for Sybase ODBC Drivers which require the Sybase 'where' clause. Registry path HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\WhereClauseBuilder Registry Key For drivers that support SybaseWhereClauseBuilder 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 Business View Manager , How To