Assigning Screening Conditions to a File

In this section:

How to:

Reference:

You can assign screening conditions to a data source, independent of a request, and activate these screening conditions for use in report requests against the data source.

A filter is a packet of definitions that resides at the file level, containing WHERE and/or IF criteria. Whenever a report request is issued against a data source, all filters that have been activated for that data source are in effect. WHERE or IF syntax that is valid in a report request is also valid in a filter.

A filter can be declared at any time before the report request is run. The filters are available to subsequent requests during the session in which the filters have been run. For details, see How to Declare a Filter.

Filters allow you to:

In an interactive environment, filters also reduce repetitive ad hoc typing.

Note: Simply declaring a filter for a data source does not make it active. A filter must be activated with a SET command. For details, see How to Activate or Deactivate Filters.

Top of page
x
Syntax: How to Declare a Filter

A filter can be described by the following declaration 

 FILTER FILE filename [CLEAR|ADD]
    [filter-defines;]
    NAME=filtername1 [,DESC=text]
    where-if phrases    .
    .
    .
    NAME=filternamen [,DESC=text]
    where-if phrases END

where:

filename
Is the name of the Master File to which the filters apply.
CLEAR
Deletes any existing filter phrases, including any previously defined virtual fields.
ADD
Enables you to add new filter phrases to an existing filter declaration without clearing previously defined filters.
filter-defines
Are virtual fields declared for use in filters. For more information, see Usage Notes for Virtual Fields Used in Filters.
filtername1...filternamen
Is the name by which the filter is referenced in subsequent SET FILTER commands. This name may be up to eight characters long and must be unique for a particular file name.
text
Describes the filter for documentation purposes. Text must fit on one line.
where-if phrases
Are screening conditions that can include all valid syntax. They may refer to data source fields and virtual fields in the Master File. They may not refer to virtual fields declared using a DEFINE command, or to other filter names.
Top of page
x
Reference: Usage Notes for Virtual Fields Used in Filters

Virtual fields used in filters:

Example: Declaring Filters

The first example creates the filter named UK, which consists of one WHERE condition. It also adds a definition for the virtual field MARK_UP to the set of virtual fields already being used in filters for the CAR data source.

When a report request is issued for CAR, with UK activated, the condition WHERE MARK_UP is greater than 1000 is automatically added to the request.

Note: The virtual field MARK_UP cannot be explicitly displayed or referenced in the TABLE request.

FILTER FILE CAR ADD
MARK_UP/D7=RCOST-DCOST;
NAME=UK
WHERE MARK_UP GT 1000
END

The second example declares three named filters for the CAR data source: ASIA, UK, and LUXURY. The filter ASIA contains a textual description, for documentation purposes only. CLEAR, on the first line, erases any previously existing filters for CAR, as well any previously defined virtual fields used in filters for CAR, before it processes the new definitions.

FILTER FILE CAR CLEAR
NAME=ASIA,DESC=Asian cars only
IF COUNTRY EQ JAPAN
NAME=UK
IF COUNTRY EQ ENGLAND
NAME=LUXURY
IF RETAIL_COST GT 50000
END
Top of page
x
Syntax: How to Activate or Deactivate Filters

Filters can be activated and deactivated with the command 

SET FILTER= {*|xx[yy zz]} IN {file|*} {ON|OFF}

where:

*
Denotes all declared filters. This is the default value.
xx, yy, zz
Are the names of filters as declared in the NAME = syntax of the FILTER FILE command.
file
Is the name of the data source to which you are assigning screening conditions. * denotes all data sources.
ON
Activates all (*) or specifically named filters for the data source or all data sources (*). The maximum number of filters you can activate for a data source is limited by the number of WHERE/IF phrases that the filters contain, not to exceed the limit of WHERE/IF criteria in any single report request.
OFF
Deactivates all (*) or specifically named filters for the data source or all data sources (*). OFF is the default value.

