FILEREAD

The FILEREAD command reads records from an input file and processes data according to action statements that you specify. FILEREAD handles binary and packed decimal data, as well as text. It can handle decimal data written in E-notation (such as .1E+9) or M-notation (such as 10M). It can convert the data to any appropriate data type before storing it in an Oracle OLAP variable, dimension, composite, or relation.

Syntax

FILEREAD fileunit [STOPAFTER n] [file-format] {[attribute...] action-statement1}

     [[attribute...] action-statementN...]

where:

file-format specifies the format of the records in the input file as follows:

     RULED|CSV [DELIMITER dchar]|STRUCTURED [TEXTSTART schar] - 

           [TEXTEND echar] [DELIMITER dchar]

attribute provide information that is used by action statements. For example, attributes can be used to locate a field in the input record, format the data from that field, convert the data to a different data type, or specify how the data should be processed as a dimension value in Oracle OLAP. For information on the placement of attributes in action statements, see "Field Attributes". An attribute can be one or more of the following:

     COLUMN n | COL n

     SPACE n | SP n

     FIELD n | FLD n

     WIDTH n | W n

     INTEGER | SHORTINTEGER | DECIMAL | SHORTDECIMAL | NUMBER | TEXT | ID | DATE | VNF | RAW DATE | BOOLEAN

     MATCH | APPEND [LAST | FIRST | BEFORE pos | AFTER pos] | ASSIGN

     BINARY | PACKED | SYMBOLIC

     TRANSLATE | NOTRANSLATE |

     SCALE n

     ZPUNCH | ZPUNCHL

     LSET 'text'

     RSET 'text'

     NOSTRIP | STRIP | LSTRIP | RSTRIP

     NAVALUE val

     NASPELL 'text'

     ZSPELL 'text'

     YESSPELL 'text'

     NOSPELL 'text'

     ZEROFILL

action-statements perform processing, such as assignment statements and IF statements. For example, an action statement can compare dimension values with values retrieved from the input record, assign data to one or more cells in a dimensioned variable, or simply increment a counter. An action-statement can be one of the following:

     assignment-statement

     IF-statement

     SELECT-statement

     ACROSS-statement: action-statement

     <action-statement-group>

Arguments

fileunit

A fileunit number assigned to a file that is opened for reading (READ mode) by a previous call to the FILEOPEN function.

STOPAFTER n

The number of records to read from the input file. When STOPAFTER is left out, or specified with a negative number or an NA, FILEREAD processes the whole file. See "STOPAFTER Keyword".

RULED

Specifies that the record is organized in fixed-width columns, that is, character-by-character or byte-by-byte. All lines must have exactly the same format. RULED is the default file format. Use the COLUMN, SPACE, and WIDTH attributes to specify the location of the data in the records.

CSV [DELIMITER dchar]

CSV specifies that the data is in CSV (comma-delimited values) format. You must use the FIELD and SPACE attributes to specify the location of the data in the record.

dchar is a text expression that specifies a single character that you want Oracle OLAP to interpret as the general field delimiter in a structured file. Oracle OLAP uses the general field delimiter to identify both numeric and text fields. The default character is a comma (,).

CSV files are a common output format that is generated by spreadsheet programs. Each line of characters in a source file is treated as a single record. Each field in the record is separated by a comma by default. You can use the DELIMITER keyword to specify some other character as field delimiter.

When a group of characters in the input record is enclosed by double quotation marks, all of the following rules apply:

• When the group includes the delimiter character, it is treated as a literal instead of as a delimiter.

