Selections Based on Individual Values

In this section:

How to:

Reference:

The WHERE phrase selects records from the data source to be included in a report. The data is evaluated according to the selection criteria before it is retrieved from the data source.

You can use as many WHERE phrases as necessary to define your selection criteria. For an illustration, see Using Multiple WHERE Phrases. For additional information, see Using Compound Expressions for Record Selection.

Note: Multiple selection tests on fields that reside on separate paths of a multi-path data source are processed as though connected by either AND or OR operators, based on the setting of a parameter called MULTIPATH. For details, see Controlling Record Selection in Multi-path Data Sources.

Syntax: How to Select Records With WHERE 

WHERE criteria [;]

where:

criteria
Are the criteria for selecting records to include in the report. The criteria must be defined in a valid expression that evaluates as true or false (that is, a Boolean expression). Expressions are described in detail in Using Expressions. Operators that can be used in WHERE expressions (such as, CONTAINS, IS, and GT), are described in Operators Supported for WHERE and IF Tests.
;
Is an optional semicolon that can be used to enhance the readability of the request. It does not affect the report.

Reference: Usage Notes for WHERE Phrases

The WHERE phrase can include:

  • Most expressions that would be valid on the right-hand side of a DEFINE expression. However, the logical expression IF ... THEN ... ELSE cannot be used.
  • Real fields, temporary fields, and fields in joined files. If a field name is enclosed in single or double quotation marks, it is treated as a literal string, not a field reference.
  • The operators EQ, NE, GE, GT, LT, LE, CONTAINS, OMITS, FROM ... TO, NOT-FROM ... TO, INCLUDES, EXCLUDES, LIKE, and NOT LIKE.
  • All arithmetic operators (+, -, *, /, **), as well as, functions (MIN, MAX, ABS, and SQRT).
  • An alphanumeric expression, which can be a literal, or a function yielding an alphanumeric or numeric result using EDIT or DECODE.

    Note that files used with DECODE expressions can contain two columns, one for field values and one for numeric decode values.

  • Alphanumeric and date literals enclosed in single quotation marks and date-time literals in the form DT (date-time literal).
  • A date literal used in a selection test against a date field cannot contain the day of the week value.
  • Text fields. However, the only operators supported for use with text fields are CONTAINS and OMITS.
  • All functions.

You can build complex selection criteria by joining simple expressions with AND and OR logical operators and, optionally, adding parentheses to specify explicitly the order of evaluation. This is easier than trying to achieve the same effect with the IF phrase, which may require the use of a separate DEFINE command. For details, see Using Compound Expressions for Record Selection.

Example: Using a Simple WHERE Test

To show only the names and salaries of employees earning more than $20,000 a year, issue the following request: 

TABLE FILE EMPLOYEE
PRINT LAST_NAME AND FIRST_NAME AND CURR_SAL
BY LAST_NAME NOPRINT
WHERE CURR_SAL GT 20000
END

In this example, CURR_SAL is a selected field, and CURR_SAL GT 20000 is the selection criterion. Only those records with a current salary greater than $20,000 are retrieved. All other records are ignored.

The output is:

Example: Using Multiple WHERE Phrases

You can use as many WHERE phrases as necessary to define your selection criteria. This request uses multiple WHERE phrases so that only those employees in the MIS or Production departments with the last name of Cross or Banning are included in the report. 

TABLE FILE EMPLOYEE
PRINT EMP_ID LAST_NAME
WHERE SALARY GT 20000
WHERE DEPARTMENT IS 'MIS' OR 'PRODUCTION'
WHERE LAST_NAME IS 'CROSS' OR 'BANNING'
END

The output is:

For related information, see Using Compound Expressions for Record Selection.

Controlling Record Selection in Multi-path Data Sources

How to:

Reference:

When you report from a multi-path data source, a parent segment may have children down some paths, but not others. The MULTIPATH parameter allows you to control whether such a parent segment is omitted from the report output.

The MULTIPATH setting also affects the processing of selection tests on independent paths. If MULTIPATH is set to:

The MULTIPATH settings apply in all types of data sources and in all reporting environments (TABLE, TABLEF, MATCH, GRAPH, and requests with multiple display commands). MULTIPATH also works with alternate views, indexed views, filters, DBA, and joined structures.

Syntax: How to Control Record Selection in Multi-path Data Sources

To set MULTIPATH from the command level or in a stored procedure, use 

SET MULTIPATH = {SIMPLE|COMPOUND}

To set MULTIPATH in a report request, use 

ON TABLE SET MULTIPATH {SIMPLE|COMPOUND}

where:

SIMPLE
Includes a parent segment in the report output if:
  • It has at least one child that passes its screening conditions.

    Note: A unique segment is considered a part of its parent segment, and therefore does not invoke independent path processing.

  • It lacks any referenced child on a path, but the child is optional.

    The (FOC144) warning message is generated when a request screens data in a multi-path report: 

    (FOC144) WARNING. TESTING IN INDEPENDENT SETS OF DATA
COMPOUND
Includes a parent in the report output if it has all of its required children. WHERE or IF tests on separate paths are treated as if they are connected by an AND operator. That is, all paths must pass the screening tests in order for the parent to be included in the report output. COMPOUND is the default value.

