Clarion Community Help

FILTER(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:

BIND

UNBIND

EVALUATE

ORDER

SQL