The AddToWhere method adds a condition to the WHERE clause of a SQL statement. This saves having to parse the statement and doing string manipulation to insert the condition at the correct spot. This method can be called, for example, from the Select script of a table to automatically add a filter condition the user doesn't see. If you want to change the filter conditions for a report, such as from the DataEngine.FinalizeSQLStatement script, add new conditions to FilterConditions collection instead.

Syntax

AddToWhere(SQLStatement as String,
  Condition as String) as String

Parameters
SQLStatement
The SQL statement to add a condition to.

Condition
The WHERE condition to add to the SQL statement.

Return Value
The SQL statement with the condition added to the WHERE clause; if there was no WHERE clause in the statement, one is added.

Example
This example, called from the Select script for a table, adjusts the query so the user can only see active records.

Visual FoxPro

lparameters toApplication as SQApplication, ;
  toDatabase as Database, tcSelect, tcCursor
local lcSelect, llReturn
lcSelect = toApplication.DataEngine.AddToWhere(tcSelect, ;
  'ACTIVE = .T.')
llReturn = not empty(toDatabase.ExecuteSQLStatement(lcSelect, ;
  .NULL., tcCursor))
return llReturn

VBScript

function Main(SQApplication, Database, SelectStatement, CursorName)
SQLSelect = SQApplication.DataEngine.AddToWhere(SelectStatement, _
  "ACTIVE = 1")
Main = Database.ExecuteSQLStatement(SQLSelect)
end function

JavaScript

function Main(SQApplication, Database, SelectStatement, CursorName) {
var SQLSelect, XMLResult ;
SQLSelect = SQApplication.DataEngine.AddToWhere(SelectStatement, 
  'ACTIVE = 1') ;
XMLResult = Database.ExecuteSQLStatement(SQLSelect) ;
return XMLResult ;
}

The code calls the AddToWhere method of the DataEngine object of the Application object to add the condition to the SQL statement, then calls ExecuteSQLStatement to retrieve the data. The VBScript and JavaScript code returns the result set as XML and the Visual FoxPro code creates a cursor.

See Also
DataEngine Object | DataEngine.FinalizeSQLStatement | FilterConditions Collection | Select