Features Added in FOCUS 7.7.03

Features Added in FOCUS 7.7.03

In this section:

The following features were added as of FOCUS 7.7.03.

Summarizing Alphanumeric Columns

How to:

Include All Columns on Summary Lines

Reference:

Usage Notes for Summarizing Alphanumeric Columns

By default, subtotals (using the SUBTOTAL and SUB-TOTAL commands) and recalculations (using the RECOMPUTE and SUMMARIZE commands) only display values for numeric report columns. However, you can include alphanumeric columns on these summary lines by either setting SUMMARYLINES=NEW and specifying the columns you want to display on the summary lines or by using the asterisk wildcard character to display all fields on the summary lines.

The alphanumeric value displayed on a SUBTOTAL or SUB-TOTAL line is either the first or last alphanumeric value within the sort group, depending on the value of the SUMPREFIX parameter. On a RECOMPUTE or SUMMARIZE line, alphanumeric values are recalculated using the summary values for that line.

Syntax: How to Include All Columns on Summary Lines

ON sortfield summarycommand *

where:

sortfield: Is the sort field for which a change in value triggers the summary line.
summarycommand: Is SUBTOTAL, SUB-TOTAL, RECOMPUTE, or SUMMARIZE.
*: Indicates that all fields, numeric and alphanumeric, should be included on the summary lines. This is required if you want to display alphanumeric columns on summary lines when SET SUMMARYLINES is set to OLD. For other values of SUMMARYLINES, you can either use the asterisk to display all columns or reference the specific columns you want to display.

Example: Including Alphanumeric Fields on Summary Lines

The following request against the GGSALES data source computes the alphanumeric equivalents of the DOLLARS and UNITS fields, creates an alphanumeric version of the formula for the ratio between DOLLARS and UNITS, and computes the numeric ratio between DOLLARS and UNITS. The RECOMPUTE * command recomputes all values on a change of value for the state sort field:

SET SUMPREFIX=FST
TABLE FILE GGSALES                                         
SUM PRODUCT DOLLARS/I8M AS 'Dollars' IN 22 UNITS AS 'Units'
COMPUTE Formula/A19 = EDIT(DOLLARS)|'/'|EDIT(UNITS)|'=';   
COMPUTE Ratio/F8    = DOLLARS/UNITS;                       
BY ST                                                      
BY CATEGORY NOPRINT                                        
WHERE ST EQ 'CA' OR 'IL'                                   
ON ST RECOMPUTE *                                          
ON TABLE SET PAGE NOPAGE                                   
END

On the output, the alphanumeric formula is recomputed using the summed numeric fields. However, the product value is taken from the first product within each sort value, as that field is not recomputed and SUMPREFIX=FST by default:

State  Product           Dollars     Units  Formula                 Ratio
-----  -------           -------     -----  -------                 -----
CA     Capuccino      $2,957,852    237246  02957852/00237246=         12
       Biscotti       $2,770,508    222844  02770508/00222844=         12
       Coffee Grinder $1,935,863    152276  01935863/00152276=         13
                                                                         
*TOTAL CA                                                                
       Capuccino      $7,664,223    612366  07664223/00612366=         13
                                                                         
IL     Espresso       $1,398,779    109581  01398779/00109581=         13
       Biscotti       $1,561,904    120976  01561904/00120976=         13
       Coffee Grinder $1,050,243     83541  01050243/00083541=         13
                                                                         
*TOTAL IL                                                                
       Espresso       $4,010,926    314098  04010926/00314098=         13
                                                                         
                                                                         
TOTAL  Capuccino     $11,675,149    926464  11675149/00926464=         13

Note that if the SUBTOTAL summary command had been used, the formula would not have been recomputed and would have displayed the values from the first line within each sort group.

Reference: Usage Notes for Summarizing Alphanumeric Columns

Date fields and numeric and alphanumeric fields with date formatting options are not included on summary lines.
Column total lines follow the same rules as summary lines.
Summary values for ACROSS sort fields are supported.

Propagating Mixed-Case Column Titles to a HOLD File

How to:

Propagate Mixed-Case Column Titles to a HOLD File

Reference:

Usage Notes for SET ASNAMES

A report request can display custom column titles on the report output by using an AS phrase to specify the title. These names can be propagated as field names to a HOLD Master File by issuing the SET ASNAMES command. With SET ASNAMES=ON, all column titles are converted to uppercase when propagated to the HOLD file. To retain mixed-case column titles as field names, use the SET ASNAMES=MIXED command.

Syntax: How to Propagate Mixed-Case Column Titles to a HOLD File

SET ASNAMES = {OFF|ON|MIXED|FOCUS}

ON TABLE SET ASNAMES {OFF|ON|MIXED|FOCUS}

where:

OFF: Does not use the literal specified in an AS phrase as a field name in HOLD files, and does not affect the way ACROSS fields are named.
ON: Uppercases the literal specified in an AS phrase and propagates it as the field name in the HOLD Master File. Creates names for ACROSS fields that consist of the AS name value concatenated to the beginning of the ACROSS field value and controls the way ACROSS fields are named in HOLD files of any format.
MIXED: Uses the literal specified in an AS phrase for the field name, retaining the case of the AS name, and creates names for ACROSS fields that consist of the AS name value concatenated to the beginning of the ACROSS field value.
FOCUS: Uses the literal specified in an AS phrase as the field name and controls the way ACROSS fields are named only in HOLD files in FOCUS format. FOCUS is the default value.

Reference: Usage Notes for SET ASNAMES

Special characters and blanks used in the AS phrase are preserved in the field name that is created when SET ASNAMES is used. Use single quotation marks around the non-standard field names when referring to them in the newly created Master File.
Text specified in an AS phrase that contains more than 66 characters is truncated to 66 characters in the Master File.
Aliases are not carried over into the HOLD Master File. A new set of aliases is supplied automatically. These aliases are named E01 for the first field, E02 for the second, and so forth.
Duplicate field names may occur in the newly created Master File as a result of truncation or the way AS phrases have been specified. In this case, refer to the fields by their aliases (E01, E02, and so forth).
When commas are used as delimiters to break lines in the column heading, only the literal up to the first comma is used as the field name in the Master File. For example, the following produces the field name PLACE in the HOLD Master File.
```
PRINT COUNTRY AS 'PLACE,OF,ORIGIN'
```
If no AS phrase is specified for a field, the field name from the original Master File is used. The TITLE attribute specified in the Master File is not used unless SET HOLDATTR was previously issued.
When ACROSS is used in a report request and the results are extracted to a HOLD file, the columns generated by the ACROSS phrase all have the same field names in the HOLD Master File. If SET ASNAMES is issued, each new column may have a unique field name. This unique field name consists of the AS name value specified in the display command, concatenated to the beginning of the value of the field used in the ACROSS phrase. If several field names have the same letters, this approach does not work.
SCAN and FSCAN do not support mixed case field names.

Example: Retaining Mixed-Case AS Names in a HOLD Master File

The following request against the GGSALES data source creates custom mixed-case column titles for the fields:

SET ASNAMES = OFF
TABLE FILE GGSALES                        
SUM DOLLARS AS 'Sales' UNITS AS 'Quantity'
BY ST AS 'State'                          
BY CATEGORY AS 'Product'                  
ON TABLE HOLD                    
END

