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)
ODBCFilterToLocalFilter(SQLStatement as String) as String
The SQL statement to convert.
The SQL statement with ODBC syntax in the WHERE clause converted to local Stonefield Query syntax.
See the example code above.