Types of Record Selection Tests

In this section:

You can select records for your reports using a variety of tests that are implemented using the operators described in Operators Supported for WHERE and IF Tests. You can test for:

Range Tests With FROM and TO

How to:

Use the operators FROM ... TO and NOT-FROM ... TO in order to determine whether field values fall within or outside of a given range. You can use either values or expressions to specify the lower and upper boundaries. Range tests can also be applied on the sort control fields. The range test is specified immediately after the sort phrase.

You can also test whether an expression falls within or outside the boundaries.

Syntax: How to Specify a Range Test (FROM and TO)

WHERE [TOTAL] {fieldname|expression} {FROM|IS-FROM} lower TO upper 
WHERE [TOTAL] fieldname  NOT-FROM      lower TO upper

where:

fieldname
Is any valid field name or alias.
expression
Is any valid expression.
lower
Are numeric or alphanumeric values or expressions that indicate lower boundaries. You may add parentheses around expressions for readability.
upper
Are numeric or alphanumeric values or expressions that indicate upper boundaries. You may add parentheses around expressions for readability.

Example: Range Test With FROM ... TO

An example of a range test using expressions as boundaries follows:

WHERE SALES FROM (DEALER_COST * 1.4) TO (DEALER_COST * 2.0)

The following is an example of a range test using expressions as the comparison value and the boundaries:

WHERE SALES * 1.5 FROM (DEALER_COST * 1.4) TO (DEALER_COST * 2.0)

Example: Range Test With NOT-FROM ... TO

The following illustrates how you can use the range test NOT-FROM ... TO to display only those records that fall outside of the specified range. In this example, it is all employees whose salaries do not fall in the range between $12,000 and $22,000.

TABLE FILE EMPLOYEE
PRINT CURR_SAL
BY LAST_NAME
WHERE CURR_SAL NOT-FROM 12000 TO 22000
END

The output is:

LAST_NAME               CURR_SAL
---------               --------
BANNING               $29,700.00
CROSS                 $27,062.00
GREENSPAN              $9,000.00
IRVING                $26,862.00
SMITH                  $9,500.00
STEVENS               $11,000.00

Example: Range Tests on Sort Fields With FROM ... TO

The following examples demonstrate how to perform range tests when sorting a field using the BY or ACROSS sort phrases:

BY MONTH FROM 4 TO 8

or

ACROSS MONTH FROM 6 TO 10

Range Tests With GE and LE or GT and LT

How to:

The operators GE (greater than or equal to), LE (less than or equal to), GT (greater than), and LT (less than) can be used to specify a range.

GE ... LE enable you to specify values within the range test boundaries.

LT ...GT enable you to specify values outside the range test boundaries.

Syntax: How to Specify Range Tests (GE and LE)

To select values that fall within a range, use

WHERE fieldname GE lower AND fieldname LE upper

To find records whose values do not fall in a specified range, use

WHERE fieldname LT lower OR fieldname GT upper

where:

fieldname
Is any valid field name or alias.
lower
Are numeric or alphanumeric values or expressions that indicate lower boundaries. You may add parentheses around expressions for readability.
upper
Are numeric or alphanumeric values or expressions that indicate upper boundaries. You may add parentheses around expressions for readability.

Example: Selecting Values Inside a Range

This WHERE phrase selects records in which the UNIT value is between 10,000 and 14,000.

WHERE UNITS GE 10000 AND UNITS LE 14000

This example is equivalent to:

WHERE UNITS GE 10000
WHERE UNITS LE 14000

Example: Selecting Values Outside a Range

The following illustrates how you can select values that are outside a range of values using the LT and GT operators. In this example, only those employees whose salaries are less than $12,000 and greater than $22,000 are included in the output.

TABLE FILE EMPLOYEE
PRINT CURR_SAL
BY LAST_NAME
WHERE CURR_SAL LT 12000 OR CURR_SAL GT 22000
END

The output is:

LAST_NAME               CURR_SAL
---------               --------
BANNING               $29,700.00
CROSS                 $27,062.00
GREENSPAN              $9,000.00
IRVING                $26,862.00
SMITH                  $9,500.00
STEVENS               $11,000.00

Missing Data Tests

How to:

When creating report requests, you may want to test for missing data. This type of test is most useful when fields that have missing data also have the MISSING attribute set to ON in the Master File. For information on missing data, see Handling Records With Missing Field Values, and the Describing Data With TIBCO WebFOCUS® Language manual.

Note: If a test value to screen on an alphanumeric field is a variable and you want to look for missing instances, you must use _FOC_MISSING, instead of MISSING, as an alphanumeric literal value in a test must be in single quotation marks, and 'MISSING' is the literal value MISSING, not the MISSING value. The value _FOC_MISSING represents the MISSING value whether it is in single quotation marks or not.

Syntax: How to Test for Missing Data

{WHERE|IF} fieldname {EQ|IS} MISSING

where:

fieldname
Is any valid field name or alias.
EQ|IS
Are record selection operators. EQ and IS are synonyms.

Syntax: How to Test for Existing Data

{WHERE|IF} fieldname {NE|IS-NOT} MISSING

where:

fieldname
Is any valid field name or alias.
NE|IS-NOT
Are record selection operators. NE and IS-NOT are synonyms.

Character String Screening With CONTAINS and OMITS

The CONTAINS and OMITS operators test alphanumeric fields when used with WHERE, and both alphanumeric and text fields when used with IF. With CONTAINS, if the characters in the given literal or literals appear anywhere within the characters of the field value, the test is passed.

OMITS is the opposite of CONTAINS; if the characters of the given literal or literals appear anywhere within the characters of the field's value, the test fails.

CONTAINS and OMITS tests are useful when you do not know the exact spelling of a value. As long as you know that a specific string appears within the value, you can retrieve the desired data.

Example: Selecting Records With CONTAINS and OMITS

The following examples illustrate several ways to use the CONTAINS and OMITS operators. The field name that is being tested must appear on the left side of the CONTAINS or OMITS operator.

  • In this example, the characters JOHN are contained in JOHNSON, and are selected by the following phrase:
    WHERE LAST_NAME CONTAINS 'JOHN'

    The LAST_NAME field may contain the characters JOHN anywhere in the field.

  • In this example, any last name without the string JOHN is selected:
    WHERE LAST_NAME OMITS 'JOHN'
  • In this example, all names that contain the letters ING are retrieved.
    TABLE FILE EMPLOYEE
    LIST LAST_NAME AND FIRST_NAME
    WHERE LAST_NAME CONTAINS 'ING'
    END

    The output is:

        LIST  LAST_NAME        FIRST_NAME
        ----  ---------        ----------
           1  BANNING          JOHN
           2  IRVING           JOAN

Screening on Masked Fields

How to:

Reference:

A mask is an alphanumeric pattern that you supply for comparison to characters in a data field. The data field must have an alphanumeric format (A). You can use the LIKE and NOT LIKE or the IS and IS-NOT operators to perform screening on masked fields.

The wildcard characters for screening on masked fields with:

Note: The IS (or EQ) and IS-NOT (or NE) operators support screening based on a mask for fixed length formats only. If the format is a variable length format, for example, AnV, use the LIKE or NOT LIKE operator to screen based on a mask.

Syntax: How to Screen Fields Based on a Mask (Using LIKE and NOT LIKE)

To search for records with the LIKE operator, use

WHERE field LIKE 'mask'

To reject records based on the mask value, use either

WHERE field NOT LIKE 'mask'

or

WHERE NOT field LIKE 'mask'

where:

field
Is any valid field name or alias.
mask
Is an alphanumeric or text character string you supply. There are two wildcard characters that you can use in the mask. The underscore (_) indicates that any character in that position is acceptable, and the percent sign (%) allows any following sequence of zero or more characters.

For related information, see Restrictions on Masking Characters.

Syntax: How to Screen Using LIKE and UNLIKE in an IF Phrase

To search for records with the LIKE operator, use

IF field LIKE 'mask1' [OR 'mask2'...]

To reject records based on the mask value, use

IF field UNLIKE 'mask1' [OR 'mask2' ...]

where:

field
Is any valid field name or alias.
mask1, mask2
Are the alphanumeric patterns you want to use for comparison. The single quotation marks are required if the mask contains blanks. There are two wildcard characters that you can use in a mask. The underscore (_) indicates that any character in that position is acceptable, and the percent sign (%) allows any following sequence of zero or more characters. Every other character in the mask accepts only itself in that position as a match to the pattern.

Syntax: How to Screen Fields Based on a Mask (Using IS and IS-NOT)

To search for records with the IS operator, use

{WHERE|IF} field {IS|EQ} 'mask'

To reject records based on the mask value, use

{WHERE|IF} field {IS-NOT|NE} 'mask'

where:

field
Is any valid field name or alias.
IS|IS-NOT
Are record selection operators. EQ is a synonym for IS. NE is a synonym for IS-NOT.
mask
Is an alphanumeric or text character string you supply. The wildcard characters that you can use in the mask are the dollar sign ($) and the combination $*. The dollar sign indicates that any character in that position is acceptable. The $* combination allows any sequence of zero or more characters. The $* is shorthand for writing a sequence of dollar signs to fill the end of the mask without specifying a specific length. This combination can only be used at the end of the mask.

For related information, see Restrictions on Masking Characters.

Reference: Restrictions on Masking Characters

  • The wildcard characters dollar sign ($) and dollar sign with an asterisk ($*), which are used with IS operators, are treated as literals with LIKE operators.
  • Masking with the characters $ and $* is not supported for compound WHERE phrases that use the AND or OR logical operators.

Example: Screening on Initial Characters

To list all employees who have taken basic-level courses, where every basic course begins with the word BASIC, issue the following request:

TABLE FILE EMPLOYEE
PRINT COURSE_NAME COURSE_CODE
BY LAST_NAME BY FIRST_NAME
WHERE COURSE_NAME LIKE 'BASIC%'
END

The output is:

Example: Screening on Characters Anywhere in a Field

If you want to see which employees have taken a FOCUS course, but you do not know where the word FOCUS appears in the title, bracket the word FOCUS with wildcards (which is equivalent to using the CONTAINS operator):

TABLE FILE EMPLOYEE
PRINT COURSE_NAME COURSE_CODE
BY LAST_NAME BY FIRST_NAME
WHERE COURSE_NAME LIKE '%FOCUS%'
END

The output is:

LAST_NAME        FIRST_NAME  COURSE_NAME                     COURSE_CODE
---------        ----------  -----------                     -----------
BLACKWOOD        ROSEMARIE   WHAT'S NEW IN FOCUS             202        
JONES            DIANE       FOCUS INTERNALS                 203        

If you want to list all employees who have taken a 20x-series course, and you know that all of these courses have the same code except for the final character, issue the following request:

TABLE FILE EMPLOYEE
PRINT COURSE_NAME COURSE_CODE
BY LAST_NAME BY FIRST_NAME
WHERE COURSE_CODE LIKE '20_'
END

The output is:

LAST_NAME        FIRST_NAME  COURSE_NAME                     COURSE_CODE
---------        ----------  -----------                     -----------
BLACKWOOD        ROSEMARIE   WHAT'S NEW IN FOCUS             202        
JONES            DIANE       FOCUS INTERNALS                 203        
                             ADVANCED TECHNIQUES             201        

Example: Screening on Initial Characters and Specific Length

The following example illustrates how to screen on initial characters and specify the length of the field value you are searching for. In this example, the WHERE phrase states that the last name must begin with BAN and be seven characters in length (the three initial characters BAN and the four placeholders, in this case, the dollar sign). The remaining characters in the field (positions 8 through 15) must be blank.

TABLE FILE EMPLOYEE
PRINT LAST_NAME
WHERE LAST_NAME IS 'BAN$$$$'
END

The output is:

LAST_NAME
---------
BANNING  

Example: Screening on Records of Unspecified Length

To retrieve records with unspecified lengths, use the dollar sign followed by an asterisk ($*):

WHERE LAST_NAME IS 'BAN$*'

This phrase searches for last names that start with the letters BAN, regardless of the name length. The characters $* reduce typing, and enable you to define a screen mask without knowing the exact length of the field you wish to retrieve.

