Navigation: Language Reference > 5 - Declaration Attributes > Variable and Entity Attributes >====== FILTER (set view filter expression) ====== | |
FILTER(expression)
FILTER | Specifies a filter expression used to evaluate records to include in the VIEW. |
expression | A string constant containing a logical expression. |
The FILTER attribute (PROP:FILTER) specifies a filter expression used to evaluate records to include in the VIEW.
The expression may reference any field in the VIEW, at all levels of JOIN structures. The entire expression must evaluate as true for a record to be included in the VIEW. The expression may contain any valid Clarion language logical expression. The expression is evaluated at runtime (just like the EVALUATE procedure), therefore you must BIND all variables used in the expression.
Use of MATCH with PROP:Filter and SQL Databases
The use of PROP:Filter as an SQL filter generator for SQL databases now supports converting the MATCH(s1, s2, n) function result to an appropriate SQL filter for all values of n, except Match:Regular. If you use the Match:Regular mode, the filter will be evaluated on the client side just like all other non-SQL convertible filters.
Other conversion rules:
MATCH(s1,s2,Match:Simple) will be converted to
s1 = s2
for all SQL drivers.
MATCH(s1,s2,Match:Soundex) will be converted to
{fn SOUNDEX(s1)} = {fn SOUNDEX(s2)}
for all ODBC back ends that support the SOUNDEX function.
MATCH(s1,s2,Match:Soundex) will be converted to
SOUNDEX(s1) = SOUNDEX(s2)
for the Oracle Accelerator.
MATCH(s1,s2,Match:Simple + Match:NoCase) will be converted to
{fn UPPER(s1)} = {fn UPPER(s2)}
for all ODBC drivers.
MATCH(s1,s2,Match:Simple + Match:NoCase) will be converted to
UPPER(s1) = UPPER(s2)
for the Oracle Accelerator.
MATCH(s1,s2) !Match:Wild mode
Will be converted to
s1 LIKE %
if the s2 parameter is using an asterisk (*), or
s1 LIKE _
if the s2 parameter is using a question mark (?) wild card.
This is valid for all SQL drivers.
Adding the Match:NoCase mode to the Match:Wild mode is converted to
{fn UPPER(s1)} LIKE % or {fn UPPER(s1)} LIKE _
for all ODBC drivers.
Adding the Match:NoCase mode to the Match:Wild mode is converted to
UPPER(s1) LIKE % or UPPER(s1) LIKE _
for the Oracle Accelerator.
Example:
BRW1::View:Browse VIEW(Members) PROJECT(Mem:MemberCode,Mem:LastName,Mem:FirstName) END KeyValue STRING(20) !Get only orders for customer 9999 since order number 100 ViewOrder VIEW(Customer),FILTER('Cus:AcctNumber = 9999 AND Hea:OrderNumber > 100') PROJECT(Cus:AcctNumber,Cus:Name) JOIN(Hea:AcctKey,Cus:AcctNumber) !Join Header file PROJECT(Hea:OrderNumber) JOIN(Dtl:OrderKey,Hea:OrderNumber) !Join Detail file PROJECT(Det:Item,Det:Quantity) JOIN(Pro:ItemKey,Dtl:Item) !Join Product file PROJECT(Pro:Description,Pro:Price) END END END END !view CODE BIND('KeyValue',KeyValue) BIND(Mem:Record) KeyValue = 'Smith' BRW1::View:Browse{PROP:Filter} = 'Mem:LastName = KeyValue' !Specify filter condition OPEN(BRW1::View:Browse) !Open the view SET(BRW1::View:Browse) !and set to the beginning !of the filtered CODE !and ordered result set OPEN((Customer,22h); OPEN((Header,22h); OPEN((Product,22h); OPEN(Detail,22h) BIND('Cus:AcctNumber',Cus:AcctNumber) BIND('Hea:OrderNumber',Hea:OrderNumber) SET(Cus:AcctKey) OPEN(ViewOrder) LOOP NEXT(ViewOrder) IF ERRORCODE() THEN BREAK. !Process the valid record END UNBIND('Cus:AcctNumber',Cus:AcctNumber) UNBIND('Hea:AcctNumber',Hea:AcctNumber) CLOSE(Header); CLOSE(Customer); CLOSE(Product); CLOSE(Detail)
See Also: