1217871 - What is the intended use of 'SQL Expression Fields' in Crystal Reports?

• What is an SQL Expression?
• In Crystal Reports, 'SQL Expression Fields' are an available option of the 'Field Explorer' dialog box. What is the intended use of the SQL Expression Fields?

• SAP Crystal Reports 2013
• SAP Crystal Reports 2016
• SAP Crystal Reports 2020

• SQL Expression Field definition
      
• SQL Expression fields are similar to formula fields, but they are written in Structured Query Language (SQL). They are useful in optimizing report performance because the tasks they execute are performed on the database server.
     
• You can use SQL Expression fields to perform pre-defined functions on database fields. The list of available functions depends on the type of database in use. This list is available in the 'Function Tree' of the 'SQL Expression Editor' dialog box. If the function tree is not visible, press 'ALT + U'.
     
• NOTE: If Crystal Reports recognizes the function name it will turn blue in the 'SQL Expression Editor' dialog box. Additional database functions may be available to you, depending on your database, but they will not turn blue.
      
      
• SQL Expression field versus a Crystal Reports formula field
         
• Often an SQL Expression field and a Crystal Reports formula field can accomplish the same goal.
     
  For example, a report design requirement is to display the first 3 letters of the first name of an employee.
  A formula field or an SQL Expression field can fulfill this requirement. 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. This results in a faster processing time.
  
  SQL Expression:  
  
  {fn LEFT("Employee"."First Name",3)}
    
  
  Crystal Reports formula: 
       
  left({Employee.First Name},3)
  
             
• SQL Expression Fields and SELECT Statements
       
• Using a SELECT statement in a SQL Expression field is not supported. Generally, an SQL Expression field cannot contain a SELECT statement because Crystal Reports can only process one SELECT statement per main report. If a SELECT statement is included in an SQL Expression field, an error message, similar to the following, may appear:
  
  "Error in compiling SQL expression"Syntax error. In query expression 'select'
  
  The exact wording of the message depends upon the database in use.
  For more robust usage of SQL in Crystal Reports 9 and later, use a command object.
       
• Although SQL Expressions with SELECT statements are not supported, they can work if they only return a single value.
  For example, Maximum, Minimum and Count are functions that return a single value.
  
  Depending on the database used, it may be necessary to enclose the SELECT statement with parentheses.
  For example, a Microsoft SQL Server database requires the SELECT statement to be enclosed in parentheses while a Microsoft Access database does not. Consult your database documentation for additional information.
        
       
• Database Functions Not Pre-Defined in Crystal Reports
      
• Database functions that are not pre-defined in Crystal Reports can be used in SQL Expression fields.
  For example: 
   
  LOWER("table"."field")
     
  "LOWER" is not a pre-defined function in Crystal Reports and, therefore, will not turn blue in the 'SQL Expression Editor'. However, the syntax will be accepted and you can now insert this field into your report.
     
     
• More Information
     
• For more information on SQL Expression fields, refer to the Crystal Reports Online Help by pressing the 'F1' key.

USE SQL EXPRESSION EDITOR ERROR IN COMPILING SQL HOW TO USE SQL QUERIES Crystal Reports SQL Expression Fields SELECT statement error Database functions , c2016184 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020