Although SQL Expressions with SELECT statements are not supported, they can work if they only return a single value.įor example, Maximum, Minimum and Count are functions that return a single value.ĭepending on the database used, it may be necessary to enclose the SELECT statement with parentheses.įor example, a Microsoft SQL Server database requires the SELECT statement to be enclosed in parentheses while a Microsoft Access database does not.The exact wording of the message depends upon the database in use.įor more robust usage of SQL in Crystal Reports 9 and later, use a command object. "Error in compiling SQL expression"Syntax error. If a SELECT statement is included in an SQL Expression field, an error message, similar to the following, may appear: Generally, an SQL Expression field cannot contain a SELECT statement because Crystal Reports can only process one SELECT statement per main report. Using a SELECT statement in a SQL Expression field is not supported.SQL Expression Fields and SELECT Statements.This results in a faster processing time. The advantage of the SQL Expression field is that the processing of the request will be done on the database server instead of in the Crystal Reports Designer. Often an SQL Expression field and a Crystal Reports formula field can accomplish the same goal.įor example, a report design requirement is to display the first 3 letters of the first name of an employee.Ī formula field or an SQL Expression field can fulfill this requirement.SQL Expression field versus a Crystal Reports formula field.Additional database functions may be available to you, depending on your database, but they will not turn blue. NOTE: If Crystal Reports recognizes the function name it will turn blue in the 'SQL Expression Editor' dialog box.If the function tree is not visible, press 'ALT + U'. This list is available in the 'Function Tree' of the 'SQL Expression Editor' dialog box. The list of available functions depends on the type of database in use. You can use SQL Expression fields to perform pre-defined functions on database fields.They are useful in optimizing report performance because the tasks they execute are performed on the database server. SQL Expression fields are similar to formula fields, but they are written in Structured Query Language (SQL).
0 kommentar(er)