With SET ASNAMES=OFF, the HOLD Master File has the same field names as the original Master File:

FILENAME=HOLD    , SUFFIX=FIX     , IOTYPE=BINARY, $       
  SEGMENT=HOLD, SEGTYPE=S2, $                              
    FIELDNAME=ST, ALIAS=E01, USAGE=A02, ACTUAL=A04, $      
    FIELDNAME=CATEGORY, ALIAS=E02, USAGE=A11, ACTUAL=A12, $
    FIELDNAME=DOLLARS, ALIAS=E03, USAGE=I08, ACTUAL=I04, $ 
    FIELDNAME=UNITS, ALIAS=E04, USAGE=I08, ACTUAL=I04, $

With SET ASNAMES=ON, the field names in the HOLD Master File are the AS names, converted to uppercase:

FILENAME=HOLD    , SUFFIX=FIX     , IOTYPE=BINARY, $       
  SEGMENT=HOLD, SEGTYPE=S2, $                              
    FIELDNAME=STATE, ALIAS=E01, USAGE=A02, ACTUAL=A04, $   
    FIELDNAME=PRODUCT, ALIAS=E02, USAGE=A11, ACTUAL=A12, $ 
    FIELDNAME=SALES, ALIAS=E03, USAGE=I08, ACTUAL=I04, $   
    FIELDNAME=QUANTITY, ALIAS=E04, USAGE=I08, ACTUAL=I04, $

With SET ASNAMES=MIXED, the field names in the HOLD Master File are the AS names, in the case specified in the request:

FILENAME=HOLD    , SUFFIX=FIX     , IOTYPE=BINARY, $       
  SEGMENT=HOLD, SEGTYPE=S2, $                              
    FIELDNAME=State, ALIAS=E01, USAGE=A02, ACTUAL=A04, $   
    FIELDNAME=Product, ALIAS=E02, USAGE=A11, ACTUAL=A12, $ 
    FIELDNAME=Sales, ALIAS=E03, USAGE=I08, ACTUAL=I04, $   
    FIELDNAME=Quantity, ALIAS=E04, USAGE=I08, ACTUAL=I04, $

Column Titles for FML and BY ROWS OVER Requests

How to:

By default, the FOR field in an FML request and the last BY field in a request with BY ROWS OVER has no column title. You can use the AS phrase to specify a column title for those fields.

Syntax: How to Provide a Column Title for the FOR Field in an FML Request

FOR fieldname [AS 'coltitle'] value [OR value OR...] [AS 'text'] 
[LABEL label] OVER
.
.
.
[value [OR value ...]] [AS 'text'] [LABEL label]
END

where:

fieldname

Is the FOR field for the FML report.

coltitle

Is the column title for the FOR field on the report output.

value

Is the value (also known as a tag value) describing the data that is retrieved for this row of the report.

AS 'text'

Enables you to assign a name to a tag value, which replaces the tag value in the output. Enclose the text in single quotation marks.

label

Assigns a label to the row for reference in a RECAP expression. The label can be up to 66 characters and cannot have blanks or special characters. Each explicit label you assign must be unique.

Even if you assign an explicit label, the positional label (R1, R2, and so on) is retained internally.

Example: Adding a Column Title to a FOR Field

The following request against the GGSALES data source produces an FML report in which PRODUCT is the FOR field:

TABLE FILE GGSALES
SUM DOLLARS UNITS 
FOR PRODUCT       
'Biscotti' OVER   
'Scone' OVER      
'Mug'     
END

With no AS name for the FOR field, there is no column title above the list of products:

          Dollar Sales  Unit Sales
          ------------  ----------
Biscotti       5387773      430369
Scone          4216114      333414
Mug            4522521      360570

The following request adds a column title for the PRODUCT field:

TABLE FILE GGSALES
SUM DOLLARS UNITS 
FOR PRODUCT AS 'Product'     
'Biscotti' OVER   
'Scone' OVER      
'Mug'     
END

On the report output, there is now a column title above the list of products:

Product   Dollar Sales  Unit Sales
-------   ------------  ----------
Biscotti       5387773      430369
Scone          4216114      333414
Mug            4522521      360570

Syntax: How to Provide a Column Title for a BY ROWS OVER Field

BY sortfield AS 'coltitle' ROWS value1 [AS 'text1'] OVER value2 [AS 'text2']
[... OVER valuen [ AS 'textn']]
END

where:

sortfield: Is the last BY field in the report.
coltitle: Is the column title for the BY field on the report output.
value1: Is the sort field value that is first in the sorting sequence.
AS 'text1': Enables you to assign alternate text for the first row, which replaces the field value in the output. Enclose the text in single quotation marks.
value2: Is the sort field value that is second in the sorting sequence.
AS 'text2': Enables you to assign alternate text for the second row, which replaces the field value in the output. Enclose the text in single quotation marks.
valuen: Is the sort field value that is last in the sorting sequence.
AS 'textn': Enables you to assign alternate text for the last row, which replaces the field value in the output. Enclose the text in single quotation marks.

Example: Adding a Column Title to a BY ROWS OVER Sort Field on Report Output

The following request against the GGSALES data source has two BY fields, PRODUCT and CATEGORY. The CATEGORY sort field specifies the rows Coffee and Food:

TABLE FILE GGSALES
SUM DOLLARS UNITS 
BY PRODUCT        
BY CATEGORY ROWS  
'Coffee' OVER     
'Food'            
END

With no AS name for the CATEGORY field, there is no column title above the list of categories:

Product                   Dollar Sales  Unit Sales
-------                   ------------  ----------
Biscotti          Coffee             .           .
                  Food         5387773      430369
Capuccino         Coffee       2401556      190880
                  Food               .           .
Croissant         Coffee             .           .
                  Food         7758857      630753
Espresso          Coffee       3906243      308986
                  Food               .           .
Latte             Coffee      11000388      882849
                  Food               .           .
Scone             Coffee             .           .
                  Food         4216114      333414

The following request adds a column title for the CATEGORY field:

TABLE FILE GGSALES
SUM DOLLARS UNITS 
BY PRODUCT        
BY CATEGORY AS 'Category' ROWS  
'Coffee' OVER     
'Food'            
END

On the report output, there is now a column title above the list of categories:

Product           Category  Dollar Sales  Unit Sales
-------           --------  ------------  ----------
Biscotti          Coffee               .           .
                  Food           5387773      430369
Capuccino         Coffee         2401556      190880
                  Food                 .           .
Croissant         Coffee               .           .
                  Food           7758857      630753
Espresso          Coffee         3906243      308986
                  Food                 .           .
Latte             Coffee        11000388      882849
                  Food                 .           .
Scone             Coffee               .           .
                  Food           4216114      333414

Controlling Column Title Underlining

How to:

Control Column Title Underlining

The SET TITLELINE command allows you to control whether column titles are underlined on report output.

Syntax: How to Control Column Title Underlining

SET TITLELINE = (ON|OFF|SKIP)

ON TABLE SET TITLELINE (ON|OFF|SKIP)

where:

ON: Underlines column titles. ON is the default value.
OFF: Replaces the underline with a blank line.
SKIP: Omits both the underline and the line on which the underline would have displayed.

Note: ACROSSLINE is a synonym for TITLELINE.

