FILEVIEW

The FILEVIEW command works in conjunction with the FILENEXT function to read one record at a time of an input file, process the data, and store the data in Oracle OLAP dimensions and variables according to the descriptions of the fields. Use FILENEXT to read the record, then use one or more FILEVIEW statements to process the fields as needed. FILEVIEW has the same attributes as FILEREAD for specifying the format of the input and the processing of the output.

Syntax

FILEVIEW fileunit [field-desc...]

Arguments

fileunit

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

field-desc

A field description describes how to process one or more fields in each input record. Attributes in the field description specify how to format the input data. FILEVIEW reads each field according to the format specification and assigns the input data to the specified object. You can assign the data to the object directly or you can specify an expression to manipulate the data before you assign it. One field description can assign data from one input field to one Oracle OLAP object. Alternately you can use the ACROSS keyword to assign several values in the input record to a variable that is dimensioned by the fastest varying dimension. Because field attributes include the column number in the input record, you can process input fields in any order.

The format for the field description is as follows.

[[pos] ACROSS dim [limit-clause]:] [attribs] object [= exp]

pos

One or more attributes that specify the position in the record where Oracle OLAP will begin reading the fields specified by the ACROSS description. To specify the position, use the attributes FIELD, SPACE, and COLUMN (see the FILEREAD command). The pos argument 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.

ACROSS-statement: action-statement

Specifies the dimension of one or more data fields in the input record. FILEVIEW assigns the data in the fields to a variable according to the values in the current status of dim. Typically, each field description processes one value. However, using the ACROSS keyword, you can process one input value for each dimension value currently in the status. 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 syntax.

ACROSS dimension [limit-clause]:

action-statement

The syntax of limit-clause is the same syntax as any of the limit-clause arguments in the various forms of the LIMIT command (that is, the syntax of the LIMIT command after the limit-type argument such as "TO"). For the syntax of these arguments, see LIMIT (using values) command, LIMIT using LEVELREL command, LIMIT command (using parent relation), LIMIT (using related dimension) command, LIMIT NOCONVERT command, and LIMIT command (using POSLIST).

The following example limits month to the last six values, no matter what the current status of month is.

ACROSS month last 6: units

attribs

One or more attributes that tell Oracle OLAP the position in the record and the format of the input data. (See the FILEREAD command for an explanation of the available attributes.)

object [= exp]

An Oracle OLAP variable, dimension, or relation to which the input data is assigned. When = exp is missing, the data is assigned implicitly to the object. When = exp is present, the data is processed according to the expression and then assigned to object.

You can use the keyword VALUE to represent the value in a particular field of a record. VALUE represents the data from the file, formatted according to the FILEREAD attributes you use. 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. VALUE 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, as shown in the following example code.

sales = if col 1 w 1 text value eq 'A' then col 2 w 8 value -
   else col 10 w 8 value

In this example, the default data type of VALUE is decimal, which is the data type of the target object sales. However, the first instance of VALUE is compared to a text expression, so you must use the attribute TEXT to specify its data type.

SELECT exp

The SELECT field-description keyword processes varying record types (such as records with different structures or different target objects) with one FILEVIEW statement. Within a field description, you can use the following syntax:

SELECT exp -

[WHEN exp action [WHEN exp action ...]] -

[ELSE action]

IF bool-exp THEN action [ELSE action]

field-desc

[field-desc]

...

DOEND

The action argument is one of the following:

NULL (no action occurs)
field-description, including nested IF and SELECT statements.

SELECT evaluates the first expression, which may contain invocations of the VALUE keyword, and which has a default data type of TEXT. SELECT 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.

IF bool-exp

The IF field-description keyword processes varying record types (such as records with different structures or different target objects) with one FILEVIEW statement. Within a field description, you can use the following syntax.

IF bool-exp THEN action [ELSE action]

action is the same as described for SELECT.

IF evaluates the Boolean expression, which may contain invocations of the VALUE keyword. IF performs the THEN action when the expression is TRUE or the ELSE action, if specified, when the expression is FALSE. No action occurs when the expression is NA.

Notes

Record Order

FILEVIEW can process the fields in a record in any order. List the field descriptions in the order you want to process them, identifying the fields with explicit column numbers. You can also use several FILEVIEW statements on the same record to do different processing depending on the data you find in the record.

Alternative OLAP DML Statement

When you want to process all the records in a file in the same way, without complicated optional processing, a FILEREAD statement is easier to use.

Dimension Values

When the target object of a field description is a dimension, you can specify whether the data in the file will be used to add values to the dimension or not. The dimension attributes are MATCH and APPEND:

MATCH -- Any value encountered in a field must already be a value of the dimension. FILEVIEW temporarily limits status to that value. When it is not already a dimension value, FILEVIEW generates an error. After executing a FILEVIEW statement, the dimension status is the same as before the execution of the statement.
APPEND -- The values in the field can already exist or they can be new. When the value exists, FILEVIEW limits status to that value; when it does not, FILEVIEW adds the value and then limits status. The dimension is limited to ALL when FILEVIEW is finished.

