|Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)
Part Number A95298-01
Reading Data from Files, 9 of 9
Sometimes several contiguous fields in a file contain data values that you want to assign to the same variable. Each field corresponds to a different value of one of the dimensions of the target variable.
For repeating fields, you can use an
ACROSS phrase in the field description to read the successive fields and place the values in the appropriate cells of the target variable. The
ACROSS phrase extracts data for each dimension value in the current status or until it reaches the end of the record. You can limit the
ACROSS dimension before the
FILEVIEW) command, or you can limit it temporarily in the
When the data file contains the information you need to limit the
ACROSS dimension, you can extract the dimension values using a temporary variable, limit the dimension, and then read the rest of the file.
Successive fields might hold sales data for successive months, as shown in the layout of
unitsale.dat in the following figure.
unitsale.dat file, columns 9 through 80 contain twelve 6-character fields. Each field contains sales data for one month of 1996.
The full data-reading program, with commands to open and close the file, is shown next.
DEFINE dr.prog5 PROGRAM LD Read a data file VARIABLE funit INTEGER TRAP ON error funit = FILEOPEN('olapfiles/unitsale.dat' READ) FILEREAD funit - COLUMN 1 WIDTH 8 product - ACROSS month jan96 TO dec96: WIDTH 6 units FILECLOSE funit RETURN error: IF funit NE na THEN FILECLOSE funit END
ACROSS phrase reads each of these fields into separate cells in the
FILEREAD command reads the sample
This command first reads the field beginning in column 1 and limits the
product dimension to the value read. (When the value read is not a dimension value of
product, an error occurs.) The command then reads the next 12 fields and assigns the values read to the
units variable for each month of 1996.
As shown in following example, the first record of the data file contains values of
month as labels for each column of data.
JAN96 FEB96 MAR96 APR96 TENT 50,808.96 34,641.59 45,742.21 61,436.19 CANOES 70,489.44 82,237.68 97,622.28 134,265.60 RACQUETS 56,337.84 60,421.50 62,921.70 74,005.92 SPORTSWEAR 57,079.10 63,121.50 67,005.90 72,077.20 FOOTWEAR 95,986.32 101,115.36 103,679.88 115,220.22
The following workspace objects are used by the example program.
DEFINE enum DIMENSION INTEGER DEFINE monthname VARIABLE ID <enum> TEMPORARY DEFINE salesdata VARIABLE DECIMAL <month product>
The example program, named
dr.prog6, has the following definition.
DEFINE dr.prog6 PROGRAM PROGRAM VARIABLE funit INTEGER TRAP ON cleanup PUSHLEVEL 'save' PUSH month product funit = FILEOPEN('olapfiles/dr6.dat' READ) IF FILENEXT(funit) THEN FILEVIEW funit COLUMN 16 ACROSS enum: - W 11 monthname LIMIT month TO CHARLIST(monthname) FILEREAD funit W 15 product COLUMN 16 ACROSS month: - W 11 salesdata cleanup: FILECLOSE funit POPLEVEL 'save' END
The program does not know how many months the file contains. The program uses a temporary variable dimensioned by an
INTEGER dimension to read the month names from the file. The
enum must have at least as many values as the largest data file has months.
FILENEXT reads only the first record. The
CHARLIST function creates a list of the month names, which is used to limit the
FILEREAD command processes the rest of the record using
month as the
ACROSS dimension. All the sales data is assigned to the correct months without the user having to specify them.