Note: The SET FILTER command is limited to one line. To activate more filters than fit on one line, issue additional SET FILTER commands. As long as you specify ON, the effect is cumulative.
Example: Activating and Deactivating Filters

The following commands activate A, B, C, D, E, F and deactivate G (assuming that it was set ON previously): 

SET FILTER = A B C IN CAR ON
SET FILTER = D E F IN CAR ON
SET FILTER = G IN CAR OFF

The following commands activate some filters and deactivate others:

SET FILTER = UK LUXURY IN CAR ON
...
TABLE FILE CAR
PRINT COUNTRY MODEL RETAIL_COST
END
...
SET FILTER = LUXURY IN CAR OFF
TABLE FILE CAR
PRINT COUNTRY MODEL RETAIL_COST
END

The first SET FILTER command activates the filters UK and LUXURY, assigned to the CAR data source, and applies their screening conditions to any subsequent report request against the CAR data source.

The second SET FILTER command deactivates the filter LUXURY for the CAR data source. Unless LUXURY is reactivated, any subsequent report request against CAR will not apply the conditions in LUXURY, but will continue to apply UK.
Top of page
x
Syntax: How to Query the Status of Filters

To determine the status of existing filters, use 

? FILTER [{file|*}] [SET] [ALL]]

where:

file
Is the name of a Master File.
*
Displays filters for all Master Files for which filters have been declared.
SET
Displays only active filters.
ALL
Displays all information about the filter, including its description and the exact WHERE/IF definition.
Example: Querying Filters

To query filters, issue the following command: 

FILTER FILE CAR CLEAR
NAME=BOTH, DESC=Asian and British cars only
IF COUNTRY EQ JAPAN AND ENGLAND
END
SET FILTER =BOTH IN CAR ON
TABLE FILE CAR
PRINT CAR RETAIL_COST
BY COUNTRY
END

The output is:

COUNTRY     CAR               RETAIL_COST
-------     ---               -----------
ENGLAND     JAGUAR                  8,878
            JAGUAR                 13,491
            JENSEN                 17,850
            TRIUMPH                 5,100
JAPAN       DATSUN                  3,139
            TOYOTA                  3,339

The following example queries filters for all data sources:

? FILTER

If no filters are defined, the following message displays:

NO FILTERS DEFINED

If filters are defined, the following screen displays:

Set File     Filter name Description
--- -------- ----------- -----------------------------------
    CAR      ROB         Rob's selections
*   CAR      PETER       Peter's selections for CAR
*   EMPLOYEE DAVE        Dave's tests
    EMPLOYEE BRAD        Brad's tests

To query filters for the CAR data source, issue:

? FILTER CAR

If no filters are defined for the CAR data source, the following message displays:

NO FILTERS DEFINED FOR FILE NAMED CAR

If filters are defined for the CAR data source, the following screen displays:

Set File     Filter name Description
--- -------- ----------- -----------------------------------
    CAR      ROB         Rob's selections
*   CAR      PETER       Peter's selections for CAR

To see all active filters, issue the following command:

? FILTER * SET

The output is:

Set File     Filter name Description
--- -------- ----------- -----------------------------------
*   CAR      PETER       Peter's selections for CAR
*   EMPLOYEE DAVE        Dave's tests

The asterisk in the first column indicates that a filter is activated.
Top of page
x
Preserving Filters Across Joins

How to:

By default, filters defined on the host data source are cleared by a JOIN command. However, filters can be maintained when a JOIN command is issued, by issuing the SET KEEPFILTERS=ON command.

Setting KEEPFILTERS to ON reinstates filter definitions and their individual declared status after a JOIN command. The set of filters and virtual fields defined prior to each join is called a context (see your documentation on SET KEEPDEFINES and on DEFINE FILE SAVE for information about contexts as they relate to virtual fields). Each new JOIN or DEFINE FILE command creates a new context.

