Crystal Reports SQL Expressions

Very nice explantion about SQL expressions in Crystal

The Crystal Reports Underground News: "Doing a SELECT in a SQL Expression field:


When teaching SQL expression fields I have always tried to stress that SQL Expressions are different from SQL Statements. A SQL Expression is a column in the report, where a SQL statement is a full query. My short version of this was to say 'a SQL Expression can't do a 'SELECT'. Well I recently learned that this is not precisely true. Under certain situations, a SQL Expression CAN do a completely separate select from the main report.

The main limitation is that it can only return a single value. So you probably will need a summary function. The following example comes from the Xtreme Sample Database:
(SELECT Max ( Orders.`Order ID`)
FROM `Orders` Orders)

Normally a CR SQL Expression would error on the SELECT, but if you put this expression in parentheses, Crystal will pass it to the database as a separate query. Amazingly, the column being queried does not even have to come from one of the tables in the report, but can be from another table in the database. In the past I would have recommended doing this via subreport. The advantage of a SQL Expression is that the value returned can be used to control things like Selecting, Sorting and Grouping in the report. To learn more about using complex SQL Expressions see my Experts Guide to SQL Expressions, Options and Commands."