FILTER (set view filter expression)

Top  Previous  Next

 

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:

BIND

UNBIND

EVALUATE

SQL