|Oracle® OLAP DML Reference
11g Release 1 (11.1)
|PDF · Mobi · ePub|
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.
FILEVIEW fileunit [field-desc...]
A fileunit number that is assigned to a file opened for reading (READ mode) in a previous call to the FILEOPEN function.
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.
[[pos] ACROSS dim [limit-clause]:] [attribs] object [= exp]
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.
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]:
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
ACROSS month last 6: units
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.)
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.
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 ...]] -
IF bool-exp THEN action [ELSE action]
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.
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
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.
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.
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.
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.
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.
The discussions of composites and variables dimensioned by composites in FILEREAD also apply to FILEVIEW.
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.
|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.)|
|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.
|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.
|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
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
WHILE FILENEXT(fil.unit) DO FILEVIEW fil.unit WIDTH 8 month WIDTH 5 INTEGER units - COLUMN 1 WIDTH 8 mname SHOW mname PROMPT DOEND
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
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,
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