WKSDATA

The WKSDATA function returns the data type of each individual cell in a worksheet or the data type of a program argument with the WORKSHEET data type. You can use WKSDATA to help in the process of transferring labels and data between text files and Oracle OLAP.

Return Value

The data type of individual worksheet cells.

Syntax

WKSDATA(worksheetname)

Parameters

worksheetname: Specifies the name of an Oracle OLAP worksheet object, such as workunits.

Usage Notes

Checking One or More Cells

You can use WKSDATA to return the data type of a single worksheet cell by using a qualified data reference for the cell, as in the following format.

SHOW WKSDATA(worksheetname(WKSROW n, WKSCOL n))

Or you can use a REPORT statement in this format with WKSDATA to provide the contents of all the cells in a worksheet side-by-side with their data types.

REPORT worksheetname WKSDATA(worksheetname)

Multiple Data Types

Always use care when using worksheet objects in expressions. Because a worksheet object can contain multiple data types, the actual data type of individual worksheet cells is not considered when an OLAP DML statement is compiled. Instead, code is generated to convert each worksheet cell to the data type it expects at that position in the expression which may lead to unexpected results in some cases.

Text Data

All textual data (as opposed to numeric, Boolean, date, and so on) in a worksheet has the TEXT data type. The ID and NTEXT data types are not supported in worksheets.

Examples

Example 8-169 Checking Data Imported from a Worksheet

Suppose you have imported a flat data file into a worksheet called workunits. You can use WKSDATA to provide a quick way to determine which areas to treat as dimension values and which as data values in bringing the worksheet into standard OLAP workspace format.

This statement produces this output following the statement that shows the data in workunits

REPORT workunits

               -----------------WORKUNITS-----------------
               ------------------WKSCOL-------------------
WKSROW             1          2          3          4
-------------- ---------- ---------- ---------- ----------
             1         NA Jan96      Feb96      Mar96
             2 Tents             307        209        277
             3 Canoes            352        411        488
             4 Racquets        1,024      1,098      1,144
             5 Sportswear      1,141      1,262      1,340
             6 Footwear        2,525      2,660      2,728

This statement uses the WKSDATA function to produce the report following the statement, which shows the data type of each cell in the worksheet.

REPORT WKSDATA(workunits)

               ------------WKSDATA(WORKUNITS)-------------
               ------------------WKSCOL-------------------
WKSROW             1          2          3          4
-------------- ---------- ---------- ---------- ----------
             1         NA TEXT       TEXT       TEXT
             2 TEXT       INTEGER    INTEGER    INTEGER
             3 TEXT       INTEGER    INTEGER    INTEGER
             4 TEXT       INTEGER    INTEGER    INTEGER
             5 TEXT       INTEGER    INTEGER    INTEGER
             6 TEXT       INTEGER    INTEGER    INTEGER