Using OR with SQLWhereItems


SQLWhereItems is a list of items for use during the OnPrepareSQL phase where each one is parsed into the SQLWhere property for you so that you don't have to worry about what may or may not already be a part of the SQLWhere property. Simply adding logical boolean units to the SQLWhereItems property will do what you want. Directly altering the SQLWhere property may erase part of or ruin the format of the WHERE clause of the SELECT statement.

I have made the SQLWhereItems property able to receive parenthesis and the OR operator as well. This should make it more straight forward to use this in the OnPrepareSQL phase where you are assembling SQL WHERE criteria from controls, etc.

It will take the following entries:

SQLWhereItems.Add( '(' );
SQLWhereItems.Add( 'MYCOL1 = 100' );
SQLWhereItems.Add( 'OR' );
SQLWhereItems.Add( 'MYCOL1 = 200' );
SQLWhereItems.Add( ')' );

and when parsed into the finalized SQL statement will end up as:

WHERE .... <original criteria plus other system stuff>
AND ( MYCOL1 = 100 OR MYCOL1 = 200 )

This may seem simple and of little use but it will make it much easier to
build the SQL criteria when combining the logic of numerous controls that
impact the statement.

It is possible to nest the parenthesis and if an operator is ommitted AND is
used as the default. For example:

SQLWhereItems.Add( '(' );
SQLWhereItems.Add( '(' );
SQLWhereItems.Add( 'MYCOL = 100' );
SQLWhereItems.Add( 'MYCOL = 200' );
SQLWhereItems.Add( ')' );
SQLWhereItems.Add( 'MYCOL = 300' );
SQLWhereItems.Add( ')' );

when parsed into the finalized SQL statement will end up as:

WHERE ....
AND (( MYCOL = 100 AND MYCOL = 200 ) AND MYCOL = 300 )