Example: Controlling Column Title Underlining

The following request has a BY and an ACROSS field:

SET TITLELINE=ON
TABLE FILE GGSALES
SUM UNITS BY PRODUCT
ACROSS REGION
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, FONT=ARIAL,$
END

With the default value (ON) for SET TITLELINE, the column titles are underlined:

With SET TITLELINE=OFF, the column titles are not underlined, but the blank line where the underlines would have been is still there:

With SET TITLELINE=SKIP, both the underlines and the blank line are removed:

Improved Support for Multiple DST Operators in the Same Request

How to:

Print Independent Distinct Values

The handling of DST operators has been improved to support multiple DST operators in the same request, and the ability to use DST with ACROSS.

With these improvements, you can control the behavior of requests that use the PRINT command with multiple DST operators to achieve independent DST values. To implement this functionality, set the PRINTDST parameter to NEW.

Syntax: How to Print Independent Distinct Values

SET PRINTDST = {OLD|NEW}

where:

OLD: Processes multiple DST operators in a PRINT request as nested BY fields, making them dependent on each other. OLD is the default value.
NEW: Processes multiple DST operators in a PRINT request as totally independent objects.

Testing For Null Values in Left Outer Join Requests

How to:

Test For Null Values In a Left Outer Join

Reference:

Usage Notes for SET SHORTPATH = SQL

In FOCUS, the command SET ALL = ON or JOIN LEFT_OUTER specifies a left outer join. With a left outer join, all records from the host file display on the report output. If a cross-referenced segment instance does not exist for a host segment instance (called a short path), the report output displays missing values for the fields from the cross-referenced segment. However, the fields are not assigned missing values for testing purposes.

If there is a screening condition on the dependent segment, those dependent segment instances that do not satisfy the screening condition are omitted from the report output, and so are their corresponding host segment instances. With missing segment instances, tests for missing values fail because the fields in the segment have not been assigned missing values.

When a relational engine performs a left outer join, it processes host records with missing cross-referenced segment instances slightly differently from the way FOCUS processes those records when both of the following conditions apply:

There is a screening condition on the cross-referenced segment.
A host segment instance does not have a corresponding cross-referenced segment instance.

When these two conditions are true, FOCUS omits the host record from the report output, while relational engines supply null values for the fields from the dependent segment and then apply the screening condition. If the missing values pass the screening condition, the entire record is retained on the report output. This type of processing is useful for finding or counting all host records that do not have matching records in the cross-referenced file or for creating a DEFINE-based join from the cross-referenced segment with the missing instance to another dependent segment.

If you want FOCUS to assign null values to the fields in a missing segment instance when a left outer join is in effect, you can issue the command SET SHORTPATH=SQL.

Syntax: How to Test For Null Values In a Left Outer Join

SET SHORTPATH = {FOCUS|SQL}

where:

FOCUS

Omits a host segment from the report output when it has no corresponding cross-referenced segment and the report has a screening condition on the cross-referenced segment.

SQL

Supplies missing values for the fields in a missing cross-referenced segment in an outer join. Applies screening conditions against this record and retains the record on the report output if it passes the screening test.

Note: There must be an outer join in effect, either as a result of the SET ALL=ON command or a JOIN LEFT_OUTER command (either inside or outside of the Master File).

Reference: Usage Notes for SET SHORTPATH = SQL

A FOCUS data source is supported as the host file in a join used with SET SHORTPATH = SQL, but not as the cross-referenced file.

Example: Testing for Null Values in Left Outer Joins

The following procedure creates two Oracle tables, ORAEMP and ORAEDUC, that will be used in a join.

TABLE FILE EMPLOYEE
SUM LAST_NAME FIRST_NAME CURR_SAL CURR_JOBCODE DEPARTMENT
BY EMP_ID
ON TABLE HOLD AS ORAEMP FORMAT SQLORA
END
-RUN
TABLE FILE EDUCFILE
SUM COURSE_CODE COURSE_NAME
BY EMP_ID BY DATE_ATTEND
ON TABLE HOLD AS ORAEDUC FORMAT SQLORA
END

The following request joins the two Oracle tables and creates a left outer join (SET ALL = ON).

JOIN EMP_ID IN ORAEMP TO ALL EMP_ID IN ORAEDUC AS J1
SET ALL = ON
TABLE FILE ORAEMP
PRINT COURSE_CODE COURSE_NAME
BY EMP_ID
END

Since the join is an outer join, all ORAEMP rows display on the report output. ORAEMP rows with no corresponding ORAEDUC row display the missing data symbol for the fields from the ORAEDUC table.

EMP_ID     COURSE_CODE     COURSE_NAME  
------     -----------     -----------
071382660  101             FILE DESCRPT & MAINT          
112847612  101             FILE DESCRPT & MAINT          
           103             BASIC REPORT PREP FOR PROG    
117593129  101             FILE DESCRPT & MAINT          
           103             BASIC REPORT PREP FOR PROG    
           201             ADVANCED TECHNIQUES           
           203             FOCUS INTERNALS               
119265415  108             BASIC RPT NON-DP MGRS         
119329144  .               .                             
123764317  .               .                             
126724188  .               .                             
219984371  .               .                             
326179357  104             FILE DESC & MAINT NON-PROG    
           106             TIMESHARING WORKSHOP          
           102             BASIC REPORT PREP NON-PROG    
           301             DECISION SUPPORT WORKSHOP     
           202             WHAT'S NEW IN FOCUS           
451123478  101             FILE DESCRPT & MAINT          
543729165  .               .                             
818692173  107             BASIC REPORT PREP DP MGRS

The following request adds a screening condition on the ORAEDUC segment. To satisfy the screening condition, the course name must either contain the characters BASIC or be missing.

JOIN CLEAR 
JOIN EMP_ID IN ORAEMP TO ALL EMP_ID IN ORAEDUC AS J1
SET ALL = ON
TABLE FILE ORAEMP
PRINT COURSE_CODE COURSE_NAME
BY EMP_ID
WHERE COURSE_NAME CONTAINS 'BASIC' OR COURSE_NAME IS MISSING
END

However, with SET ALL = ON, the rows with missing values are not retained on the report output.

EMP_ID     COURSE_CODE     COURSE_NAME      
------     -----------     -----------      
112847612  103             BASIC REPORT PREP FOR PROG    
117593129  103             BASIC REPORT PREP FOR PROG    
119265415  108             BASIC RPT NON-DP MGRS         
326179357  102             BASIC REPORT PREP NON-PROG    
818692173  107             BASIC REPORT PREP DP MGRS

The following request adds the SET SHORTPATH = SQL command.

JOIN CLEAR 
JOIN EMP_ID IN ORAEMP TO ALL EMP_ID IN ORAEDUC AS J1
SET ALL = ON
SET SHORTPATH=SQL
TABLE FILE ORAEMP
PRINT COURSE_CODE COURSE_NAME
BY EMP_ID
WHERE COURSE_NAME CONTAINS 'BASIC' OR COURSE_NAME IS MISSING
END

The report output now displays both the records containing the characters BASIC and those with missing values.

