| Oracle® OLAP DML Reference 11g Release 2 (11.2) Part Number E17122-07 |
|
|
PDF · Mobi · ePub |
| Oracle® OLAP DML Reference 11g Release 2 (11.2) Part Number E17122-07 |
|
|
PDF · Mobi · ePub |
The FILEREAD command reads records from an input file and processes data according to action statements that you specify. FILEREAD handles binary data, packed decimal data, and 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.
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. Use one of the following:
attribute provide information that is used by action statements.
For information on the placement of attributes in action statements, see "Placement of Field Attributes in FILEREAD".
action-statements perform processing, such as assignment statements and IF statements. An action-statement can be one of the following:
A fileunit number assigned to a file that is opened for reading (READ mode) by a previous call to the FILEOPEN function.
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".
Specifies that the record is organized in fixed-width columns, that is, character-by-character or byte-by-byte. All lines must have 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 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 is ignored.
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. Consequently, an unquoted sequence of numbers containing a decimal point is stored as a single value; however, an unquoted sequence of numbers containing commas or other delimiters to mark off thousands is 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.
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 (").
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 (").
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 (,).
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.
| Syntax | Description |
|---|---|
|
{COLUMN|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|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 |
|
{FIELD|FLD} n |
In a structured PRN file only, the field from which to extract the data. |
|
{WIDTH|W} n |
For unstructured records, the number of columns the field occupies in the input record. When there is no default, WIDTH must be included for ruled records or FILEREAD generates an error. The default is derived from the data type according to the following list:
The maximum width is 4,000 characters for text input. |
|
data-type |
One of the following keywords: INTEGER, SHORTINTEGER, DECIMAL, SHORTDECIMAL, NUMBER, TEXT, ID, DATE, VNF, RAW DATE, BOOLEAN.
|
|
dimension-value-handling |
When the target object is a dimension or dimension surrogate, one of the following keyword clauses that specifies whether or not to add new values to the target object:
|
|
input-field-format |
One of the following keywords that specifies the format of the input field:
|
|
TRANSLATE|NOTRANSLATE |
Whether or not Oracle OLAP translates the data from the format of the original operating system, as identified by a FILESET ORIGIN statement. Specify TRANSLATE when you want Oracle OLAP to translate the data; or specify NOTRANSLATE when you do not want Oracle OLAP to translate the data. |
|
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|ZPUNCHL |
Provides information about how the input zone is overpunched. Specify ZPUNCH when the input is zone overpunched. Specify ZPUNCHL when 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 a multiline value, only the first line is used. |
|
RSET 'text' |
For text input and TEXT or ID target objects, adds text to the right of the value before storing. When text is a multiline value, only the first line is used. |
|
stripping |
For text input, one of the following keywords that indicates if spaces or nulls are stripped from input value before storing in the target object:
|
For binary or packed input, specifies that when the input is the specified numeric value, NA is assigned to the target object.
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.
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.
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.
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.
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.
You may specify one or more action statements to be performed each time a record is retrieved from the input file. Typically, you 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 (except for the attributes that control dimension processing) are not meaningful for SQL loading and are ignored when executing within SQL FETCH and SQL IMPORT.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 is 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.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 statement.
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.
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 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 multiple action statements, 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 (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
In a FILEREAD ACROSS statement, you can specify attributes to indicate the position in the record where Oracle OLAP begins 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.
You can group several action statements 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 multiple action statements to execute for every value in status of the ACROSS dimension.
As an alternative to FILEREAD, you can use the FILENEXT function to read one record at a time with one or more FILEVIEW statements to process the fields in the record.
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 have 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 reads the number of records you specify without processing the data. Then you issue a second FILEREAD statement 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 9-130, "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.
Conjoint Dimension Maintenance
When a conjoint dimension is the target object, you can read its values using one of 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
FILEREAD with Variables Dimensioned by Composites
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.
Variables Dimensioned by Composites and Efficiency
When you use the automatic composite maintenance feature of FILEREAD to load data into variables dimensioned by composites, 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 statements 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:
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.
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 want 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.
Using DWMQY Dimensions 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 have 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 interprets 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 also interprets values of a time dimension or a DATE variable correctly when they are INTEGER values 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.
Placement of Field Attributes in FILEREAD
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 object 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.
Handling Errors When FILEREAD Encounters an Error
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 a TRAP statement to turn error trapping back on and GOTO to branch back to the FILEREAD statement. Processing continues with the next record. See Example 9-128, "Error Handling".
Specifying a Target Object that has NTEXT Values
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) which 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.
Example 9-126 Dimension Values and Data
Suppose your analytic workspace contains six-character product identification numbers. You must import both product names and a value for the number of units sold each month. The data file for the last quarter has the following format.
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
The dimension and variables have the following definitions.
DEFINE month DIMENSION MONTH DEFINE productid DIMENSION ID DEFINE productname VARIABLE TEXT <productid> DEFINE units.sold VARIABLE INTEGER <month productid>
The following program uses FILEREAD to add any new values for month and productid to the analytic workspace and to put the data in the correct variables. Maintain dimensions in one FILEREAD statement, close the file, and process it again to get the associated data.
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
Example 9-127 Dimension Surrogate Values
This example uses one FILEREAD operation to add a value to the product dimension and assign a value to prodnum, which is a NUMBER dimension surrogate for the product dimension. It uses a second FILEREAD to assign a value to the units variable, which is dimensioned by month, product, and district. The data file for the dimension and surrogate values has the following format.
Kiyaks400
The following statements define a fileunit, open the file, read its contents and append a value to the product dimension and assign a value to the prodnum surrogate, and close the file.
DEFINE funit INT
funit = FILEOPEN('Ds.Dat' READ)
FILEREAD funit COL 1 APPEND W 6 product COL 7 ASSIGN W 3 prodnum
FILECLOSE funit
The data file for the variable value has the following format.
Jan02400Boston416
The following statements open the file, read its contents, match the value of the prodnum surrogate and assign a value to the units variable, and close the file.
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
When your input file has data that does not match the format specifications, or when it has a dimension value that is not part of the analytic workspace when you are using the default MATCH attribute, you get an error. You can use error processing at the trap label to check for that kind of error, skip the bad record, and continue processing the file. You can also use a FILEPUT statement to store the bad records in a separate file (see the FILEPUT command).
In the following example, the statements at the trap label check whether the file was successfully opened (fil.unit has an INTEGER value) and whether the user interrupted the program. When these are not the reason for the error, the program assumes it encountered a bad record, resets the trap, and branches back to the FILEREAD statement to continue processing with the next record.
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
Example 9-129 Preprocessing File Data Before Assigning to an analytic workspace Object
You can also process the data in each field before assigning it to a variable or dimension in the analytic workspace. Suppose your data file has product identifiers that are six-digit numbers, and your analytic workspace has a product dimension whose values are these same product numbers, preceded by a "P." You can process the identifiers in the file by adding a "P" at the beginning of each value.
FILEREAD unit COLUMN 1 WIDTH 6 APPEND LSET 'p' product
Example 9-130 Maintaining Conjoint Dimensions with File Data
To maintain a conjoint dimension with FILEREAD, you first maintain its base dimensions by appending any new values from the input file. Then you assign the resulting combination of base dimension values to the conjoint dimension. The following example gets base dimension values from two separate fields, appends the values to the base dimensions, then appends the combination to the conjoint dimension.
FILEREAD unit APPEND proddist = <W 8 product, W 8 district>
In the preceding statement, the angle brackets automatically cause APPEND to apply to all three dimensions. When you do not want to add new values to the base dimensions, but want only to add new conjoint dimension values, you must explicitly state the keyword MATCH or change the order of the target objects, as shown in the two following statements.
fileread unit APPEND proddist = <W 8 MATCH product,W 8 MATCH district>
or
FILEREAD unit W 8 product W 8 district APPEND proddist = <product, district>
Example 9-131 Reading Data From a Structured PRN File
Suppose you want to read data from a structured PRN file with values of the product dimension in field two, values of the district dimension in field three, and several months of sales values beginning in field six. You could read the first 10 records in the file with the following statement.
FILEREAD unit STOPAFTER 10 STRUCTURED FIELD 2 product - district FIELD 6 ACROSS month: sales
|
 Copyright © 1994, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|