If a new filter is defined after a JOIN command, it cannot have the same name as any previously defined filter unless you issue the FILTER FILE command with the CLEAR option. The CLEAR option clears all filter definitions for that data source in all contexts.

When a JOIN is cleared, each filter definition that was in effect prior to the JOIN command and that was not cleared, is reinstated with its original status. Clearing a join by issuing the JOIN CLEAR join_name command removes all of the contexts and filter definitions that were created after the JOIN join_name command was issued.

Note: When an error occurs because of a reference to field that does not exist in the original FILTER FILE, the filter is disabled even though KEEPFILTERs is set to ON.
x
Syntax: How to Preserve Filter Definitions With KEEPFILTERS
SET KEEPFILTERS = {OFF|ON}

where:

OFF
Does not preserve filters issued prior to a join. OFF is the default value.
ON
Preserves filters across joins.
Example: Preserving Filters With KEEPFILTERS

The first filter, UNITPR, is defined prior to issuing any joins, but after setting KEEPFILTERS to ON:

SET KEEPFILTERS = ON
FILTER FILE VIDEOTRK
PERUNIT/F5 = TRANSTOT/QUANTITY;
NAME=UNITPR
WHERE PERUNIT GT 2
WHERE LASTNAME LE 'CRUZ'
END

The ? FILTER command shows that the filter named UNITPR was created but not activated (activation is indicated by an asterisk in the SET column of the display:

? FILTER
 
SET FILE     FILTER NAME DESCRIPTION
--- -------- ----------- ---------------------------------
    VIDEOTRK UNITPR

Next the filter is activated:

SET FILTER= UNITPR IN VIDEOTRK ON

The ? FILTER query shows that the filter is now activated: 

? FILTER
 
SET FILE     FILTER NAME DESCRIPTION
--- -------- ----------- --------------------------------- 
*   VIDEOTRK UNITPR

The following TABLE request is issued against the filtered data source: 

TABLE FILE VIDEOTRK
SUM QUANTITY TRANSTOT BY LASTNAME
END

The output shows that the TABLE request retrieved only the data that satisfies the UNITPR filter:

NUMBER OF RECORDS IN TABLE=        6  LINES=      3
ACCESS LIMITED BY FILTERS
 
PAUSE.. PLEASE ISSUE CARRIAGE RETURN WHEN READY
 
LASTNAME         QUANTITY  TRANSTOT
--------         --------  --------
CHANG                   3     31.00
COLE                    2     18.98
CRUZ                    2     16.00

Now, the VIDEOTRK data source is joined to the MOVIES data source. The ? FILTER query shows that the join did not clear the UNITPR filter:

JOIN MOVIECODE IN VIDEOTRK TO ALL MOVIECODE IN MOVIES AS J1

The ? FILTER command shows that the UNITPR filter still exists and is still activated:

? FILTER
 
SET FILE     FILTER NAME DESCRIPTION
--- -------- ----------- --------------------------------- 
*   VIDEOTRK UNITPR

Next a new filter, YEARS1, is created and activated for the join between VIDEOTRK and MOVIES:

FILTER FILE VIDEOTRK
YEARS/I5 = (EXPDATE - TRANSDATE)/365;
NAME=YEARS1
WHERE YEARS GT 1
END
SET FILTER= YEARS1 IN VIDEOTRK ON

The ? FILTER query shows that both the UNITPR and YEARS1 filters exist and are activated:

? FILTER
 
SET FILE     FILTER NAME DESCRIPTION
--- -------- ----------- ---------------------------------
*   VIDEOTRK UNITPR
*   VIDEOTRK YEARS1

Now, J1 is cleared. The output of the ? FILTER command shows that the YEARS1 filter that was created after the JOIN command was issued no longer exists. The UNITPR filter created prior to the JOIN command still exists with its original status:

JOIN CLEAR J1
? FILTER
 
SET FILE     FILTER NAME DESCRIPTION
--- -------- ----------- ---------------------------------
*   VIDEOTRK UNITPR
Information Builders