• When a double quotation mark (") is included in the group of characters, then it must be followed by another double quotation mark.

• When a linefeed character (\n) is included in the group of characters, then it is ignored.

• Any spaces or tabs that occur before or after the double quotation marks that enclose the group of characters will be ignored.

STRUCTURED

Specifies that the record is in "structured prn" format. You must use the FIELD and SPACE attributes to specify the location of the data in the record.

Structured files are a common output format for PC software. They are text files in which the fields are composed of groups of characters. A group of characters is defined by two conditions: text enclosed in double quotes, or a sequence of numbers that is uninterrupted except by a decimal point. This means that an unquoted sequence of numbers containing a decimal point will be stored as a single value; however, an unquoted sequence of numbers containing commas or other delimiters to mark off thousands will be split into several values rather than stored as a single value. Any unquoted, non-numeric characters are ignored, except a minus sign that immediately precedes a number is considered to be part of the number. A space cannot separate the minus sign from the number.

When your file format does not conform to the pattern described here, you can use the TEXTSTART, TEXTEND, and DELIMITER keywords that let you customize the delimiters FILEREAD uses to identify the start and end of each field.

TEXTSTART schar

Specifies a single character that you want Oracle OLAP to interpret as the start of a text field in a structured file. schar is the value of the character. The default character is a double quote (").

TEXTEND echar

Specifies a single character that you want Oracle OLAP to interpret as the end of a text field in a structured file. echarr is the value of the character. The default character is a double quote (").

DELIMITER dchar

Specifies a single character that you want Oracle OLAP to interpret as the general field delimiter in a structured file. Oracle OLAP uses the general field delimiter to identify both numeric and text fields. dchar is the value of the character. The default character is a comma (,).

COLUMN n

COL n

The column in which the field starts in the input record. By default, field 1 begins in column 1 and subsequent fields begin in the column following the previous field. The current field's default column is the sum of the previous field's first column plus its width plus any spaces specified for the current field.

SPACE n

SP n

The number of spaces between a field and the preceding field. In a structured PRN file, the number of fields between the preceding and current field. The default is 0.

FIELD n

FLD n

In a structured PRN file only, the field from which to extract the data.

WIDTH n

W n

The number of columns the field occupies in the input record. The default is derived from the data type according to the following list:

• BINARY input format with INTEGER, SHORTINTEGER, or SHORTDECIMAL target data type has a default of 4 columns.

• BINARY input format with DECIMAL or NUMBER target data type has a default of 8 columns.

• BINARY input format with BOOLEAN target data type has a default of 2 columns.

• PACKED input format with any type of target data type has no default.

• SYMBOLIC input format with ID target data type has a default of 8 columns.

• SYMBOLIC input format with a target data type that is not ID has no default.

When there is no default, WIDTH must be included for ruled records or FILEREAD generates an error. (Structured records do not require a WIDTH specification.)

The maximum width is 4000 characters for text input.

INTEGER

SHORTINTEGER

DECIMAL

SHORTDECIMAL

NUMBER

TEXT

ID

DATE

VNF

RAW DATE

BOOLEAN

For text data, the data type to which the input is converted before it is stored in your analytic workspace.

• Except for dimensions of type DAY, WEEK, MONTH, QUARTER, and YEAR, the default is the data type of the target object.

• For dimensions of type DAY, WEEK, MONTH, QUARTER, and YEAR, the default is VNF.

• For DATE variables and dimensions of type DAY, WEEK, MONTH, QUARTER, and YEAR, RAW DATE indicates the input values are positive integers that represent the number of days since December 31, 1899, or negative integers that represent the number of days before December 31, 1899.

• For binary data, the data type of the data in the input record.

See "NTEXT Values".

MATCH

APPEND

ASSIGN

When the target object is a dimension, these attributes specify whether or not to add new dimension values. For each record processed, the dimension is temporarily limited to the value in the record.

When the target object is a dimension and you do not specify a dimension attribute, then values in the input field must match current dimension values. When the value does not exist, FILEREAD generates an error. This attribute also applies when the target object is a a dimension surrogate.

The field contains new dimension values and may contain existing values as well. New values are added to the dimension list and the status is limited to the current value. The status is set to ALL after FILEREAD finishes. For time dimensions, Oracle OLAP automatically fills in any "missing" periods between the existing ones and the new ones. This attribute does not apply when the target object is a a dimension surrogate because you cannot directly add values to a surrogate.

This attribute applies only to a dimension surrogate. It assigns the new value to the surrogate.

LAST

When the target object is a non-time dimension and the dimension attribute is APPEND, adds the value to the end of the dimension list.

FIRST

When the target object is a non-time dimension and the dimension attribute is APPEND, adds the value to the beginning of the list.

BEFORE pos

When the target object is a non-time dimension and the dimension attribute is APPEND, adds the value before the specified value or integer position.

AFTER pos

When the target object is a non-time dimension and the dimension attribute is APPEND, adds the value after the specified value or integer position.

SYMBOLIC

Specifies that the format of the input field is ASCII or EBCDIC text.

BINARY

Specifies that the format of the input field is binary.

PACKED

Specifies that the format of the input field is packed decimal.

TRANSLATE

Specifies that Oracle OLAP translates the data from the format of the original operating system, as identified by a FILESET ORIGIN statement

NOTRANSLATE

Specifies that Oracle OLAP does not translate the data from the format of the original operating system, as identified by a FILESET ORIGIN statement.

SCALE n

The number of digits to the right of the assumed decimal or binary point. The default is 0. When the input data is text, a decimal point in the input overrides the number specified by SCALE.

ZPUNCH

Specifies that the input is zone overpunched.

ZPUNCHL

Specifies that the input is zone overpunched on the left.

 LSET 'text'

For text input and TEXT or ID target objects, adds text to the left of the value before storing. When text is multiline, only the first line is used. By default, no text is appended

 RSET 'text'

For text input and TEXT or ID target objects, adds text to the right of the value before storing. When text is multiline, only the first line is used. By default, no text is appended.

NOSTRIP

For text input specifies that no spaces (or nulls) are stripped from the input.

STRIP

For text input specifies that spaces (and nulls) are stripped from both left and right of the input.

LSTRIP

For text input specifies that spaces and nulls are stripped from the left of the input.

RSTRIP

For text input specifies that spaces and nulls are stripped from the right of the input.

NAVALUE val

For binary or packed input, specifies that when the input is the specified numeric value, NA is assigned to the target object.

NASPELL 'text'

For text input, specifies that Oracle OLAP stores text as NA. When the input is the specified text, NA is assigned to the target object. Text can be a multiline string listing several possible NA values. In addition to the values specified for text, when the input is NA, then NA is assigned to the target object.

ZSPELL 'text'

For textual numeric input, specifies that Oracle OLAP stores text as 0. When the input is the specified text, zero is assigned to the target object. Text can be a multiline string that lists several possible zero values. In addition to the values specified for text, when the input is 0, then 0 is assigned to the target object.

YESSPELL 'text'

For text input that is BOOLEAN, specifies that Oracle OLAP stores text as YES. When the input is text then YES is assigned to the target object. Text can be a multiline string that lists several possible YES values. In addition to the values specified in text, when the input is YES, ON, or TRUE, YES is assigned to the target object.

NOSPELL 'text'

For text input that is BOOLEAN, specifies that Oracle OLAP stores text as NO. When the input is text then NO is assigned to the target object. Text can be a multiline string that lists several possible NO values. In addition to the values specified in 'text,' when the input is NO, OFF, or FALSE, NO is assigned to the target object.

ZEROFILL

For text numeric input, specifies that Oracle OLAP fills any spaces in the resulting text with zeros. Any spaces in the input are replaced with zeros. The default is no filling with zeros.

action-statement

You may specify one or more action statements to be performed each time a record is retrieved from the input file. Typically, you will use action statements to set dimension status and assign data retrieved from the input record to a target object in Oracle OLAP. However, you may specify action statements that do not reference the data in the input record. For example, one of your action statements might be an assignment statement that simply increments a counter. Alternatively, an action statement might use the input data in some kind of processing, but not actually assign it to a target object in Oracle OLAP.

In your list of action statements, be sure to process dimensions before variables. FILEREAD processes each action statement from left to right for each input record. When an action statement performs dimension processing, the resulting status remains in effect for subsequent action statements. When you do not first specify action statements that limit a variable's dimensions, FILEREAD uses the first value in status to target a cell in the variable. Unless you specify an ACROSS phrase, FILEREAD assigns a single value from a field in an input record to a single cell in an Oracle OLAP variable. By default, FILEREAD does not loop over a variable's dimensions when assigning data to the variable. See "Field Order".

Use the VALUE keyword in FILEREAD action statements to represent the value in a particular field of the input record. VALUE represents this data, formatted according to the FILEREAD attributes you have specified. When the field in the record is blank, FILEREAD considers its value to be NA. By default, the data type of VALUE is the data type of the target object. However, you can specify a different data type with an attribute keyword.

Note: When you have already specified action statements for use with FILEREAD, you can reuse the code with SQL FETCH and SQL IMPORT by simply adjusting the assignment statements and eliminating the VALUE keyword (if necessary). Most of the FILEREAD attributes (with the exception of the attributes that control dimension processing) are not meaningful for SQL loading and are ignored when executing within SQL FETCH and SQL IMPORT.

assignment-statement

An assignment statement lets you assign a value to an Oracle OLAP object. An assignment statement has the following form.

     object [= expression]

object is the target where the data will be assigned and stored. The object can be an Oracle OLAP variable, dimension, dimension surrogate, composite, or relation.

expression is the source of the data value to be assigned to the target.

Important: In a SQL FETCH or a SQL IMPORT assignment statement, the expression component is not optional. However, a FILEREAD assignment statement may consist only of an object name. In this case, the input data is assigned directly to object. An expression in a FILEREAD assignment statement may include the VALUE keyword.

IF-statement

An IF statement lets you perform some action depending on whether a Boolean expression is TRUE or FALSE. An IF statement has the following form.

     IF bool-exp

       THEN action

      [ELSE action]

IF evaluates the Boolean expression. When it is TRUE, the THEN action occurs. When it is FALSE, the ELSE action (if specified) occurs. When the Boolean expression is NA, no action occurs.

An action can be one of the following:

• NULL (no action occurs)

• An assignment statement

• A SELECT statement

• An IF statement

• A DO … DOEND statement containing action-statements

A FILEREAD IF statement may contain invocations of the VALUE keyword. You can use a FILEREAD IF statement to process varying record types (such as records with different structures or different target objects) with one FILEREAD command.

In FILEREAD, the VALUE keyword can be used more than once to represent different values from the same record. For each instance, specify the column from which to read each value.

SELECT statement

A SELECT statement lets you perform some action based on the value of an expression. A SELECT statement has the following form.

     SELECT select-expression

        [WHEN expression1 action

        [WHEN expression2 action . . .]

     [ELSE action]

SELECT evaluates the SELECT expression and then sequentially compares the result with the WHEN expressions. When the first match is found, the associated action occurs. When no match is found, the ELSE action (if specified) occurs.

An action for a SELECT statement is the same as an action for an IF statement.

A FILEREAD SELECT statement may contain invocations of the VALUE keyword. You can use a FILEREAD SELECT statement to process varying record types (such as records with different structures or different target objects) with one FILEREAD command.

ACROSS-statement: action-statement

An ACROSS statement causes the following action statement to execute once for every value in status of the ACROSS dimension. When you want the looping to apply to more than one action statement, enclose the action statements in angle brackets. An ACROSS statement has the following form.

     ACROSS dimension [limit-clause]:

        action-statement

limit-clause temporarily changes the status of dimension, as long as you are not in a FOR loop over dimension. The new status is in effect only for the duration of the SQL FETCH command. The format of limit-clause is as follows.

     [ADD|COMPLEMENT|KEEP|REMOVE|TO] valuelist

To specify the temporary status, insert any of the LIMIT command keywords (the default is TO) along with an appropriate list of dimension values or related dimensions. You can use any valid LIMIT clause (see LIMIT command for further information). The following example limits month to the last six values, no matter what the current status of month is.

ACROSS month last 6: units

In a FILEREAD ACROSS statement, you can specify attributes to indicate the position in the record where Oracle OLAP will begin reading the fields specified by the ACROSS phrase. To specify the position, use the attributes FIELD, SPACE, and COLUMN. A position attribute is optional when the series of fields specified in the ACROSS phrase begins in the next field for structured records, or the next byte for ruled records.

<action-statement-group>

You can group several action statements together by enclosing them in angle brackets. An action-statement-group has the following form.

     <action-statement1 -

     [action-statement2 . . .]>

A typical use for action statement groups is after an ACROSS statement. With the angle bracket syntax, you can cause more than one action statement to execute for every value in status of the ACROSS dimension.

Notes

As an alternative to FILEREAD, you can use the FILENEXT function to read one record at a time with one or more FILEVIEW commands to process the fields in the record.

Before you can process data from a file with FILEREAD, use the FILEOPEN function to open the file for reading (READ mode). When you are finished, close the file with the FILECLOSE command.

When an input record contains both dimension values and variable data, the dimension values must be the first fields that are read in the record, and the variable data values must be read after those dimension values. To do this, you can either order the fields in the input record itself or you can use FILEREAD attributes to specify the field positions explicitly. (See the description for the attribute argument.)

To organize the input records so that you do not need to use position attributes with FILEREAD, put all of the dimension values in the first fields of the record and put the variable data values in the last fields of the record. For example, suppose that you have data for two variables (units and sales) that share the same dimensions in the same order (time, product, and geography). In this case, the first three fields in the input record should contain dimension values, while the fourth and fifth fields should contain variable data, such as in the following sample input record.

Sep99    Snowshoes    Boston    35    5565.95

By default, FILEREAD automatically reads all the records in a file in sequential order. When you want to process only the first part of a file, use the STOPAFTER keyword. FILEREAD processes the number of records you specify, then stops. You can then close the file.

When you want to skip the first part of the file and process the remaining records, you can use the STOPAFTER keyword and omit the field descriptions. FILEREAD will read the number of records you specify without processing the data. Then you issue a second FILEREAD command with field descriptions for processing the input. The following program lines illustrate this method.

lIMIT district TO 'Boston'
unit = FILEOPEN('bostdata' READ)
FILEREAD unit STOPAFTER 25
FILEREAD unit WIDTH 8 product SPACE 2 ACROSS month 13 TO 24:-
   WIDTH 4 PACKED sales

When the target object of a field description is a dimension, you can specify whether or not to use the data in the file to add values to the dimension. The dimension attributes are MATCH and APPEND. When you are adding values to a dimension with APPEND, you can specify a dimension position attribute (LAST, FIRST, BEFORE pos, AFTER pos) immediately after APPEND.

In an assignment statement of the form object=expression, dimension attributes cannot appear on the right side of the equal sign, but must be specified before the target object. The only exception is when dimensions as target objects also appear on the right side, such as when you are maintaining a conjoint dimension. See Example 13-12, "Maintaining Conjoint Dimensions with File Data".

When your input data consists of dimension position numbers, rather than dimension values, specify the conversion type as INTEGER in the field description, even though the dimension has a type of TEXT, ID, DAY, WEEK, MONTH, QUARTER, or YEAR.

FILEREAD unit COLUMN 1 WIDTH 8 INTEGER month

When the input contains position numbers, you cannot use the APPEND keyword to add new values to a dimension of type TEXT, ID, DAY, WEEK, MONTH, QUARTER, or YEAR, because the new position numbers have no associated value to be added.

When a conjoint dimension is the target object, you can read its values using one of the two methods:

• Method One—When the input contains values or position numbers of the base dimensions, you must specify a dimension list surrounded by angle brackets after the equal sign, as shown in the following two sample lines.

  FILEREAD unit proddist = <COL 1 W 10 product COL 20 -
     W 8 district>
  FILEREAD unit proddist = <COL 1 W 10 INTEGER product COL 20 -
     W 8 INTEGER district>
  
  

  The preceding examples show values of the product and district dimensions being used to designate a value of the proddist concat dimension You could also use the APPEND attribute when you needed to maintain any of the dimensions. However, when you needed to process the values of product or district first, so that the syntax would require an equal sign inside the angle brackets, you would have to use an alternative method. (Nested equal signs are not allowed.) For this method you would read and process the base dimension values first, and then use the dimensions, without any field attributes, in the dimension list for the conjoint dimension. For example, to convert the base dimension values of a conjoint dimension to uppercase, use a statement similar to the following.

  FILEREAD unit COL 14 W 8 product = UPCASE(VALUE) -
     COL 5 W 8 district = UPCASE(VALUE) -
     proddist = <product, district>
  
  

• Method Two—When the input contains position numbers of the conjoint dimension itself, you must specify the INTEGER keyword.

  FILEREAD unit INTEGER proddist
  

When reading data into a variable dimensioned by a composite, FILEREAD automatically creates any missing target cells that are being assigned non-NA values. This process also adds to the composite all the dimension value combinations that correspond to those new cells. Thus, both the target object and the composite might be larger after an assignment.

When you use the automatic composite maintenance feature of FILEREAD to load data into variables dimensioned by composites, you should be aware of potential performance problems that might later occur when you attempt to access the variables' data. The position of a composite in the dimension list of a variable indicates whether or not performance might later become an issue.

When the composite appears at the end of the dimension list in the variable's definition (the slowest-varying position), you can use FILEREAD just as you would for a variable whose dimension list does not include composites. For example, you could use the same FILEREAD commands to read data into the variables newsales and newsales.cp (with the following definitions) without sacrificing efficiency.

DEFINE newsales VARIABLE DECIMAL <product district month>
DEFINE newsales.cp VARIABLE DECIMAL <product SPARSE<district month>> 

newsales.cp is dimensioned by three dimensions, the last two of which are in a composite. When, however, you have a variable like newsales2.cp (with the following definition) there can be performance implications for accessing data loaded with FILEREAD.

DEFINE newsales.cp VARIABLE DECIMAL <SPARSE<district month> product > 

In this case, you can use one of two methods to avoid performance problems. Refer to "Prevent Performance Problems: Method One" and "Prevent Performance Problems: Method Two".

You can use CHGDFN with the SEGWIDTH keyword to change the segment size for the variable before using FILEREAD. CHGDFN SEGWIDTH lets you specify the size of a variable's segments. A segment is a portion of the total number of values a variable holds. The number of segments in a variable affects the performance of data loading and data accessing.

The segment size that you specify with a CHGDFN SEGWIDTH statement is used not only for the variable you designate as varname, but also for all other variables and relations that are defined with the same combination of dimensions and composites in the same order.

Alternatively, you can explicitly add composite values just as you would for a conjoint dimension. You can use this method both for named and unnamed composites. See "Composite Maintenance".

When you wish to explicitly maintain composites with FILEREAD, use the same syntax that you use to maintain conjoint dimensions. When the composite is unnamed, refer to it with the form SPARSE<dim1 dim2 ...>. See "FILEREAD with Variables Dimensioned by Composites" and "Variables Dimensioned by Composites and Efficiency" to evaluate the advantages of explicit versus automatic composite maintenance with FILEREAD.

When the target object of a field is a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, the default conversion type is VNF. Therefore, you do not need to specify a conversion type when the input values are formatted according to the VNF of the target dimension (or the default VNF when the dimension does not have a VNF of its own).

When the target object of a field is a DATE variable or a dimension of type DAY, WEEK, MONTH, QUARTER, and YEAR, FILEREAD will interpret the values correctly when they are in a valid input style for dates as described in DATEORDER. For dimensions of type DAY, WEEK, MONTH, QUARTER, and YEAR, you must specify DATE as the conversion type. For values of a DATE variable, DATE is the default conversion type, so the DATE keyword is optional.

FILEREAD will also interpret values of a time dimension or a DATE variable correctly when they are integers that represent dates (1 = January 1, 1900). In this case, you must specify RAW DATE as the conversion type.

When a field is blank, its value is NA and NA is assigned to the target variable. Examples of blank fields are a text field filled with spaces, a field that begins beyond the end of the record, or a field in a structured file that has nothing, not even a space, between the field delimiters.

Normally, the field attributes immediately precede the target object or the expression on the right of the equal sign.

     attributes object

However, when you want an attribute to apply to several fields, specify the attribute followed by the list of target objects surrounded by angle brackets. You can also include attributes that apply to one of the objects by typing them inside the brackets before the object to which they apply.

     attributes0 <attributes1 object1=expression object2 attributes3 object3>

Angle brackets are also used to surround the base values of a conjoint dimension value.

When FILEREAD encounters an error, you can control what happens with an error trap and appropriate processing. Errors can be caused by attempts to convert data to an incompatible data type or by encountering invalid dimension values. You can use the FILEERROR function to get more information about what caused the error. After processing the error, you can use the TRAP command to turn error trapping back on and GOTO to branch back to the FILEREAD command. Processing continues with the next record. See Example 13-10, "Error Handling".

When you specify a target object of type NTEXT for data from a structured or CSV file, FILEREAD translates the data from the file into the database character set before storing the values (even though they are assigned to an NTEXT object). This can result in data loss when the data from the file cannot be represented in the database character set. For data from a ruled file, which has fixed-width columns, FILEREAD does not translate into the database characters set, so there is no data loss.

Examples

Jan951234aa00Chocolate Chip Cookies        123
Jan951099bb00Oatmeal Cookies               145
Jan952355cc00Sugar Cookies                 223
Jan955553ee00Ginger Snap Cookies           233
Feb951234aa00Chocolate Chip Cookies        123
Feb951099bb00Oatmeal Cookies               O145
Feb952355cc00Sugar Cookies                 SS223
Feb955553ee00Ginger Snap Cookies           G233
Mar952355cc00Sugar oCookies                 223
Mar955553ee00Ginger Snap Cookies           233
Mar953222dd00Brownies                      432

DEFINE month DIMENSION MONTH
DEFINE productid DIMENSION ID
DEFINE productname VARIABLE TEXT <productid> 
DEFINE units.sold VARIABLE INTEGER <month productid> 

DEFINE read.product PROGRAM
PROGRAM
VARIABLE fi INT
fi = FILEOPEN('Dr.Dat' READ)
FILEREAD fi COLUMN 1 APPEND WIDTH 5 month -
   COLUMN 6 APPEND WIDTH 6 productid
FILECLOSE fi
 
fi = FILEOPEN('Dr.Dat' READ)
FILEREAD fi COLUMN 1 WIDTH 5 month -
   COLUMN 6 WIDTH 6 productid -
   COLUMN 12 WIDTH 30 productname -
   COLUMN 44 WIDTH 22 units.sold
FILECLOSE fi
END

Kiyaks400

DEFINE funit INT
funit = FILEOPEN('Ds.Dat' READ)
FILEREAD funit COL 1 APPEND W 6 product COL 7 ASSIGN W 3 prodnum
FILECLOSE funit

Jan02400Boston416

funit = FILEOPEN('Var.Dat' READ)
FILEREAD funit COL 1 W 5 month COL 6 MATCH W 3 prodnum -
  COL 9 W 6 district COL 15 W 3 INTEGER units
FILECLOSE funit

DEFINE read.price PROGRAM
PROGRAM
VARIABLE fil.unit INTEGER
TRAP ON ERROR
fil.unit = FILEOPEN( ARG(1) READ)
LIMIT month TO &ARG(2)
NEXT:
FILEREAD fil.unit - 
  WIDTH 8 product -
  WIDTH 4 BINARY price
FILECLOSE fil.unit
RETURN
error:
IF fil.unit EQ NA
  THEN RETURN
IF ERRORNAME NE 'attn' AND ERRORNAME NE 'quit'
  THEN DO
    SHOW JOINCHARS('Record ' RECNO(fil.unit) ' is Invalid.')
    TRAP ON ERROR
    GOTO NEXT
  DOEND
FILECLOSE fil.unit
END

FILEREAD unit COLUMN 1 WIDTH 6 APPEND LSET 'p' product

FILEREAD unit APPEND proddist = <W 8 product, W 8 district>

fileread unit APPEND proddist = <W 8 MATCH product,W 8 MATCH district>

FILEREAD unit W 8 product W 8 district APPEND proddist = <product, district>

FILEREAD unit STOPAFTER 10 STRUCTURED FIELD 2 product -
   district FIELD 6 ACROSS month: sales