If you create a Select script for a table, you have two choices to ensure the result set the script returns respects the report's filter conditions. One is to parse the WHERE clause from the SQL statement passed as a parameter and use it when constructing the result set. For example:

lparameters toApplication as SQApplication, toDatabase as Database, ;
    tcSelect, tcCursor
lcSelect = 'select * from Orders'
lnPos = atc(' where ', tcSelect)
if lnPos > 0
  lcWhere = substr(tcSelect, lnPos + 7)
  lcSelect = toApplication.DataEngine.AddToWhere(lcSelect, lcWhere)
endif
toDatabase.ExecuteSQLStatement(lcSelect, , tcCursor)

The other is to construct the result set without regard to the report's filter, then use a final SQL statement to return the result set with the desired fields and filter conditions. However, if you query an ODBC database engine, such as SQL Server, the filter conditions may use a different syntax than Stonefield Query does. This is particularly the case for filter conditions involving date, datetime, and logical values. The ODBCFilterToLocalFilter method converts the WHERE clause in a SQL statement from ODBC syntax to local Stonefield Query syntax. For example:

lparameters toApplication as SQApplication, toDatabase as Database, ;
    tcSelect, tcCursor
lcSelect = 'select * from Orders'
lcCursor = sys(2015)
toDatabase.ExecuteSQLStatement(lcSelect, , lcCursor)
lcSelect = toApplication.DataEngine.ODBCFilterToLocalFilter(tcSelect)
lcSelect = strtran(lcSelect, 'Orders', lcCursor, -1, -1, 1)
&lcSelect into cursor (tcCursor)

Syntax

ODBCFilterToLocalFilter(SQLStatement as String) as String

Parameters
SQLStatement
The SQL statement to convert.

Return Value
The SQL statement with ODBC syntax in the WHERE clause converted to local Stonefield Query syntax.

Example
See the example code above.

See also

DataEngine Object | Select

© Stonefield Software Inc., 2023 • Updated: 06/06/16
Comment or report problem with topic