EMP_ID     COURSE_CODE     COURSE_NAME      
------     -----------     -----------      
112847612  103             BASIC REPORT PREP FOR PROG    
117593129  103             BASIC REPORT PREP FOR PROG    
119265415  108             BASIC RPT NON-DP MGRS         
119329144  .               .                             
123764317  .               .                             
126724188  .               .                             
219984371  .               .                             
326179357  102             BASIC REPORT PREP NON-PROG    
543729165  .               .                             
818692173  107             BASIC REPORT PREP DP MGRS

Example: Finding Host Records That Have No Matching Cross-Referenced Records

The following request counts and lists those employees who have taken no courses.

JOIN LEFT_OUTER EMP_ID IN ORAEMP TO ALL EMP_ID IN ORAEDUC AS J1
SET ALL = ON
SET SHORTPATH=SQL
TABLE FILE ORAEMP
COUNT EMP_ID
LIST EMP_ID LAST_NAME FIRST_NAME
WHERE COURSE_NAME IS MISSING
END

The output is:

EMP_ID                                           
COUNT    LIST  EMP_ID     LAST_NAME        FIRST_NAME
------   ----  ------     ---------        ---------- 
     5      1  119329144  BANNING          JOHN      
            2  123764317  IRVING           JOAN      
            3  126724188  ROMANS           ANTHONY   
            4  219984371  MCCOY            JOHN      
            5  543729165  GREENSPAN        MARY

Ignoring ON Phrases for Absent Fields

How to:

Control Processing of ON Phrases

To make report requests flexible, options may be included that are not needed in every situation. User selections then control the options used for each execution of the request.

Options, such as subtotals and page breaks, are included in a report request using an ON phrase. Some options specified with an ON phrase require a sort field, and others accept a sort or display field. By default, if the field referenced in the ON phrase is not present in the request, or if the option is not supported with the type of field specified, the following message is generated and processing terminates:

(FOC013) The 'ON FIELDNAME' FIELD IS NOT A SORT FIELD: sortfield

You can use the SET ONFIELD=IGNORE command to instruct FOCUS to ignore ON phrases that reference absent fields or fields that are not supported by the specified option.

Note that any field used must be present in the Master File for the data source or the following message is generated and execution terminates:

(FOC003) THE FIELDNAME IS NOT RECOGNIZED: field

Syntax: How to Control Processing of ON Phrases

SET ONFIELD = {ALL|IGNORE}

ON TABLE SET ONFIELD {ALL|IGNORE}

where:

ALL: Issues a message and terminates execution when a field referenced in an ON phrase is not present in the request. ALL is the default value.
IGNORE: Ignores ON phrases that reference fields that are not present in the request, as well as ON phrases that include options not supported by the type of field specified.

Example: Ignoring ON Phrases for Absent Fields

The following request against the EMPDATA data source has ON phrases for the fields DEPT, DIV, and PIN. PIN is a sort field, but the other sort field must be entered at run time as the amper variable &F1:

SET USER = EUSER               
 TABLE FILE EMPDATA            
   SUM SALARY                  
   BY &F1                      
   BY PIN                      
      ON DEPT SKIP-LINE NOSPLIT
      ON &F1  SUBTOTAL         
      ON DIV   PAGE-BREAK      
      ON TABLE SET ONFIELD ALL
       END

Run the request supplying the value DEPT for the variable &F1. The following messages are generated:

 ERROR AT OR NEAR LINE      8  IN PROCEDURE IGNORE3 FOCEXEC *
(FOC013) THE 'ON FIELDNAME' FIELD IS NOT A SORT FIELD: DIV   
 BYPASSING TO END OF COMMAND                                 
(FOC009) INCOMPLETE REQUEST STATEMENT

Now change the value of the ONFIELD parameter to IGNORE and run the request again, supplying the value DEPT for the variable &F1. The partial output is:

DEPT                  PIN                 SALARY
----                  ---                 ------
                                                
ACCOUNTING            000000070       $83,000.00
                      000000100       $32,400.00
                      000000300       $79,000.00
                      000000370       $62,500.00
                      000000400       $26,400.00
                                                
*TOTAL ACCOUNTING                    $283,300.00
                                                
                                                
ADMIN SERVICES        000000170       $30,800.00
                      000000180       $25,400.00
                                                
*TOTAL ADMIN SERVICES                 $56,200.00

Controlling Collation Sequence

How to:

Establish a Binary or Case-Insensitive Collation Sequence

Reference:

Usage Notes for SET COLLATION

Collation is defined as a set of rules that apply to the ordering and matching of all language elements that involve comparison of two values. A wide variety of elements are affected by this feature, for example sorting, aggregation, WHERE conditions, and StyleSheets. By default, items are sorted based on their binary values. This release introduces the settings SRV_CI and SRV_CS, case-insensitive and case-sensitive collation based on the LANGUAGE setting. Case-insensitive collation means that all WHERE clauses and sorts ignore the case of the elements being compared. COLLATION is a session level setting (it is not supported in an ON TABLE phrase and should be set in the edasprof profile).

This document describes collation in binary, code page, case-sensitive, or case-insensitive order. The collation setting applies to alphanumeric values.

Syntax: How to Establish a Binary or Case-Insensitive Collation Sequence

Add the following command to the edasprof profile:

SET COLLATION = {BINARY|SRV_CI|SRV_CS|CODEPAGE}

where:

BINARY

Bases the collation sequence on binary values.

Note: The FOCPARM profile shipped with FOCUS sets COLLATION to BINARY for consistency with prior releases.

SRV_CI

Bases collation sequence on the LANGUAGE setting, and is case-insensitive.

SRV_CS

Bases collation sequence on the LANGUAGE setting, and is case-sensitive.

CODEPAGE

Bases collation sequence on the code page in effect, and is case-sensitive. CODEPAGE is the default value.

In most cases, CODEPAGE is the same as BINARY. The only differences are for Danish, Finnish, German, Norwegian, and Swedish in an EBCDIC environment.

Reference: Usage Notes for SET COLLATION

SUFFIX=FIX or SUFFIX=FOCUS/XFOCUS HOLD files created in one mode may not be usable as targets for a JOIN in another mode if the join field is on alphanumeric data with mixed cases.
FIXRETRIEVE is supported only for binary data, so setting COLLATION to anything other than BINARY will turn FIXRETRIEVE OFF, which may affect join performance.

Rules for Sorting and Aggregation

Records with the same characters in the same order, but with variations in case, are considered to be identical. If multiple input records have these variations, the value used is from the first such record.
In a detail level report, the sort value is the same for each output record. That value will be the one from the input record that had the lowest value (collated first).
When the MIN (or the MAX) value for two or more alphanumeric display fields having a given instance of the sort field values is the same by case-insensitive collation, but the two values vary by case in some positions, the one retained is the last one in the input file (highest input record number) when SUMPREF=LST and the first (lowest record number) when SUMPREF=FST.

Example: Using Binary and Case-Insensitive Collation Sequence for Sorting

The following request creates a FOCUS data source named COLLATE that has some records with product names that differ only by case of one letter:

