How to Add a SQL Expression to a Crystal Report

SQL Expressions are useful when adding a value from a table that is not part of the select statement or when adding data that may or may not be present for each record.  For example, adding a field for a UDF that is not a required field.  

To create a SQL Expression field, follow the below steps:

1.  Right-click on SQL Expression Fields in the Field Explorer and select Add.  

2.  Enter the SQL statement in the SQL Editor.  The SQL statement must be surrounded by parentheses.  Also, all fields from the main dataset used to filter the expression must be surrounded by quotation marks. 

This is an example of a SQL Expression that will pull the CR073_TXT_01 record for an event UDF:

(SELECT CR073_TXT_01
FROM CR073_ISSUES
WHERE CR073_ORG_CODE = "EV200_EVENT_MASTER"."EV200_ORG_CODE"
AND CR073_EVT_ID = "EV200_EVENT_MASTER"."EV200_EVT_ID"
AND CR073_EXT_ACCT_CODE ='@EVENT')

The SQL expression is wrapped in parentheses and both EV200_ORG_CODE and EV200_EVT_ID are wrapped in quotations.  In the SQL Expression screen, double-clicking on the appropriate fields in the list of available fields will enter them into the SQL appropriately formatted.   

When using a SQL Expression field, the following needs to be considered:

• All SQL Expression fields can only return one record per details row.  For example, a SQL expression would not be appropriate for pulling order numbers related to an event as there may be more than one.  To prevent report errors, it is recommended to include the SQL keyword TOP 1 in the select statement (SELECT TOP 1 CR073_TXT_01), otherwise the report may return an error in the event that it finds more than one matching value.  
• A SQL expression cannot retrieve data from the same table that is being used to filter it.  For example:

(SELECT TOP 1  ER101_DESC
FROM ER101_ACCT_ORDER_DTL
WHERE ER101_ORG_CODE = "ER101_ACCT_ORDER_DTL"."ER101_ORG_CODE"
AND ER101_ORD_NBR = "ER101_ACCT_ORDER_DTL"."ER101_ORD_NBR")

This example is filtering ER101 by ER101.  Crystal reports is not able to deduce which values come from the main data set and which values should come from the SQL expression so it will return an error or will return unexpected data when attempting to run the report.

In this case, either alias the table in the SQL statement or move the data to a subreport.

An SQL statement where the table has been aliased looks like the following:

(SELECT TOP 1  ITEM_DESCRIPTIONS.ER101_DESC
FROM ER101_ACCT_ORDER_DTL ITEM_DESCRIPTIONS
WHERE ITEM_DESCRIPTIONS.ER101_ORG_CODE = "ER101_ACCT_ORDER_DTL"."ER101_ORG_CODE"
AND ITEM_DESCRIPTIONS.ER101_ORD_NBR = "ER101_ACCT_ORDER_DTL"."ER101_ORD_NBR")