For more information about handling dimensions, see the FILEREAD command.

Handling Errors When FILEVIEW Encounters an Error

When FILEVIEW 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 find out what type of error occurred. After processing the error, you can use GOTO to branch back to the FILEVIEW statement.

Attribute List

For a complete list of the attributes for FILEVIEW and FILEREAD and for more information about processing NA values, reading date values, reading multidimensional data, storing NTEXT values, and specifying attributes, see the FILEREAD command.

FILEVIEW with Composites

The discussions of composites and variables dimensioned by composites in FILEREAD also apply to FILEVIEW.

Examples

Example 9-132 Varying Months

The following program processes an input file that contains sales data for a variable number of months. The file has the following records:

Record 1 -- Title (to be ignored).
Record 2 -- Column labels. Month names are used to set the status of month. The number of months is unknown before processing the file.
Record 3 -- Dashes underlining column labels (to be ignored).
Record 4 -- Blank.
Record 5 to end -- There are three record types for Record 5—one for each type of line to be read.

One record type for Record 5 represents a detail line with the contents shown in the following table.

Column	Width	Format	Data
1	8	Symbolic	District name or blank (When the district name is blank on a detail line, the most recent line containing a district determines the current district.)
10	10	Symbolic	Product name
21	10	Symbolic	Sales for first month
33	10	Symbolic	Sales for second month
45	To end of record	Symbolic	Sales for additional months

Another record type in Record 5 represents a totals line with the contents shown in the following table.

Column	Width	Data
1	18	Blank
21	To end of record	Totals

A third record type of Record 5 contains dashes or equal signs as row separators as illustrated in the following table.

Column	Width	Data
1	18	Blank
21	To end of record	Dashes (--) or equal signs (==)

This is a report of the sample file.

This is the Title
                    Jan95      Feb95      Mar95      Apr95
                    ---------- ---------- ---------- ----------
 
Boston   Tents       32,153.52  32,536.30  43,062.75  57,608.39
         Canoes      66,013.92  76,083.84  91,748.16 125,594.28
         Racquets    52,420.86  56,837.88  58,838.04  69,338.88
         Sportswear  53,194.70  58,913.40  62,797.80  67,869.10
         Footwear    91,406.82  86,827.32 100,199.46 107,526.66
                    ---------- ---------- ---------- ----------
                    295,189.82 311,198.74 356,646.21 427,937.31
                    ---------- ---------- ---------- ----------
Atlanta  Tents       40,674.20  44,236.55  51,227.06  78,469.37
           .
           .
           .
         Footwear    53,284.54  57,331.30  59,144.76  70,516.98
                    ---------- ---------- ---------- ----------
                    231,780.46 245,812.33 275,622.68 355,784.92
                    ---------- ---------- ---------- ----------
                     1,813,326  1,985,731  2,185,174  2,638,409
                    ========== ========== ========== ==========

The program figures out which months are covered in the file, then reads the detail lines and assigns the sales data to the appropriate district and month. The program ignores total lines and underlines when FILEVIEW finds columns 1 through 19 blank. The program takes the name of the data file as an argument.

DEFINE salesdata PROGRAM
LD Store Several Months of Sales Data in an Analytic Workspace
PROGRAM
VARIABLE fil.unit INTEGER
VARIABLE flag BOOLEAN
VARIABLE mname TEXT
VARIABLE label TEXT
VARIABLE savedist TEXT
 
TRAP ON error NOPRINT
PUSH month district
fil.unit = FILEOPEN(ARG(1) READ)
 
IF FILENEXT(fil.unit) NE YES    "Skip Record 1
  THEN SIGNAL noread
IF FILENEXT(fil.unit) NE YES    "Process Record 2 
  THEN SIGNAL noread
FILEVIEW fil.unit COLUMN 21 ACROSS month: -
  WIDTH 10 mname = JOINLINES( mname VALUE)
LIMIT month TO mname
IF FILENEXT(fil.unit) NE YES     "Skip Record 3
  THEN SIGNAL noread
IF FILENEXT(fil.unit) NE YES     "Skip Record 4
  THEN SIGNAL noread
 
WHILE FILENEXT(fil.unit)  "Process Record 5 To End Of File
   DO
   "Store Value In Local Label Variable
   FILEVIEW fil.unit COLUMN 1 WIDTH 18 label  
   IF label NE NA         "Check For NA (Blank Field)
     THEN DO              "Get District Value If Present
      IF EXTCHARS(label, 1, 8) NE '      ' 
         "Set District Status
         THEN savedist = BLANKSTRIP(EXTCHARS(label, 1, 8)) 
       FILEVIEW fil.unit -
         COLUMN 1 WIDTH 8 district = IF VALUE NE NA THEN -
            VALUE ELSE savedist -
         COLUMN 10 WIDTH 10 product -
         COLUMN 19 ACROSS month: WIDTH 10 SPACE 2 -
            SCALE 2 newsales
      DOEND