CREATE FILE COLLATE                                                
-RUN                                                               
MODIFY FILE COLLATE                                                
FIXFORM PROD_NUM/C4 PRODNAME/C30 QTY_IN_STOCK/C7 PRICE/C12 COST/C12
CHECK OFF                                                          
DATA                                                               
10042 Hd VCR LCD Menu               43068      179.00      129.00  
10052 HD VCR LCD Menu               43068      179.00      129.00  
1006Combo Player - 4 HD VCR + DVD   13527      399.00      289.00  
1007Combo Player - 4 Hd VCR + DVD   13527      399.00      289.00  
1008DVD Upgrade Unit for Cent. VCR    199      199.00      139.00  
1010750SL Digital Camcorder 300 X   10758      999.00      750.00  
1012650DL Digital Camcorder 150 X    2972      899.00      710.00  
1014340SX Digital Camera 65K P        990      249.00      199.00  
1015340SX digital Camera 65K P        990      249.00      199.00
1016330DX Digital Camera 1024K P    12707      279.00      199.00
1018250 8MM Camcorder 40 X          60073      399.00      320.00
1019250 8mm Camcorder 40 X          60073      399.00      320.00
1020150 8MM Camcorder 20 X           5961      319.00      240.00
1022120 VHS-C Camcorder 40 X         2300      399.00      259.00
1024110 VHS-C Camcorder 20 X         4000      349.00      249.00
1026AR2 35mm Camera 8 X             12444      129.00       95.00
1029AR2 35MM Camera 8 X             11499      109.00       79.00
1028AR3 35MM Camera 10 X            11499      109.00       79.00
1030QX Portable CD Player           22000      169.00       99.00
1032R5 Micro Digital Tape Recorder   1990       89.00       69.00
1034ZT Digital PDA - Commercial     21000      499.00      349.00
1036ZC Digital PDA - Standard       33000      299.00      249.00
END

The following request prints the values of PRODNAME in the order in which they are encountered in the input stream:

TABLE FILE COLLATE
PRINT PROD_NUM PRODNAME
END

On the output, the rows with product numbers 1004 and 1005 differ only in the case of the letter d in HD. The record with the lowercase d is before the record with the uppercase D. The rows with record numbers 1006 and 1007 also differ only in the case of the letter d in HD. In this case, the record with the uppercase D is before the record with the lowercase d:

Product  Product                       
Number:  Name:                         
-------  -------                       
1004     2 Hd VCR LCD Menu             
1005     2 HD VCR LCD Menu             
1006     Combo Player - 4 HD VCR + DVD 
1007     Combo Player - 4 Hd VCR + DVD 
1008     DVD Upgrade Unit for Cent. VCR
1010     750SL Digital Camcorder 300 X 
1012     650DL Digital Camcorder 150 X 
1014     340SX Digital Camera 65K P    
1015     340SX digital Camera 65K P    
1016     330DX Digital Camera 1024K P  
1018     250 8MM Camcorder 40 X        
1019     250 8mm Camcorder 40 X        
1020     150 8MM Camcorder 20 X        
1022     120 VHS-C Camcorder 40 X      
1024     110 VHS-C Camcorder 20 X      
1026     AR2 35mm Camera 8 X           
1029     AR2 35MM Camera 8 X           
1028     AR3 35MM Camera 10 X          
1030     QX Portable CD Player         
1032     R5 Micro Digital Tape Recorder
1034     ZT Digital PDA - Commercial   
1036     ZC Digital PDA - Standard

The next request sorts the output in BINARY order. The setting COLLATION = BINARY is in effect:

TABLE FILE COLLATE      
PRINT PROD_NUM          
BY PRODNAME             
END

In an EBCDIC environment, the records with the lowercase letters sort in front of the records with the uppercase letters, so the row with product number 1007 sorts in front of the row with product number 1006:

Product                         Product
Name:                           Number:
-------                         -------
AR2 35mm Camera 8 X             1026   
AR2 35MM Camera 8 X             1029   
AR3 35MM Camera 10 X            1028   
Combo Player - 4 Hd VCR + DVD   1007   
Combo Player - 4 HD VCR + DVD   1006   
DVD Upgrade Unit for Cent. VCR  1008   
QX Portable CD Player           1030   
R5 Micro Digital Tape Recorder  1032   
ZC Digital PDA - Standard       1036   
ZT Digital PDA - Commercial     1034   
110 VHS-C Camcorder 20 X        1024   
120 VHS-C Camcorder 40 X        1022   
150 8MM Camcorder 20 X          1020   
2 Hd VCR LCD Menu               1004   
2 HD VCR LCD Menu               1005   
250 8mm Camcorder 40 X          1019   
250 8MM Camcorder 40 X          1018   
330DX Digital Camera 1024K P    1016   
340SX digital Camera 65K P      1015   
340SX Digital Camera 65K P      1014   
650DL Digital Camcorder 150 X   1012   
750SL Digital Camcorder 300 X   1010

In an ASCII environment, the records with the uppercase letters sort in front of the records with the lowercase letters, so the row with product number 1005 sorts in front of the row with product number 1004:

Product                         Product 
Name:                           Number:
-------                         ------- 
110 VHS-C Camcorder 20 X        1024   
120 VHS-C Camcorder 40 X        1022   
150 8MM Camcorder 20 X          1020   
2 HD VCR LCD Menu               1005   
2 Hd VCR LCD Menu               1004   
250 8MM Camcorder 40 X          1018   
250 8mm Camcorder 40 X          1019   
330DX Digital Camera 1024K P    1016   
340SX Digital Camera 65K P      1014   
340SX digital Camera 65K P      1015   
650DL Digital Camcorder 150 X   1012   
750SL Digital Camcorder 300 X   1010   
AR2 35MM Camera 8 X             1029   
AR2 35mm Camera 8 X             1026   
AR3 35MM Camera 10 X            1028   
Combo Player - 4 HD VCR + DVD   1006   
Combo Player - 4 Hd VCR + DVD   1007   
DVD Upgrade Unit for Cent. VCR  1008   
QX Portable CD Player           1030   
R5 Micro Digital Tape Recorder  1032   
ZC Digital PDA - Standard       1036   
ZT Digital PDA - Commercial     1034

With COLLATION set to SRV_CI and a sort on the PRODNAME field, the uppercase and lowercase letters have the same value, so the row displays only once for multiple record numbers. For example, the rows with product numbers 1004 and 1005 display with the same PRODNAME value and the sort field value for the display is the first one in the input stream.

The following shows the output in an EBCDIC environment:

Product                         Product
Name:                           Number:
-------                         -------
AR2 35mm Camera 8 X             1026   
                                1029   
AR3 35MM Camera 10 X            1028   
Combo Player - 4 HD VCR + DVD   1006   
                                1007   
DVD Upgrade Unit for Cent. VCR  1008   
QX Portable CD Player           1030   
R5 Micro Digital Tape Recorder  1032   
ZC Digital PDA - Standard       1036   
ZT Digital PDA - Commercial     1034   
110 VHS-C Camcorder 20 X        1024   
120 VHS-C Camcorder 40 X        1022   
150 8MM Camcorder 20 X          1020   
2 Hd VCR LCD Menu               1004   
                                1005   
250 8MM Camcorder 40 X          1018   
250 8MM Camcorder 40 X          1019
330DX Digital Camera 1024K P    1016
340SX Digital Camera 65K P      1014
                                1015
650DL Digital Camcorder 150 X   1012
750SL Digital Camcorder 300 X   1010

The following shows the output in an ASCII environment:

