DF_FILE_SQL_FILTER

See Also: DF_FILE_SQL_FILTER_ACTIVE, DF_FILE_SQL_FILTER_EQ

 

A filter to apply to select statements generated for find operations on the table. This is a global table filter. See pbApplyGlobalSQLFilters for more information.

Level

Table

Supported by

The DataFlex SQL Drivers (SQL Server, DB2 and ODBC)

Type

String, temporary

Access

Read / Write

Values

A filter expression.

Syntax

Use cli.pkg

 

Get_Attribute DF_FILE_SQL_FILTER of {tableNumber} to {StringVariable}

Set_Attribute DF_FILE_SQL_FILTER of {tableNumber} to {StringVariable}

Sample

Use cli.pkg

 

Procedure FilterData

    String sFilter

 

    Move "Customer.Name LIKE %Smith%" to sFilter

    Set_Attribute DF_FILE_SQL_FILTER of Customer.File_Number to sFilter

End_Procedure

Sample

Use cli.pkg

 

Procedure FilterData

    Set_Attribute DF_FILE_SQL_FILTER of Customer.File_Number to ("Customer.Number >= 1000")

End_Procedure

Remarks

This attribute allows the programmer to define a filter that is applied to find operations. It can be used to speed up find operations. The filter value in the attribute is added to the beginning of the where clause of statements generated for find operations but it will not be added for find eq operations. You must use SQL syntax in the filter string. The filter is applied when the DF_FILE_SQL_FILTER_ACTIVE attribute is set to true.

We recommend always surrounding filters containing string constants by parentheses to ensure they are executed correctly if combined with local SQL filters (see psSQLFilter).

Note that by default, global SQL filters are not applied to all find operations. The exceptions are Find EQ and FindByRowId. This exception exists to not break relates. The DF_FILE_SQL_FILTER_EQ attribute controls this behavior. So if you want your user to never see data that doesn’t match your filter (for example when building multi-tenant applications), then you need to make sure that DF_FILE_SQL_FILTER_EQ is set to true for each table.

For more information about SQL filters, see Using SQL Filters (Constraints).