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.
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)
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.
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.
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