Syntax: How to Deactivate Wildcard Characters

SET EQTEST = {WILDCARD|EXACT}

where:

WILDCARD
Treats the $ and $* characters as wildcard characters. WILDCARD is the default value.
EXACT
Treats the $ and $* characters as normal characters, not wildcards, in IF tests and in WHERE tests that can be translated to IF tests.

Example: Selecting Records With SET EQTEST

The following request against the VIDEOTR2 data source creates two similar email addresses:

  • handy$man@usa.com, which has a dollar sign.
  • handyiman@usa.com, which has the letter i in the same position as the $ character in the other email address.
DEFINE FILE VIDEOTR2                              
SMAIL/A18= IF EMAIL EQ 'handyman@usa.com'         
           THEN 'handyiman@usa.com'               
           ELSE EMAIL;                            
SMAIL/A18 = STRREP(18,SMAIL,1,'_',1,'$',18,SMAIL);
END
TABLE FILE VIDEOTR2                               
PRINT SMAIL                                       
BY LASTNAME BY FIRSTNAME                          
WHERE SMAIL EQ 'handy$man@usa.com'                
ON TABLE SET EQTEST WILDCARD
END

With SET EQTEST=WILDCARD (the default), the WHERE test WHERE SMAIL IS 'handy$man@usa.com' returns both the record with the $ in the address and the record with the letter i in the address because the $ is treated as a wildcard character, and any character in that position causes the record to pass the screening test:

LASTNAME         FIRSTNAME   SMAIL            
--------         ---------   -----            
HANDLER          EVAN        handy$man@usa.com
                             handyiman@usa.com

Changing the ON TABLE SET command to ON TABLE SET EQTEST EXACT returns just the ONE email address with the $ character because the dollar sign is now treated as a normal character and only passes the test if there is an exact match:

LASTNAME         FIRSTNAME   SMAIL            
--------         ---------   -----            
HANDLER          EVAN        handy$man@usa.com

Using an Escape Character for LIKE

How to:

Reference:

You can use an escape character in the LIKE syntax to treat the masking characters (% and _) as literals within the search pattern, rather than as wildcards. This technique enables you to search for these characters in the data. For related information, see Screening on Masked Fields .

Syntax: How to Use an Escape Character in a WHERE Phrase

Any single character can be used as an escape character, if prefaced with the word ESCAPE

WHERE fieldname LIKE 'mask' ESCAPE 'c'

where:

fieldname
Is any valid field name or alias to be evaluated in the selection test.
mask
Is the search pattern that you supply. The single quotation marks are required.
c
Is any single character that you identify as the escape character. If you embed the escape character in the mask, before a % or _, the % or _ character is treated as a literal, rather than as a wildcard. The single quotation marks are required.

Syntax: How to Specify an Escape Character for a Mask in an IF Phrase

You can assign any single character as an escape character by prefacing it with the word ESCAPE in the LIKE or UNLIKE syntax