For related information, see MULTIPATH and SET ALL Combinations and Rules for Determining If a Segment Is Required.

Reference: Requirements and Usage Notes for MULTIPATH = COMPOUND

  • The minimum memory requirement for the MULTIPATH = COMPOUND setting is 4K per active segment. If there is insufficient memory, the SIMPLE setting is implemented and a message is returned.

    There is no limit to the number of segment instances (rows). However, no single segment instance can have more than 4K of active fields (referenced fields or fields needed for retrieving referenced fields). If this limit is exceeded, the SIMPLE setting is implemented and a message is returned.

  • WHERE criteria that screen on more than one path with the OR operator are not supported.

Example: Retrieving Data From Multiple Paths

This example uses the following segments from the EMPLOYEE data source:

The request that follows retrieves data from both paths with MULTIPATH = SIMPLE, and displays data if either criterion is met: 

SET ALL = OFF
SET MULTIPATH = SIMPLE
TABLE FILE EMPLOYEE 
PRINT GROSS DATE_ATTEND COURSE_NAME
BY LAST_NAME BY FIRST_NAME
WHERE PAY_DATE EQ 820730
WHERE COURSE_CODE EQ '103'
END

The following warning message is generated: 

(FOC144) WARNING. TESTING IN INDEPENDENT SETS OF DATA

Although several employees have not taken any courses, they are included in the report output since they have instances on one of the two paths.

The output is:

If you run the same request with MULTIPATH = COMPOUND, the employees without instances for COURSE_NAME are omitted from the report output, and the warning message is not generated.

The output is: 

LAST_NAME       FIRST_NAME            GROSS  DATE_ATTEND  COURSE_NAME      
---------       ----------            -----  -----------  -----------      
JONES           DIANE             $1,540.00     82/05/26  BASIC REPORT PREP FOR PROG
SMITH           MARY              $1,100.00     81/11/16  BASIC REPORT PREP FOR PROG

 

Reference: MULTIPATH and SET ALL Combinations

The ALL parameter affects independent path processing. The following table uses examples from the EMPLOYEE data source to explain the interaction of ALL and MULTIPATH.

Request

MULTIPATH=SIMPLE

MULTIPATH=COMPOUND

 
SET ALL = OFF
PRINT EMP_ID PAY_DATE DATE_ATTEND

Shows employees who have either SALINFO data or ATTNDSEG data.

Shows employees who have both SALINFO and ATTNDSEG data. 

SET ALL = ON
PRINT EMP_ID PAY_DATE DATE_ATTEND

Shows employees who have SALINFO data or ATTNDSEG data or no child data at all.

Same as SIMPLE.

 
SET ALL = OFF
PRINT EMP_ID PAY_DATE DATE_ATTEND
WHERE PAY_DATE EQ 980115

Shows employees who have either SALINFO data for 980115 or any ATTNDSEG data.

Produces (FOC144) message.

Shows employees who have both SALINFO data for 980115 and ATTNDSEG data. 

SET ALL = ON
PRINT EMP_ID PAY_DATE DATE_ATTEND
WHERE PAY_DATE EQ 980115

Shows employees who have either SALINFO data for 980115 or any ATTNDSEG data.

Produces (FOC144) message.

Shows employees who have SALINFO data for 980115. Any DATE_ATTEND data is also shown. 

SET ALL = OFF 
PRINT ALL.EMP_ID DATE_ATTEND
WHERE PAY_DATE EQ 980115

Shows employees who have either SALINFO data for 980115 or any ATTNDSEG data.

Produces (FOC144) message.

Shows employees who have SALINFO data for 980115. Any DATE_ATTEND data is also shown. 

SET ALL = ON or OFF
PRINT EMP_ID PAY_DATE DATE_ATTEND
WHERE PAY_DATE EQ 980115 AND COURSE_CODE EQ  '103'

Shows employees who have either SALINFO data for 980115 or COURSE 103.

Note: SIMPLE treats AND in the WHERE clause as OR.

Produces (FOC144) message.

Shows employees who have both SALINFO data for 980115 and COURSE 103.

Note: SET ALL = PASS is not supported with MULTIPATH = COMPOUND.

For related information about the ALL parameter, see Handling Records With Missing Field Values.

Reference: Rules for Determining If a Segment Is Required

The segment rule is applied level by level, descending through the data source/view hierarchy. That is, a parent segment existence depends on the child segment existence, and the child segment depends on the grandchild existence, and so on, for the full data source tree.

The following rules are used to determine if a segment is required or optional:

  • When SET ALL is ON or OFF, a segment with WHERE or IF criteria is required for its parent, and all segments up to the root segment are required for their parents.

    When SET ALL = PASS, a segment with WHERE or IF criteria is optional.

  • IF SET ALL = ON or PASS, all referenced segments with no WHERE or IF criteria are optional for their parents (outer join).
  • IF SET ALL = OFF, all referenced segments are required (inner join).
  • A referenced segment can become optional if its parent segment uses the ALL. field prefix operator.

Note: ALL = PASS is not supported for all data adapters and, if it is supported, it may behave slightly differently. Check your specific data adapter documentation for detailed information.

For related information about the ALL parameter, see Handling Records With Missing Field Values, and the Describing Data With TIBCO WebFOCUS® Language manual.