Product                         Product   
Name:                           Number:   
-------                         -------    
110 VHS-C Camcorder 20 X        1024   
120 VHS-C Camcorder 40 X        1022   
150 8MM Camcorder 20 X          1020   
2 Hd VCR LCD Menu               1004   
                                1005   
250 8MM Camcorder 40 X          1018   
                                1019   
330DX Digital Camera 1024K P    1016   
340SX Digital Camera 65K P      1014   
                                1015   
650DL Digital Camcorder 150 X   1012   
750SL Digital Camcorder 300 X   1010   
AR2 35mm Camera 8 X             1026   
                                1029   
AR3 35MM Camera 10 X            1028   
Combo Player - 4 HD VCR + DVD   1006   
                                1007   
DVD Upgrade Unit for Cent. VCR  1008   
QX Portable CD Player           1030   
R5 Micro Digital Tape Recorder  1032   
ZC Digital PDA - Standard       1036   
ZT Digital PDA - Commercial     1034

Example: Using a Binary and Case-Insensitive Collation Sequence for Selection

The following request against the COLLATE data source selects records in which the PRODNAME contains the characters HD:

TABLE FILE COLLATE          
PRINT PROD_NUM PRODNAME     
WHERE PRODNAME CONTAINS 'HD'
END

With COLLATION set to BINARY, only the records with an exact match (uppercase HD) are selected. The output is:

Product  Product                      
Number:  Name:                        
-------  -------                      
1005     2 HD VCR LCD Menu            
1006     Combo Player - 4 HD VCR + DVD

Running the same request but changing the COLLATION parameter to SRV_CI selects all records with any combination of uppercase and lowercase values for H and D. The rows are displayed in the order in which they appeared in the data source:

Product  Product                      
Number:  Name:                        
-------  -------                      
1004     2 Hd VCR LCD Menu            
1005     2 HD VCR LCD Menu            
1006     Combo Player - 4 HD VCR + DVD
1007     Combo Player - 4 Hd VCR + DVD

Using Distinct Operators on Multiple Fields

This feature allows the DST operator to be used on more than one field within a single request and allows the ACROSS horizontal sort to be used with fields that have the DST operator applied. In requests with multiple display fields, the DST operator must be used at the lowest level of aggregation (the last display command in the request).

When used in a request against a relational database, the multiple DST syntax is optimized where the relational engine supports it.

Example: Counting Distinct Field Values for Multiple Fields

The following request against the GGSALES data source counts the distinct number of products and categories by state.

TABLE FILE GGSALES                    
  SUM CNT.DST.PRODUCT CNT.DST.CATEGORY
BY ST                                 
END

The output is:

       COUNT     COUNT   
       DISTINCT  DISTINCT
State  PRODUCT   CATEGORY
-----  --------  --------
CA           10         3
CT           10         3
FL           10         3
GA           10         3
IL            9         3
MA           10         3
MO            9         3
NY           10         3
TN           10         3
TX            9         3
WA           10         3

Example: Counting Distinct Field Values With Multiple Display Commands

The following request against the GGSALES data source counts the total number of records by region, then the number of records, distinct categories, and distinct products by region and by state. The DST or CNT.DST operator can be used only with the last display command.

TABLE FILE GGSALES                     
COUNT CATEGORY AS 'TOTAL,COUNT'        
  BY REGION                            
SUM CNT.CATEGORY AS 'STATE,COUNT'      
    CNT.DST.CATEGORY    CNT.DST.PRODUCT
  BY REGION                            
  BY ST                                
END

The output is:

                                  COUNT     COUNT   
             TOTAL         STATE  DISTINCT  DISTINCT
Region       COUNT  State  COUNT  CATEGORY  PRODUCT 
------       -----  -----  -----  --------  --------
Midwest       1085  IL       362         3         9
                    MO       361         3         9
                    TX       362         3         9
Northeast     1084  CT       361         3        10
                    MA       360         3        10
                    NY       363         3        10
Southeast     1082  FL       361         3        10
                    GA       361         3        10
                    TN       360         3        10
West          1080  CA       721         3        10
                    WA       359         3        10

Multiple Conditional Headings and Footings

How to:

Reference:

Usage Notes for Multiple Headings

A TABLE request can have more than one page heading or footing. For each heading or footing, a WHEN clause against the data being retrieved can determine whether the heading or footing displays on the report output.

In a heading, the data for the WHEN clause and data field values displayed in the heading are based on the first line on the page. In a footing, the data for the WHEN clause and the data field values displayed in the footing are based on the last line on the page.

The CONDITION StyleSheet attribute enables you to identify a specific WHEN clause, so that you can style each heading or footing separately.

Syntax: How to Specify a Heading or Footing With a WHEN Clause

{HEADING [CENTER]|FOOTING}
"text_and_data1"
   .
   .
   .
"text_and_datan"
WHEN expression

where:

text_and_data1, text_and_datan: Is the text and data for each heading or footing line.
expression: Is an expression that resolves to TRUE or FALSE (1 or 0). If its value resolves to TRUE, the heading or footing is displayed. If the expression resolves to FALSE, the heading or footing is not displayed.

Reference: Usage Notes for Multiple Headings

HEADING CENTER and FOOTING CENTER apply only to the specific heading or footing in which they are specified.
A request can have a total of 120 headings, 120 footings, 120 subheadings, and 120 subfootings.
Once you use the BOTTOM option on a footing, all subsequent footings also go to the bottom of the page.

Syntax: How to Style a Specific Heading or Footing

TYPE = {HEADING|FOOTING}, CONDITION = n, ... ,$

where:

n: Is the number of the WHEN condition in the heading or footing from top to bottom. If not specified, formatting applies to all headings and footings.

Example: Using Multiple Headings With WHEN Clauses

The following request against the EMPLOYEE data source displays a page for each employee with salary and job code information for that employee. If the employee is female, the page starts with a heading that refers to the employee as Ms. If the employee is male, the page starts with a heading that refers to the employee as Mr. If the department is MIS, the signature is Barbara Cross. If the department is PRODUCTION, the signature is John Banning.

DEFINE FILE EMPLOYEE                                             
GENDER/A1 = DECODE FIRST_NAME(ALFRED 'M' RICHARD 'M' JOHN 'M'    
  ANTHONY 'M' ROGER 'M' MARY 'F' DIANE 'F' JOAN 'F' ROSEMARIE 'F'
  BARBARA 'F');                                                  
MIXEDNAME/A15 = LCWORD(15, LAST_NAME, MIXEDNAME);                
NAME/A16 = MIXEDNAME||',';                                       
END                                                              
                                                                 
TABLE FILE EMPLOYEE                                              
PRINT LAST_NAME NOPRINT GENDER NOPRINT      NAME NOPRINT         
HEADING                                                          
"Dear Ms. <NAME"                                                 
   WHEN GENDER EQ 'F';                                           
HEADING                                                          
"Dear Mr. <NAME>"                                                
      WHEN GENDER EQ 'M';                                        
HEADING                                                           
" "                                                 
HEADING                                             
"This is to inform you that your current salary is "
"<CURR_SAL and your job code is <CURR_JOBCODE>."
" "                                                 
"Sincerely,"                                       
HEADING                                             
"Barbara Cross "                                   
  WHEN DEPARTMENT EQ 'MIS';                         