IF field {LIKE|UNLIKE} 'mask1' ESCAPE 'a' [OR 'mask2' ESCAPE 'b' ... 

where:

field
Is any valid field name or alias to be evaluated in the selection test.
mask1, mask2
Are search patterns that you supply. The single quotation marks are required.
a, b ...
Are single characters that you identify as escape characters. Each mask can specify its own escape character or use the same character as other masks. If you embed the escape character in the mask, before a % or _, the % or _ character is treated as a literal, rather than as a wildcard. The single quotation marks are required if the mask contains blanks.

Reference: Usage Notes for Escape Characters

  • The use of an escape character in front of any character other than %, _, and itself is ignored.
  • The escape character itself can be escaped, thus becoming a normal character in a string (for example, 'abc\%\\').
  • Only one escape character can be used per LIKE phrase in a WHERE phrase.
  • The escape character is only in effect when the ESCAPE syntax is included in the LIKE phrase.
  • Every LIKE phrase can provide its own escape character.
  • If a WHERE criterion is used with literal OR phrases, the ESCAPE must be on the first OR phrase, and applies to all subsequent phrases in that WHERE expression. For example:
    WHERE field LIKE 'ABCg_' ESCAPE 'g' OR 'ABCg%' OR 'g%ABC'

Example: Using the Escape Character in a WHERE Phrase

The VIDEOTR2 data source contains an email address field. To search for the email address with the characters 'handy_' you can issue the following request:

TABLE FILE VIDEOTR2
PRINT CUSTID LASTNAME FIRSTNAME EMAIL
WHERE EMAIL LIKE 'handy_%'
END

Because the underscore character functions as a wildcard character, this request returns two instances, only one of which contains the underscore character.

The output is:

CUSTID  LASTNAME         FIRSTNAME   EMAIL            
------  --------         ---------   -----            
0944    HANDLER          EVAN        handy_man@usa.com
0944    HANDLER          EVAN        handyman@usa.com 

To retrieve only the instance that contains the underscore character, you must indicate that the underscore should be treated as a normal character, not a wildcard. The following request retrieves only the instance with the underscore character in the email field:

TABLE FILE VIDEOTR2
PRINT CUSTID LASTNAME FIRSTNAME EMAIL
WHERE EMAIL LIKE 'handy\_%' ESCAPE '\'
END

The output is:

CUSTID  LASTNAME         FIRSTNAME   EMAIL            
------  --------         ---------   -----            
0944    HANDLER          EVAN        handy_man@usa.com

Example: Using an Escape Character in an IF Phrase

The VIDEOTR2 data source contains an email address field. To search for email addresses with the characters 'handy_' you can issue the following request:

TABLE FILE VIDEOTR2
PRINT CUSTID LASTNAME FIRSTNAME EMAI
IF EMAIL LIKE 'handy_%'
END

Because the underscore character functions as a wildcard character, this request returns two instances, only one of which contains the underscore character.

The output is:

CUSTID  LASTNAME         FIRSTNAME   EMAIL
------  --------         ---------   -----
0944    HANDLER          EVAN        handy_man@usa.com
0944    HANDLER          EVAN        handyman@usa.com

To retrieve only the instance that contains the underscore character, you must indicate that the underscore should be treated as a normal character, not a wildcard. The following request retrieves only the instance with the underscore character in the email field:

TABLE FILE VIDEOTR2
PRINT CUSTID LASTNAME FIRSTNAME EMAI
IF EMAIL LIKE 'handy\_%' ESCAPE '\'
END

The output is:

CUSTID  LASTNAME         FIRSTNAME   EMAIL
------  --------         ---------   -----
0944    HANDLER          EVAN        handy_man@usa.com

Qualifying Parent Segments Using INCLUDES and EXCLUDES

Reference:

You can test whether instances of a given field in a child segment include or exclude all literals in a list using the INCLUDES and EXCLUDES operators. INCLUDES and EXCLUDES retrieve only parent records. You cannot print or list any field in the same segment as the field specified for the INCLUDES or EXCLUDES test.

Note: INCLUDES and EXCLUDES work only with multi-segment FOCUS data sources.

Reference: Usage Notes for INCLUDES and EXCLUDES

  • Literals containing embedded blanks must be enclosed in single quotation marks.
  • The total number of literals must be 31 or less.
  • To use more than one INCLUDES or EXCLUDES phrase in a request, begin each phrase on a separate line.
  • You can connect the literals you are testing for with ANDs and ORs; however, the ORs are changed to ANDs.

Example: Selecting Records With INCLUDES and EXCLUDES

A request that contains the phrase

WHERE JOBCODE INCLUDES A01 OR B01

returns employee records with JOBCODE instances for both A01 and B01, as if you had used AND.

In the following example, for a record to be selected, its JOBCODE field must have values of both A01 and B01:

WHERE JOBCODE INCLUDES A01 AND B01

If either one is missing, the record is not selected for the report.

If the selection criterion is

WHERE JOBCODE EXCLUDES A01 AND B01

every record that does not have both values is selected for the report.

In the CAR data source, only England produces Jaguars and Jensens, and so the request

TABLE FILE CAR
PRINT COUNTRY
WHERE CAR INCLUDES JAGUAR AND JENSEN
END

generates this output:

COUNTRY
-------
ENGLAND