INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Common Formulas

What is a SQL Expression? by rhinok
Posted: 20 Dec 01 (Edited 21 Jan 05)

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 ADD_MONTHS(D,N) 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 (Date(YYYY, MM, DD) - how Date Parameters are entered when reports are scheduled through Crystal Enterprise), 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:

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

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

//Record Selection Criteria - Indirect Method
{@Date} In {?From_Date} to {?To_Date}

Where @Date is a formula defined as follows:

//@Date
ToText(ToText({Table.Date_Field},'MM/dd/yyyy'))

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):

//%Date
TO_CHAR(TO_CHAR("TABLE"."DATE_FIELD",'MM/DD/YYYY')
,'MM/DD/YYYY')

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

//Record Selection using a SQL Expression
{%Date} In {?From_Date} to {?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 subquery or Function (User Defined Function in SQL Server) outside of the actual database environment.  That is, you have the ability to create customized fields 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.

Back to Business Objects: Crystal Reports 4 Other topics FAQ Index
Back to Business Objects: Crystal Reports 4 Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close