NEXT:
  DOEND
 
FILECLOSE fil.unit
POP month district
RETURN
error:
IF fil.unit EQ NA
  THEN SHOW JOINCHARS('Can\'t Open Data File ' ARG(1) '.')
ELSE IF ERRORNAME NE 'attn' AND ERRORNAME NE 'QUIT'
  THEN DO 
    SHOW JOINCHARS('RECORD ' RECNO(fil.unit) ' is invalid.')
    GOTO NEXT
  DOEND
ELSE IF ERRORNAME EQ 'noread'
  THEN DO
    SHOW 'File Too Short.'
    FILECLOSE fil.unit
  DOEND
ELSE DO
  SHOW 'Data Import Interrupted.' 
  FILECLOSE fil.unit
DOEND
POP month district
RETURN

Example 9-133 Additional Processing

When you want to save the dimension value that FILEVIEW read for display or further processing, you can read the field again and save the value in a variable. These lines in a program display the name of the month that FILEVIEW read. The FILEVIEW command saves the month value in column 1 in a variable called mname.

WHILE FILENEXT(fil.unit)
DO
  FILEVIEW fil.unit WIDTH 8 month WIDTH 5 INTEGER units -
       COLUMN 1 WIDTH 8 mname
  SHOW mname PROMPT
DOEND

Example 9-134 Using the VALUE Keyword as a Function

Suppose you want to read and report data from a disk file similar to the following, named numbers.dat, which has columns 15 characters wide.

1.0            2.0            3.0            4.0            5.0 
          -1.0           -2.0           -3.0           -4.0           -5.0
           0.0            0.0            1.43900000E+03                1.39900000E+03

You can read this data using the VALUE keyword as a function with FILEVIEW in a program similar to the following one (named try). However, this first example does not work. The FILEVIEW command will skip fields. The reason for the data skipping is that each time FILEREAD fetches a field from the current record, it updates the column pointer to point past the field. When the next fetch does not specify a position (using the COLUMN, SPACE, or FIELD attribute), data will be read from the default position established by the previous fetch. This usually desirable behavior will not work when more than one fetch is needed to perform a single assignment. This happens when the VALUE function is coded twice in the same IF...THEN...ELSE command block, as shown here. The NAMELIST and DIRLIST attributes return one value for multiple versions of a particular file name in the directory. The NAMELIST attribute also returns only one value for multiple files in the directory with the same root file name but different file types.

DEFINE try PROGRAM
PROGRAM
VARIABLE funit INTEGER
DEFINE dvar VARIABLE DECIMAL <year>
PUSH year
LIMIT year TO LAST 5
TRAP ON ERROR
funit=FILEOPEN('numbers.dat' R)
 
WHILE FILENEXT(funit)
   DO
   FILEVIEW funit ACROSS year: W 15 TEXT dvar = -
      IF FINDCHARS(VALUE, 'e') EQ 0 -  "Incorrect Use of Value
      THEN CONVERT(VALUE, dec) -       "Results in Skipped
      ELSE -9999.99                    "Fields
      REPORT DOWN year dvar
   DOEND
error:
FILECLOSE funit
DELETE dvar
POP year
END

When you execute the try program,

try

the output skips numbers, as in the following.

YEAR              DVAR
-------------  ----------
Yr93                 2.00
Yr94                 4.00
Yr95                   NA
Yr96            -9,999.99
Yr97            -9,999.99
   
YEAR              DVAR
-------------  ----------
Yr93                -2.00
Yr94                -4.00
Yr95                   NA
Yr96            -9,999.99
Yr97            -9,999.99
   
YEAR              DVAR
-------------  ----------
Yr93                 0.00
Yr94            -9,999.99
Yr95            -9,999.99
Yr96            -9,999.99
Yr97            -9,999.99

However, when the SPACE attribute is used to make the second VALUE back up some distance so it reads the same field that the first VALUE read, everything works fine. SPACE can be used in the preceding sample program by changing the THEN clause to the following clause.

THEN CONVERT(SPACE -15 VALUE, dec) -

Now when you execute the program,

try

the output will look like this.

YEAR              DVAR
-------------  ----------
Yr93                 1.00
Yr94                 2.00
Yr95                 3.00
Yr96                 4.00
Yr97                 5.00
   
YEAR              DVAR
-------------  ----------
Yr93                -1.00
Yr94                -2.00
Yr95                -3.00
Yr96                -4.00
Yr97                -5.00
   
YEAR              DVAR
-------------  ----------
Yr93                 0.00
Yr94                 0.00
Yr95            -9,999.99
Yr96            -9,999.99
Yr97            -9,999.99