HEADING                                             
"John Banning   "                                  
  WHEN DEPARTMENT EQ 'PRODUCTION' ;                 
WHERE LAST_NAME NE 'BANNING' OR 'CROSS'             
BY EMP_ID NOPRINT PAGE-BREAK                        
ON TABLE SET PAGE NOPAGE
END

The first page of output is for a male in the PRODUCTION department.

Dear Mr. Stevens,                                
                                                 
This is to inform you that your current salary is
     $11,000.00 and that A07 is your job code.   
                                                 
Sincerely,                                       
John Banning

The second page of output is for a female in the MIS department.

Dear Ms. Smith,                                  
                                                 
This is to inform you that your current salary is
     $13,200.00 and that B14 is your job code.   
                                                 
Sincerely,                                       
Barbara Cross

Example: Styling Multiple Headings With WHEN

The following request displays a page for each employee with salary and job code information for that employee. The first WHEN condition applies if the employee is female. The second WHEN condition applies if the employee is male. The third WHEN condition applies if the department is MIS. The fourth WHEN condition applies if the department is PRODUCTION. The StyleSheet declarations include styling elements for the second and third conditions.

DEFINE FILE EMPLOYEE                                             
GENDER/A1 = DECODE FIRST_NAME(ALFRED 'M' RICHARD 'M' JOHN 'M'    
  ANTHONY 'M' ROGER 'M' MARY 'F' DIANE 'F' JOAN 'F' ROSEMARIE 'F'
  BARBARA 'F');                                                  
MIXEDNAME/A15 = LCWORD(15, LAST_NAME, MIXEDNAME);                
NAME/A16 = MIXEDNAME||',';                                       
END                                                              
                                                                 
TABLE FILE EMPLOYEE                                              
PRINT LAST_NAME NOPRINT GENDER NOPRINT NAME NOPRINT         
HEADING                                                          
"Dear Ms. <NAME"                                                 
   WHEN GENDER EQ 'F';                                           
HEADING                                                          
"Dear Mr. <NAME>"                                                
      WHEN GENDER EQ 'M';                                        
HEADING                                                           
" "                                                 
HEADING                                             
"This is to inform you that your current salary is "
"<CURR_SAL and your job code is <CURR_JOBCODE>."
" "                                                 
"Sincerely,"                                       
HEADING                                             
"Barbara Cross "                                   
  WHEN DEPARTMENT EQ 'MIS';                         
HEADING                                             
"John Banning   "                                  
  WHEN DEPARTMENT EQ 'PRODUCTION' ;                 
WHERE LAST_NAME NE 'BANNING' OR 'CROSS'             
BY EMP_ID NOPRINT PAGE-BREAK                        
ON TABLE SET PAGE NOPAGE
ON TABLE HOLD FORMAT PDF     
ON TABLE SET STYLE *                     
TYPE=HEADING, CONDITION=2, STYLE=ITALIC,$
TYPE=HEADING, CONDITION=3, STYLE=BOLD,$  
ENDSTYLE                                 
END

In the StyleSheet for the request, heading lines displayed because of the first condition are in an italic typeface and heading lines displayed because of the third condition are in a bold typeface.

The first page of output is for a male employee, so the greeting line is in an italic typeface.

The second page of output is for an employee in the MIS department, so the signature line is in a bold typeface.

Displaying Summary Lines Above the Data

How to:

Control Placement of Summary Lines

The SET SUBTOTALS command controls whether summary lines are displayed above or below the detail lines in a report. The summary commands affected include SUBTOTAL, SUB-TOTAL, RECOMPUTE, SUMMARIZE, COMPUTE, RECAP, and COLUMN-TOTAL.

Syntax: How to Control Placement of Summary Lines

SET SUBTOTALS = {ABOVE|BELOW}

ON TABLE SET SUBTOTALS {ABOVE|BELOW}

where:

ABOVE: Places summary lines above the detail lines and displays the sort field values on every detail line of the report output.
BELOW: Places summary lines below the detail lines. BELOW is the default value.

Example: Placing Subtotals Above the Data

The following request against the EMPLOYEE data source sums deduction amounts and gross salaries by department, deduction code, and last name. It then subtotals the deduction amounts and gross salaries for each department. The following request places the subtotals below the detail lines (the default).

TABLE FILE EMPLOYEE               
SUM DED_AMT GROSS                 
BY DEPARTMENT                     
BY DED_CODE                       
  BY LAST_NAME                    
WHERE BANK_ACCT NE 0              
WHERE DED_CODE EQ 'FICA' OR 'CITY'
  ON DEPARTMENT SUBTOTAL          
  ON TABLE SET SUBTOTALS BELOW    
  ON TABLE SET PAGE NOPAGE        
END

The output is:

DEPARTMENT  DED_CODE  LAST_NAME                DED_AMT            GROSS
----------  --------  ---------                -------            -----
MIS         CITY      BLACKWOOD                 $31.76        $9,075.00
                      CROSS                     $82.69       $22,013.77
                      JONES                     $14.01        $6,099.50
            FICA      BLACKWOOD              $2,223.37        $9,075.00
                      CROSS                  $5,788.01       $22,013.77
                      JONES                    $980.64        $6,099.50
                                                                       
*TOTAL DEPARTMENT MIS                        $9,120.47       $74,376.54
                                                                       
PRODUCTION  CITY      BANNING                    $7.42        $2,475.00
                      IRVING                    $60.24       $17,094.00
                      MCKNIGHT                  $18.26        $9,129.99
            FICA      BANNING                  $519.75        $2,475.00
                      IRVING                 $4,216.53       $17,094.00
                      MCKNIGHT               $1,278.21        $9,129.99
                                                                       
*TOTAL DEPARTMENT PRODUCTION                 $6,100.40       $57,397.98
                                                                       
                                                                       
TOTAL                                       $15,220.88      $131,774.52

The following is the same request, but with the subtotals placed above the detail lines.

TABLE FILE EMPLOYEE               
SUM DED_AMT GROSS                 
BY DEPARTMENT                     
BY DED_CODE                       
  BY LAST_NAME                    
WHERE BANK_ACCT NE 0              
WHERE DED_CODE EQ 'FICA' OR 'CITY'
  ON DEPARTMENT SUBTOTAL          
  ON TABLE SET SUBTOTALS ABOVE    
  ON TABLE SET PAGE NOPAGE        
END

On the output, the grand total line comes first, then the subtotal for the MIS department followed by the detail lines for the MIS department, followed by the subtotal for the PRODUCTION department and its detail lines. Note that all sort field values display on each line of the report output.

DEPARTMENT  DED_CODE  LAST_NAME                DED_AMT            GROSS
----------  --------  ---------                -------            -----
TOTAL                                       $15,220.88      $131,774.52
*TOTAL DEPARTMENT MIS                        $9,120.47       $74,376.54
                                                                       
MIS         CITY      BLACKWOOD                 $31.76        $9,075.00
MIS         CITY      CROSS                     $82.69       $22,013.77
MIS         CITY      JONES                     $14.01        $6,099.50
MIS         FICA      BLACKWOOD              $2,223.37        $9,075.00
MIS         FICA      CROSS                  $5,788.01       $22,013.77
MIS         FICA      JONES                    $980.64        $6,099.50
                                                                       
