|Oracle9i OLAP Services Developer's Guide to the OLAP DML
Release 1 (9.0.1)
Part Number A86720-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 FILEREAD (or FILEVIEW) command, or you can limit it temporarily in the ACROSS phrase.
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('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
The ACROSS phrase reads each of these fields into separate cells in the UNITS variable.
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 OLAP DML 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('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 INTEGER dimension 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 MONTH dimension.
Finally, 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.
For more information, see the following table.
|IF you want documentation about . . .||THEN see . . .|
the CHARLIST function,
the topic for the function in the OLAP DML Reference