Evaluating Clarion Statement as SQL Statement

In addition to using Clarion Syntax, you can also use full SQL Syntax by wrapping the free-form statement with SQL('sql statement using Clarion Syntax as needed') This instructs roomMaster to execute an SQL statement against the database rather than just evaluate the current buffer. However, the SQL statement MUST return either a true, false or numeric value so the rule can evaluate to either True or False. If this is not a True/False statement, it can return a value, but the value must be a SINGLE RECORD VALUE - and not more than one record in a record set. (ie: SELECT FIRST .... or SELECT TOP 1 ...)

This can be accomplished by using a SELECT COUNT(*) statement. You cannot return a record set such as using SELECT * or SELECT field, field as this would not evaluate as either True or False. To include an apostrophe as part of the value inside the statement requires two apostrophes in succession, such as VALUE = ''H''. You must have knowledge of SQL Syntax and knowledge of the tables and fields to construct these statements.

An example of checking to see if any special packages were attached to the reservation:

SQL('SELECT COUNT(*) FROM OTHERCHARGE WHERE OTHERKEY = ''P' & FORMAT(RES:CONFNUM,@N07) & '''')

The entire statement would first be evaluated to an expression AND THEN passed as an SQL statement to the SQL Engine. The above statement would be translated to:

SELECT COUNT(*) FROM OTHERCHARGE WHERE OTHERKEY = 'P0001234'

Notice that at the end, there are four apostrophe's. The first is used to indicate the beginning of literal text. The second and third are to indicate an actual apostrophe (see above paragraph regarding two apostrophes in succession) and the final (fourth) apostrophe indicates the ending of literal text.