*TOTAL DEPARTMENT PRODUCTION                 $6,100.40       $57,397.98
                                                                       
PRODUCTION  CITY      BANNING                    $7.42        $2,475.00
PRODUCTION  CITY      IRVING                    $60.24       $17,094.00
PRODUCTION  CITY      MCKNIGHT                  $18.26        $9,129.99
PRODUCTION  FICA      BANNING                  $519.75        $2,475.00
PRODUCTION  FICA      IRVING                 $4,216.53       $17,094.00
PRODUCTION  FICA      MCKNIGHT               $1,278.21        $9,129.99

Selecting Records Without Wildcard Characters

How to:

Deactivate Wildcard Characters

An IF or WHERE clause in a request selects records based on whether they satisfy the criteria specified in an expression. However, an IF or WHERE clause using the EQ (is equal to) or IS operator treats the dollar sign character ($) as a wildcard character, meaning that any character in that position satisfies the criteria. Also, the dollar sign followed by an asterisk ($*) is a wildcard combination, meaning that any combination of characters satisfies the criteria. By default, therefore, it is impossible to use this syntax to select only those records that contain a $ character or a $* character combination.

The expression in an IF clause is more restrictive than the expression in a WHERE clause. An IF expression must compare a field value to one or more alphanumeric literals. A WHERE expression can compare two fields and can contain compound expressions created using the logical operators AND, OR, and NOT. Depending on the complexity of the expression, the WHERE clause can be broken down into one or more IF clauses.

In IF clauses, and those WHERE clauses that can be translated into one or more IF clauses, you can treat the $ and $* characters as normal characters rather than wildcards by issuing the SET EQTEST=EXACT command.

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 e-mail 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 e-mail 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 e-mail 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

TITLE and DESCRIPTION for a Virtual Field

How to:

In a DEFINE FILE command or a DEFINE FUNCTION, you can specify a TITLE and a DESCRIPTION for each virtual field, just as you can for a DEFINE in a Master File.

Syntax: How to Add a TITLE and DESCRIPTION to a Virtual Field

DEFINE FILE filename dfieldname[/format] [WITH rfield] [MISSING {ON|OFF} 
 [[NEEDS] [SOME|ALL] [DATA]]
 [TITLE 'line1[,line2 ...']
 [DESCRIPTION 'description'] = expression;
   .
   .
   .
END

where:

filename: Is the name of the file for which the virtual field is being defined.
dfieldname: Is the name of the virtual field.
format: Is the format of the virtual field. The default format is D12.2.
rfield: Associates a virtual field with a data source segment containing a real field.
line1, line2...: Are the lines of the default column title to be displayed for the virtual field unless overridden by an AS phrase.
description: Is the description to be associated with the virtual field.
expression: Is the expression that, when evaluated, defines the virtual field value.

Syntax: How to Add a TITLE and DESCRIPTION to a Virtual Field in a DEFINE FUNCTION

DEFINE FUNCTION functionname (argument1/format1,..., argumentn/formatn) dfieldname[/format] [MISSING {ON|OFF}] 
 [[NEEDS] [SOME|ALL] [DATA]]
 [TITLE 'line1[,line2 ...']
 [DESCRiption 'description'] = expression;
   .
   .
   .functionname/format = [result_expression];
END

where:

functionname: Is the name of the DEFINE FUNCTION.
argument1/format1, ..., argumentn/formatn: Are the function input arguments and their formats.
dfieldname: Is the name of the virtual field.
format: Is the format of the virtual field. The default format is D12.2.
line1,line2 ...: Are the lines of the default column title to be displayed for the virtual field unless overridden by an AS phrase.
description: Is the description to be associated with the virtual field.
result_expression: Is the expression that, when evaluated, defines the DEFINE FUNCTION return value.

Example: Assigning a TITLE and DESCRIPTION to a Virtual Field

The following request against the EMPDATA data source creates two virtual fields:

The virtual field NAME combines employee first and last names into one field and assigns that field the title:
```
EMPLOYEE
FULL NAME
```
The virtual field MSALARY evaluates to missing if the jobclass is 019PVB or to the annual salary divided by 12. Its title is:
```
MONTHLY
SALARY
```
Its description is:
```
Monthly Salary or missing
```

The request follows.

DEFINE FILE EMPDATA                                                 
NAME/A50 TITLE 'EMPLOYEE,FULL NAME' = FIRSTNAME || (' ' | LASTNAME);
MSALARY/D12.2 TITLE 'MONTHLY,SALARY'                                
DESCRIPTION 'Monthly Salary or missing'                             
MISSING ON = IF JOBCLASS EQ '019PVB'                                
          THEN MISSING ELSE  SALARY / 12;                           
END                                                                 
TABLE FILE EMPDATA                                                  
SUM MSALARY                                                         
BY NAME BY JOBCLASS IN 20                                           
WHERE PIN FROM '000000100' TO '000000200'                           
END

The output is:

EMPLOYEE                            MONTHLY
FULL NAME          JOBCLASS         SALARY 
---------          --------         -------
ANTHONY RUSSO      38909           1,608.33
KAREN LASTRA       019PVB                 .
KARL WHITE         064PSA          3,408.33
KATE WANG          064PSB          4,125.00
LAURA GORDON       38913           2,116.67
MARCUS CVEK        019PTB          5,208.33
MARK MEDINA        257PRB          3,250.00
ROSE HIRSCHMAN     019PTB          5,208.33
TIM ANDERSON       38910           2,700.00
VERONICA WHITE     019PUA          5,208.33
WILLIAM MORAN      38914           2,566.67

If you add a HOLD command to the request and SET HOLDATTR=ON, the TITLE and DESCRIPTION attributes are propagated to the HOLD Master File.

SET HOLDATTR = ON                                                   
DEFINE FILE EMPDATA                                                 
NAME/A50 TITLE 'EMPLOYEE,FULL NAME' = FIRSTNAME || (' ' | LASTNAME);
MSALARY/D12.2 TITLE 'MONTHLY,SALARY'                                
DESCRIPTION 'Monthly Salary or missing'                             
MISSING ON = IF JOBCLASS EQ '019PVB'                                
          THEN MISSING ELSE  SALARY / 12;                           
END                                                                 
TABLE FILE EMPDATA                                                  
SUM MSALARY                                                         
BY NAME BY JOBCLASS IN 20                                           
WHERE PIN FROM '000000100' TO '000000200'                           
ON TABLE HOLD AS DEFINE1 FORMAT ALPHA                               
END

The DEFINE1 Master File has the TITLE and DESCRIPTION attributes assigned to the virtual fields.

FILENAME=DEFINE1 , SUFFIX=FIX     , IOTYPE=STREAM, $                    
  SEGMENT=DEFINE1, SEGTYPE=S2, $                                        
    FIELDNAME=NAME, ALIAS=E01, USAGE=A50, ACTUAL=A50,                   
      TITLE='EMPLOYEE,FULL NAME', $                                     
    FIELDNAME=JOBCLASS, ALIAS=E02, USAGE=A8, ACTUAL=A08, $              
    FIELDNAME=SALARY, ALIAS=E03, USAGE=D12.2, ACTUAL=A12,               
      MISSING=ON,                                                       
      TITLE='MONTHLY,SALARY', DESCRIPTION='Monthly Salary or missing', $

Information Builders