What is a SQL Expression?

Many of us have questions about SQL Expressions, however, Crystal Decisions documentation is noticeably scarce on this topic. I hope that the following FAQ addresses your primary questions:

Q: What is a SQL Expression?

A: A SQL Expression is a user defined field (such as a Formula or Parameter field). The ability to create a SQL Expression was added in Crystal Reports 7.


Q: How is a SQL Expression different than a Formula field?

A: There are many ways in which a SQL Expression differs from a Formula field:

1) The only functions available to a SQL Expression are database-specific functions as opposed to the common Crystal or Basic Syntax functions which are available in a Formula. The exact database functions available to you are determined by your database (SQL Server, Oracle, etc...) and your database driver (Native, CR-supplied ODBC and Third Party ODBC drivers).

2) The only fields available for use in a SQL Expression are true database table fields. You cannot incorporate Crystal functions, Parameters or Special Fields into a SQL Expression like you can in a Formula.

3) You cannot use Crystal or Basic Syntax (If-Then-Else statements, for example) in a SQL Expression. You can only use the database-native function syntax such as the Oracle Function [color blue]ADD_MONTHS(D,N)[/color] which returns the Date (D) plus N months.

4) SQL Expressions are always handled on the database whereas Formulas are handled on the client. If you were to review your Report SQL (Database|Show SQL Query) you would see that the SQL Expressions are present in the Select clause of your SQL Statement.


Q: Why would I want to use a SQL Expression instead of a Formula?

A: The best reason I can think of for using a SQL Expression instead of a Formula field is to improve Report performance. Let's look at the following example:

Scenario:
You have been tasked to build a report with From and To Date Parameters. Since your users hate typing Dates in standard Crystal Syntax ([color blue]Date(YYYY, MM, DD)[/color]), your boss has mandated that the Date values entered be Strings in the 'MM/DD/YYYY' format.

You have created {?From_Date} and {?To_Date} parameters based on String Values and masked, as requested. Unfortunately, your Date fields are actually Date Values and are incompatible with String values. You can handle this a number of different ways in Record Selection Criteria Directly or Indirectly with a Formula:

[color green]//Record Selection Criteria - Direct Method 1[/color]
{Table.Date_Field} [color blue]In Date(Right([/color]{?From_Date}[color blue],4), Left([/color]{?From_Date}[color blue],2), Mid([/color]{?From_Date}[color blue,4,2)) to Date(Right([/color]{?To_Date}[color blue],4), Left([/color]{?To_Date}[color blue],2), Mid([/color]{?To_Date}[color blue],4,2))[/color]

[color green]//Record Selection Criteria - Direct Method 2[/color]
[color blue]ToText([/color]{Table.Date_Field},'MM/dd/yyyy'[color blue]) In [/color]{?From_Date}[color blue] to [/color]{?To_Date}

[color green]//Record Selection Criteria - Indirect Method[/color]
{@Date}[color blue] In [/color]{?From_Date}[color blue] to [/color]{?To_Date}

Where @Date is a formula defined as follows:

[color green]//@Date[/color]
[color blue]ToText(ToText([/color]{Table.Date_Field},'MM/dd/yyyy')[color blue])[/color]

Whichever method you choose, your Record Selection Criteria won't be evaluated until all records have been returned. Depending both on the size and integrity of your database and your machine's processor, RAM and Hard Drive space, you could be in for a very long wait. Please note that the following SQL Statement (based on the last example) does not contain a Where Clause:

SELECT
"TABLE"."DATE_FIELD"
FROM
"DATABASE"."TABLE" "TABLE_NAME"

If you were to create a SQL Expression as follows (Oracle 8i Native Driver in this example):

[color green]//%Date[/color]
[color blue]TO_CHAR([/color]"TABLE"."DATE_FIELD"[color blue],'MM/DD/YYYY')[/color]

Then you could use it in your Record Selection Statement as follows:

[color green]//Record Selection using a SQL Expression[/color]
{%Date} [color blue]In [/color]{?From_Date} [color blue]to [/color]{?To_Date}

In this example, the Record Selection Criteria is passed to the Database and is, therefore, processed on the Server. This could result in a very significant performance increase. Please note that the Customized Parameter Selection is passed to the Database in the Where Clause of the following SQL Statement:

SELECT
TO_CHAR("TABLE"."DATE_FIELD",'MM/DD/YYYY')
FROM
"DATABASE"."TABLE" "TABLE_NAME"
WHERE
TO_CHAR("TABLE"."DATE_FIELD",'MM/DD/YYYY') >= '11/01/2001' AND
TO_CHAR("TABLE"."DATE_FIELD",'MM/DD/YYYY') <= '11/05/2001'


Q: When is it appropriate to use SQL Expressions?

A: Unfortunately, the answer to this question is a little more nebulous as it largely depends on a combination of your skills, database permissions and your Report Development environment.

For example, many companies have defined barriers between Report Writers and Database Developers. In these cases, the Report writer may not have permission to create objects such as Views, Stored Procs or Functions on the Database. As such, the Report Writer must build reports using only the existing database objects. In this type of scenario, SQL Expressions can be very effective since you are basically building a 'View' outside of the actual database environment. That is, you have the ability to create customized field that are handled as if they were actual database fields.

If you, as a Report Writer or Developer, have the skills to create database objects and have the necessary level of database permissions and are in a development environment that supports the addition of new database objects then you probably don't need to use SQL Expressions since all of your database field manipulation can be done in the database object you create.

Generally speaking, however, if you can substitute a SQL Expression for an equivalent Formula (based on your available SQL Functions) then it is appropriate to do so. As explained above, this is especially important when it comes to your Record Selection Criteria.