Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E12197-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

6 OLAP DML Functions: A - K

This chapter contains the following topics:

Reference topics for the remaining OLAP DML functions appear in alphabetical order in Chapter 7, "OLAP DML Functions: L - Z".

For other OLAP DML reference topics, see Chapter 4, "OLAP DML Properties", Chapter 5, "OLAP DML Options", Chapter 8, "OLAP DML Commands: A-G", and Chapter 9, "OLAP DML Commands: H-Z".

About OLAP DML Functions

OLAP functions work in much the same way as functions work in other programming languages. They initiate action and return a value. The one exception is the looping nature of OLAP DML functions as discussed in "OLAP DML Statements Apply to All of the Values of a Data Object".

Most of the OLAP DML functions are standard text and calculation functions. Other OLAP DML functions return more complex information. For example, the OLAP DML provides the AW and OBJ functions that you can use to retrieve many different types of information about an analytic workspace and its objects and the AGGREGATE function that you can use to calculate aggregate data on-the-fly at user request.

Additionally, you can augment the functionality of the OLAP DML by writing an OLAP DML program for use as a function.

Tip:

Many OLAP DML statements can be coded as a 3-character abbreviation that consists of the first letter of the statement plus the next two consonants.

Functions: Alphabetical Listing

A B C D E F G H I J K L M N O P Q R S T U V W Y

A


ABS
ADD_MONTHS
AGGCOUNT
AGGMAPINFO
AGGREGATE function
AGGREGATION
AGGROPS
ALLOCOPS
ANTILOG
ANTILOG10
ANY
ARCCOS
ARCSIN
ARCTAN
ARCTAN2
ARG
ARGCOUNT
ARGFR
ARGS
ASCII
ASCISSTR
AVERAGE
AW function

B


BACK
BASEDIM
BASEVAL
BEGINDATE
BIN_TO_NUM
BITAND
BLANKSTRIP

C


CALLTYPE
CHARTOROWID
CATEGORIZE
CEIL
CHANGEBYTES
CHANGECHARS
CHANGEDRELATIONS
CHANGEDVALUES
CHARLIST
CHGDIMS
CHR
COALESCE
COLVAL
CONTEXT function
CONVERT
CORRELATION
COS
COSH
COUNT
CUMSUM
CURRENT_DATE
CURRENT_TIMESTAMP

D


DAYOF
DBTIMEZONE
DDOF
DECODE
DEPRDECL
DEPRDECLSW
DEPRSL
DEPRSOYD

E


ENDDATE
ENDOF
EVERY
EXISTS
EXP
EVERSION
EXTBYTES
EXTCHARS
EXTCOLS
EXTLINES
EXTRACT

F


FCOPEN
FCQUERY
FILEERROR
FILEGET
FILENEXT
FILEOPEN
FILEQUERY
FILTERLINES
FINDBYTES
FINDCHARS
FINDLINES
FINTSCHED
FLOOR
FPMTSCHED
FROM_TZ

G


GET
GREATEST
GROUPINGID function
GROWRATE

H


HEXTORAW
HIERCHECK
HIERHEIGHT function
HIERSHAPE

I


INFO
INITCAP
INLIST
INSBYTES
INSCHARS
INSCOLS
INSLINES
INSTAT
INSTR functions
INTPART
IRR
ISDATE
ISEMPTY
ISINFINITE
ISNAN
ISSESSION
ISVALUE

J


JOINBYTES
JOINCHARS
JOINCOLS
JOINLINES

K


KEY

L


LAG
LAGABSPCT
LAGDIF
LAGPCT
LARGEST
LAST_DAY
LEAD
LEAST
LIMIT function
LIMITMAPINFO
LNNVL
LOCALTIMESTAMP
LOG function
LOG10
LOWCASE
LOWER
LPAD
LTRIM

M


MAKEDATE
MAX
MAXBYTES
MAXCHARS
MEDIAN
MIN
MMOF
MODE
MODULO
MONTHS_BETWEEN
MOVINGAVERAGE
MOVINGMAX
MOVINGMIN
MOVINGTOTAL

N


NA2
NAFILL
NAFLAG
NANVL
NEW_TIME
NEXT_DAY
NLS_CHARSET_ID
NLS_CHARSET_NAME
NLSSORT
NONE
NORMAL
NPV
NULLIF
NUMBYTES
NUMCHARS
NUMLINES
NUMTODSINTERVAL
NUMTOYMINTERVAL
NVL
NVL2

O


OBJ
OBJLIST
OBSCURE
ORA_HASH

P


PARTITION
PARTITIONCHECK
PERCENTAGE

Q


QUAL

R


RANDOM
RANK
RAWTOHEX
RECNO
REGEXP_COUNT
REGEXP_INSTR
REGEXP_REPLACE
REGEXP_SUBSTR
REM
REMAINDER
REMBYTES
REMCHARS
REMCOLS
REMLINES
REPLBYTES
REPLCHARS
REPLCOLS
REPLLINES
RESERVED
ROUND
ROW function
ROWIDTOCHAR
ROWIDTONCHAR
RPAD
RTRIM
RUNTOTAL

S


SESSIONTIMEZONE
SIGN
SIN
SINH
SMALLEST
SMOOTH
SORT function
SORTLINES
SOUNDEX
SQLFETCH
SQRT
STARTOF
STATALL
STATDEPTH
STATEQUAL
STATFIRST
STATLAST
STATLEN
STATLIST
STATMAX
STATMIN
STATRANK
STATVAL
STDDEV
SUBSTR functions
SUBTOTAL
SYS_CONTEXT
SYSDATE
SYSINFO
SYSTEM
SYSTIMESTAMP

T


TALLY
TAN
TANH
TCONVERT
TEXTFILL
TO_ BINARY_DOUBLE
TO_BINARY_FLOAT
TO_CHAR
TO_DATE
TO_DSINTERVAL
TO_NCHAR
TO_NUMBER
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TOD
TODAY
TOTAL
TRANSLATE
TRIGGER function
TRIM
TRUNCATE
TZ_OFFSET

U


UNIQUELINES
UNRAVEL
UPPER
UPPER

V


VALSPERPAGE
VALUES
VINTSCHED
VPMTSCHED
VSIZE

W


WEEKOF
WIDTH_BUCKET
WKSDATA
WRITABLE

Y


YYOF

Functions by Category

The OLAP DML provides the typical numeric and text functions:

Conversion functions
Datetime functions
Date-only functions
Financial functions
File management functions
NA functions
Numeric (general) functions
Numeric aggregation functions
Program argument and context functions
Reporting functions
Statistical and forecasting functions
Text functions

It also has functions that are unique to its data model:

Analytic workspace and object information functions
Aggregation, allocation, and model specification functions
System and Database information functions
Time-series functions
Object value retrieval functions
Status manipulation functions

Analytic workspace and object information functions


AW function
CHANGEDRELATIONS
CHANGEDVALUES
BASEDIM
EXISTS
ISEMPTY
ISSESSION
ISVALUE
LIMITMAPINFO
OBJ
OBJLIST
PARTITION
RESERVED
TALLY
TRIGGER function
VALSPERPAGE
VSIZE
WRITABLE

Aggregation, allocation, and model specification functions


AGGCOUNT
AGGMAPINFO
AGGREGATE function
AGGREGATION
AGGROPS
ALLOCOPS
HIERCHECK
INFO
CHANGEDRELATIONS
CHANGEDVALUES
ISEMPTY
PARTITIONCHECK

Conversion functions


ASCII
ASCISSTR
BIN_TO_NUM
CHR
CONVERT
FROM_TZ
HEXTORAW
RAWTOHEX
TCONVERT
TO_ BINARY_DOUBLE
TO_BINARY_FLOAT
TO_CHAR
TO_DATE
TO_NCHAR
TO_NUMBER
TO_TIMESTAMP
TO_TIMESTAMP_TZ

System and Database information functions


CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
LOCALTIMESTAMP
SESSIONTIMEZONE
SYS_CONTEXT
SYSDATE
SYSINFO
SYSTEM

Datetime functions


ADD_MONTHS
CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
ROUND (datetime)
NEW_TIME
NUMTODSINTERVAL
NUMTOYMINTERVAL
SESSIONTIMEZONE
SYSDATE
SYSTIMESTAMP
TO_DSINTERVAL
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TOD
TRIM
TRUNCATE (datetime)
TZ_OFFSET

Date-only functions


BEGINDATE
DAYOF
DDOF
ENDDATE
ENDOF
ISDATE
MAKEDATE
MMOF
STARTOF
TODAY
WEEKOF
YYOF

Financial functions


DEPRDECL
DEPRDECLSW
DEPRSL
DEPRSOYD
FINTSCHED
FPMTSCHED
GROWRATE
IRR
NPV
VINTSCHED
VPMTSCHED

File management functions


FILEERROR
FILEGET
FILENEXT
FILEOPEN
FILEQUERY
GET
RECNO

NA functions


COALESCE
NA2
NAFILL
NAFLAG
NULLIF
NVL
NVL2

Numeric (general) functions


ABS
ANTILOG
ANTILOG10
ARCCOS
ARCSIN
ARCTAN
ARCTAN2
BIN_TO_NUM
BITAND
CEIL
COS
COSH
DECODE
EXP
FLOOR
GREATEST
INTPART
ISINFINITE
ISNAN
LEAST
LOG function
LOG10
MAX
MIN
MODULO
NANVL
NULLIF
ORA_HASH
RANDOM
RANK
REM
REMAINDER
ROUND (number)
SIGN
SIN
SINH
SMOOTH
SORT function
SQRT
TAN
TANH
TRUNCATE (number)
VSIZE
WIDTH_BUCKET

Numeric aggregation functions


ANY
AVERAGE
COUNT
EVERY
LARGEST
MEDIAN
MODE
NONE
PERCENTAGE
SMALLEST
TCONVERT
TOTAL

Object value retrieval functions


BASEVAL
CHGDIMS
COALESCE
HIERHEIGHT function
KEY
QUAL
ROW function
SORT function
UNRAVEL
VALUES
WKSDATA

Program argument and context functions


ARG
ARGCOUNT
ARGFR
ARGS
BACK
CALLTYPE
CONTEXT function

Reporting functions


COLVAL
RUNTOTAL
SUBTOTAL

Statistical and forecasting functions


CATEGORIZE
CORRELATION
FCOPEN
FCQUERY
INFO
NORMAL
ORA_HASH
RANDOM
STDDEV

Status manipulation functions


CHGDIMS
INSTAT
LIMIT function
STATALL
STATDEPTH
STATEQUAL
STATFIRST
STATLAST
STATLEN
STATLIST
STATMAX
STATMIN
STATRANK
STATVAL
VALUES

Text functions


ASCII
ASCISSTR
BLANKSTRIP
CHANGEBYTES
CHANGECHARS
CHARLIST
EXTBYTES
EXTCHARS
EXTCOLS
EXTLINES
FILTERLINES
FINDBYTES
FINDCHARS
FINDLINES
INITCAP
INLIST
INSBYTES
INSCHARS
INSCOLS
INSLINES
INSTR functions
JOINBYTES
JOINCHARS
JOINCOLS
JOINLINES
LEAST
LENGTH functions
LOWCASE
LOWER
LPAD
LTRIM
MAXBYTES
MAXCHARS
NLS_CHARSET_ID
NLS_CHARSET_NAME
NLSSORT
NULLIF
NUMBYTES
NUMCHARS
NUMLINES
OBSCURE
REGEXP_COUNT
REGEXP_INSTR
REGEXP_REPLACE
REGEXP_SUBSTR
REMBYTES
REMCHARS
REMCOLS
REMLINES
REPLBYTES
REPLCHARS
REPLCOLS
REPLLINES
RPAD
RTRIM
SORT function
SORTLINES
SOUNDEX
SUBSTR functions
TEXTFILL
TRANSLATE
TRIM
UNIQUELINES
UPPER
UPPER
VSIZE

Time-series functions


CUMSUM
LAG
LAGABSPCT
LAGDIF
LAGPCT
LEAD
MOVINGAVERAGE
MOVINGMAX
MOVINGMIN
MOVINGTOTAL

ABS

The ABS function calculates the absolute value of an expression. Since, the absolute value of a real number is its numeric value without regard to its sign (for example, 3 is the absolute value of both 3 and -3), this function always returns a positive value.

Return Value

DECIMAL.

The dimensionality of the result is the same as the specified expression.

Syntax

ABS(expression)

Arguments

expression

The expression whose absolute value is to be calculated.

Examples

Example 6-1 Finding Values in an Absolute Range

Suppose you are interested in how close your planned 1996 sales figures for sportswear in Boston were to the actual sales. You would like to see those months where budgeted figures are off by more than $5,000 in either direction. You can use ABS to help you find those months.

LIMIT product TO 'Sportswear'
LIMIT district TO 'Boston'
LIMIT month TO YEAR 'Yr96'
LIMIT month KEEP ABS(sales - sales.plan) GT 5000
REPORT DOWN month sales sales.plan sales - sales.plan

These statements produce the following output.

DISTRICT: BOSTON
               ------------PRODUCT-------------
               -----------SPORTSWEAR-----------
                                      SALES -  
MONTH            SALES    SALES.PLAN SALES.PLAN
-------------- ---------- ---------- ----------
Jun96           79,630.20  73,568.52   6,061.68
Jul96           95,707.30  80,744.18  14,963.12
Aug96           82,004.00  71,811.45  10,192.55
Sep96           89,988.60  78,282.07  11,706.53
Dec96           50,281.40  56,720.87  -6,439.47

ADD_MONTHS

The ADD_MONTHS function returns the date that is n months after the specified date.

Return Value

DATETIME

Syntax

ADD_MONTHS(start_datetimen)

Arguments

start_datetime

A DATETIME expression that identifies the starting date. When the day component of start_datetime is the last day of the month or when the returned month has fewer days, then the returned day component is the last day of the month. Otherwise, the day component of the returned date is the same as the day component of start_datetime. See Example 6-2, "End-of-Month Calculation".

n

An INTEGER that identifies the number of months to be added to start_datetime.

Examples

Example 6-2 End-of-Month Calculation

The following statement displays the date of the day that is one month after January 30, 2000.

SHOW ADD_MONTHS('30Jan00', 1)

Since February 29 was the last day of February 2000, ADD_MONTHS returns February 29, 2000.

29-Feb-00

AGGCOUNT

The AGGCOUNT function retrieves the values of the Aggcount variable associated with the specified variable. An Aggcount variable is an INTEGER variable that Oracle OLAP automatically creates when it creates a variable defined using a DEFINE VARIABLE statement that includes a USING AGGOUNT phrase.

Return Value

INTEGER

The values of the Aggcount variable that are the non-NA counts of the number of leaf nodes that contribute to the calculation of aggregate values when RELATION (for aggregation) statements that have an AVERAGE, HAVERAGE, WAVERAGE, or HWAVERAGE execute.

Syntax

AGGCOUNT(variable-name)

Arguments

variable-name

The name of the variable with which the Aggcount variable is associated.

Examples

Example 6-3 Reporting on an Aggcount Variable

Assume that within your analytic workspace you have objects with the following definitions.

DEFINE geog DIMENSION TEXT
DEFINE time DIMENSION TEXT
DEFINE product DIMENSION TEXT
DEFINE cc_geog_product COMPOSITE <geog product> COMPRESSED
DEFINE sales VARIABLE DECIMAL <time cc_geog_product <geog product>> WITH AGGCOUNT
DEFINE geog_parentrel RELATION geog <geog>
DEFINE product_parentrel RELATION product <product>
DEFINE time_parentrel RELATION time <time>
DEFINE aggsales AGGMAP
   AGGMAP
   RELATION time_parentrel OPERATOR AVERAGE ARGS COUNT YES
   RELATION geog_parentrel
   RELATION product_parentrel
   END
 

Notice that the definition for the sales variable includes a request for an Aggcount variable and that, within the aggsales aggmap, the RELATION statement for the time_parentrel relation incudes an AVERAGE operator.

Assume also that when only the base values of the sales variable are populated, sales has the following values for Radios and TVs.

REPORT sales

PRODUCT: Radio
               -------------SALES--------------
               --------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine              122.93     176.69         NA
California         168.32     150.92         NA
Quebec                 NA         NA         NA
Ontario            187.46     164.46         NA
USA                    NA         NA         NA
Canada                 NA         NA         NA
World                  NA         NA         NA
 
PRODUCT: TV
               -------------SALES--------------
               --------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine              184.75     135.40         NA
California         139.89     145.71         NA
Quebec                 NA         NA         NA
Ontario            123.63     113.32         NA
USA                    NA         NA         NA
Canada                 NA         NA         NA
World                  NA         NA         NA
 
PRODUCT: AV
               -------------SALES--------------
               --------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine                  NA         NA         NA
California             NA         NA         NA
Quebec                 NA         NA         NA
Ontario                NA         NA         NA
USA                    NA         NA         NA
Canada                 NA         NA         NA
World                  NA         NA         NA
 

Since no aggregation has occurred, for AV, Oracle OLAP has not yet populated the Aggcount variable and the Aggcount variable for sales contains only NA values.

Now assume that you aggregate the sales variable by issuing the following statement.

AGGREGATE sales USING aggsales

A report of sales shows the following values.

REPORT sales

PRODUCT: Radio
               -------------SALES--------------
               --------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine              122.93     176.69     149.81
California         168.32     150.92     159.62
Quebec                 NA         NA         NA
Ontario            187.46     164.46     175.96
USA                291.24     327.61     309.42
Canada             187.46     164.46     175.96
World              478.70     492.07     485.38
 
PRODUCT: TV
               -------------SALES--------------
               --------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine              184.75     135.40     160.07
California         139.89     145.71     142.80
Quebec                 NA         NA         NA
Ontario            123.63     113.32     118.47
USA                324.64     281.11     302.87
Canada             123.63     113.32     118.47
World              448.27     394.42     421.35
 
PRODUCT: AV
               -------------SALES--------------
               --------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine              307.67     312.08     309.88
California         308.21     296.63     302.42
Quebec                 NA         NA         NA
Ontario            311.09     277.78     294.43
USA                615.88     608.71     612.30
Canada             311.09     277.78     294.43
World              926.97     886.49     906.73

A report of the Aggcount variable shows that it is populated with the INTEGER values that are needed to aggregate the average sales.

REPORT AGGCOUNT (sales)

PRODUCT: Radio
               --------AGGCOUNT (SALES)--------
               --------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine                  NA         NA          2
California             NA         NA          2
Quebec                 NA         NA         NA
Ontario                NA         NA          2
USA                     2          2          4
Canada                 NA         NA          2
World                   3          3          6
 
PRODUCT: TV
               --------AGGCOUNT (SALES)--------
               --------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine                  NA         NA          2
California             NA         NA          2
Quebec                 NA         NA         NA
Ontario                NA         NA          2
USA                     2          2          4
Canada                 NA         NA          2
World                   3          3          6
 
PRODUCT: AV
               --------AGGCOUNT (SALES)--------
               --------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine                   2          2          4
California              2          2          4
Quebec                 NA         NA         NA
Ontario                 2          2          4
USA                     4          4          8
Canada                  2          2          4
World                   6          6         12

AGGMAPINFO

The AGGMAPINFO function returns information about the specification of an aggmap object in your analytic workspace.

You can get information about an aggregation specification (that is, an aggmap object with a map type of AGGMAP) only after it has been compiled. You can compile an aggregation specification using a COMPILE statement or by including the FUNCDATA keyword when you execute the AGGREGATE command. When an aggregation specification has not been compiled before you use it with the AGGMAPINFO function, then it is compiled by AGGMAPINFO. You do not have to compile an aggmap for use with ALLOCATE.

Return Value

Varies depending on the type of information that is requested. See Table 6-1, "Keywords for the choice Parameter of the AGGMAPINFO function" for more information.

Syntax

AGGMAPINFO (name {choice | {choice-at-position rel-pos} })

Arguments

name

The name of the aggmap object.

choice

Specifies the type of information returned. See Table 6-1, "Keywords for the choice Parameter of the AGGMAPINFO function", for details.

Table 6-1 Keywords for the choice Parameter of the AGGMAPINFO function

Keyword Data Type Description

ADDED_MODELS

TEXT

The models that are currently added to an aggmap using AGGMAP ADD or REMOVE model statements.The names of the models are returned as a multi-line text string.

AGGINDEX

BOOLEAN

Indicates the setting for the AGGINDEX statement in the aggmap. A YES setting specifies that all possible indexes (composite tuples) are created whenever the aggmap is recompiled. (Applies to AGGMAP type aggmaps only.)

CHILDREN member-name

TEXT

The dimension members used in the right-hand side of equations used to calculate temporary calculated members added using MAINTAIN ADD SESSION statements. The names of the members are returned as a multi-line text string.

CUSTOMMEMBERS

TEXT

The members added using MAINTAIN ADD SESSION statements. The names of the members are returned as a multi-line text string.

DIMENSION

TEXT

The names of the dimensions of the models or relations used by the aggmap. The names of the members are returned as a multi-line text string.

FCACHE

BOOLEAN

Indicates whether Oracle OLAP has a cache for the AGGREGATE function. (Applies to AGGMAP type aggmaps only.)

MAPTYPE

TEXT

The type of the aggmap.

  • Returns AGGMAP for an aggregation specification (that is, when the specification has been entered with an AGGMAP statement). You can use this type of aggmap only with the AGGREGATE command or AGGREGATE function.

  • Returns ALLOCMAP for an allocation specification (that is, when the specification has been entered with an ALLOCMAP statement). You can use this type of aggmap only with ALLOCATE.

  • Returns NA when the aggmap has been defined but a specification has not been entered with an AGGMAP or ALLOCMAP statement.

MODELS

TEXT

The models in the aggmap. The names of the models are returned as a multi-line text string.

NUMRELS

INTEGER

The total number of RELATION statements in an aggmap specification.

RELATIONS

TEXT

The name of relation that is specified by a RELATION statement in the aggmap specification. Each statement is displayed on a separate line.

STORE

BOOLEAN

Indicates whether the CACHE statement in the aggmap is set to STORE. A YES setting specifies that the data that is calculated on the fly is stored in the cache. (Applies to AGGMAP-type aggmaps only.)

VARIABLES

TEXT

The variables for which this aggmap object has been specified as the default aggmap using AGGMAP ADD or REMOVE model statements or the $AGGMAP property. The names of the variables are returned as a multi-line text string.


choice-at-position

Specifies the exactly which piece of information you want returned.

PRECOMPUTE returns the text of the limit clause that follows the PRECOMPUTE keyword in a RELATION statement. You must use the rel-pos argument to specify a single RELATION statement. Returns NA when the RELATION statement does not have a PRECOMPUTE keyword. (Applies to AGGMAP type aggmaps only.)

RELATION returns the name of the relation that follows the RELATION statement that you specify with the rel-pos argument.

STATUS returns the status list that results from the compilation of the PRECOMPUTE clause in the RELATION statement that you specify with the rel-pos argument. (Applies to AGGMAP type aggmaps only.)

rel-pos

An INTEGER that specifies a RELATION statement in the aggmap. The INTEGER indicates the position of the statement in the list of RELATION statements. You can use the rel-pos argument only with the RELATION, PRECOMPUTE, or STATUS keywords. For example, to get information about the first RELATION statement in an aggmap, use an INTEGER with a value of 1 as the rel-pos argument. To get information about the fourth RELATION statement in an aggmap, use the INTEGER 4, and so on. You may use any INTEGER between 1 and the total number of RELATION statements in an aggmap specification. You can use the NUMRELS keyword to obtain the total number of RELATION statements for an aggmap object.

Examples

Example 6-4 Retrieving Information About an Aggmap Object

Suppose an aggmap named sales.agg has been defined with the following statement.

DEFINE sales.agg AGGMAP <time, product, geography>

Suppose the following specification has been added to sales.agg with an AGGMAP statement.

AGGMAP
RELATION time.r PRECOMPUTE (time ne 'Year98')
RELATION product.r
RELATION geography.r
CACHE STORE
END

Once a specification has been added to the aggmap, you can use AGGMAPINFO to get information about its specification.

To see the names of the hierarchies that are specified by the RELATION statements, use the following statement.

SHOW AGGMAPINFO(sales.agg RELATIONS)

The following results are displayed.

time.r
product.r
geography.r

The following statement and result tell you how many RELATION statements are in the aggmap object.

SHOW AGGMAPINFO(sales.agg NUMRELS)
3

The following statement and result verifies that data that is calculated on the fly is stored in the cache for the session. The result is YES because the aggmap contains a CACHE STORE statement.

show AGGMAPINFO(sales.agg STORE)
YES

The following statement displays the relation name that is specified in the second RELATION statement in the aggmap.

SHOW AGGMAPINFO(sales.agg RELATION 2)
product.r

The following statement displays the limit clause that follows the PRECOMPUTE keyword in the first RELATION statement in the aggmap.

SHOW AGGMAPINFO(sales.agg PRECOMPUTE 1)
time NE 'YEAR98'

Suppose the time dimension values are Jan98 to Dec99, Year98, and Year99. The following statement displays the status list for the dimension in the first RELATION statement in the aggmap.

SHOW AGGMAPINFO(sales.agg STATUS 1)
Jan98 TO Dec99, Year99

Because the limit clause in the RELATION statement specifies that the time dimension values should not equal Year98, all time dimension values other than Year98 are included in its status.

The following statement displays the aggmap type of sales.agg.

SHOW AGGMAPINFO(sales.agg MAPTYPE)
AGGMAP 

AGGREGATE function

The AGGREGATE function calculates the data in the variable that is not specified as PRECOMPUTE in the specified aggmap. (For information about specifying precompute data, see PRECOMPUTE statement and the PRECOMPUTE clause of the RELATION (for aggregation) statement.) The aggregation is limited to those values that are currently in status.

Note:

When the variable you want to aggregate has an $AGGMAP property, you do not have to explicitly use the AGGREGATE function to aggregate nonprecomted data in that variable.

Return Value

The same data type as the aggregated variable.

Syntax

AGGREGATE (var ... [USING aggmap] -

[FROM fromspec|FROMVAR textvar] [FORCECALC FORCEORDER] [COUNTVAR countvar])

Arguments

var

The name of the variable whose data is calculated (if necessary) and returned.

USING

This keyword indicates that the aggregation is performed using the specified aggmap.

aggmap

The name of a previously-defined aggmap that specifies how the data is aggregated. For information about aggmaps, see DEFINE AGGMAP.

FROM

This keyword indicates that the detail data is obtained from a different object. A FROM clause is only one way in which you can specify the variable from which detail data should be obtained when performing aggregation. See "Ways of Specifying Where to Obtain Detail Data for Aggregation".

fromspec

An arbitrarily dimensioned variable, formula, or relation from which the detail data for the aggregation is obtained.

FROMVAR

This keyword indicates that the detail data is obtained from different objects to perform a capstone aggregation. A FROMVAR clause is only one way in which you can specify the variable from which detail data should be obtained when performing aggregation. See "Ways of Specifying Where to Obtain Detail Data for Aggregation".

textvar

An arbitrarily dimensioned variable used to resolve any leaf nodes. Specify NA to indicate that a node does not need detail data to calculate the value.

FORCECALC

Specifies that any value that is not specified in a PRECOMPUTE clause of a RELATION statement that are in the aggmap should be recalculated, even when there is a value stored in the desired cell. Use the FORCECALC keyword when you want users to be able to change detail data cells and see the changed values reflected in dynamically-computed aggregate cells.

Note:

You can also set an $AGGREGATE_FORCECALC property on a variable to specify this behavior as the default aggregation behavior. In this case, you do not have to include the FORCECALC keyword with the AGGREGATE function.
FORCEORDER

Specifies that the calculation must be performed in the order in which the RELATION statements are listed in the aggmap. Use this option when you have changed some values calculated by the AGGREGATE command. Otherwise, the optimization methods used by the AGGREGATE function may cause the modified values to be ignored. FORCEORDER slows performance.

Note:

You can also set an $AGGREGATE_FORCEORDER property on a variable to specify this behavior as the default aggregation behavior. In this case, you do not have to include the FORCEORDER keyword with the AGGREGATE function.
COUNTVAR countvar

Indicates that Oracle OLAP should use the user-defined variable specified by countvar to store the non-NA counts of the number of leaf nodes that contributed to aggregate values calculated for RELATION (for aggregation) statements that have an AVERAGE, HAVERAGE, HWAVERAGE, or WAVERAGE operator.

Note:

Typically, you do not use a user-defined Countvar variable to store the counts for average aggregations. Instead, you use an Oracle OLAP-created Aggcount variable. You must use an Aggcount variable when the aggregation specification includes a RELATION (for aggregation) statement with an average operator that is for a compressed composite.

For more information on Aggcount variables, see "Aggcount Variables".

The countvar variable must be an INTEGER variable with the same dimensions in the same order as the dimensions of the variable specified by var. When you aggregate several variables together, you must define an INTEGER variable for each one to record the results.

Notes

Steps for Supporting Run-Time Calculations

Follow these steps when combining pre-aggregation with run-time aggregation:

  1. Create an aggmap that limits the amount of data to be precalculated.

  2. Execute the AGGREGATE command with the FUNCDATA argument.

  3. When you have made any changes after executing the AGGREGATE command (see "Compiling the Aggmap"), recompile the aggmap with a COMPILE statement.

  4. Add an $AGGREGATE_FROM property to the data variables (see "Using NA Values to Trigger Run-Time Calculations").

  5. UPDATE and COMMIT the analytic workspace.

Compiling the Aggmap

Be sure to compile the aggmap at the time you load data, either with an explicit COMPILE statement or with the FUNCDATA argument to the AGGREGATE command. Otherwise, the aggmap is recompiled at run time for each session in which the AGGREGATE function is used. Perform other calculations (such as calculating models) before you compile the aggmap.

You must recompile the aggmap after maintaining any of the dimensions in the aggmap definition or any of the relations that are included in the text of the aggmap.

Run-Time Changes to Data Values

When users are able to change data values at run time, then the data may get out of synchronization. You can prevent this problem in the following ways:

Using NA Values to Trigger Run-Time Calculations

By adding an $NATRIGGER property to a variable, you can implicitly call the AGGREGATE function each time the data is queried. The following statements cause sales data to be aggregated using the sales.aggmap aggmap.

CONSIDER sales
PROPERTY '$NATRIGGER' 'AGGREGATE(sales USING sales.aggmap)'

From now on, a statement such as REPORT SALES executes the AGGREGATE function, so that computed values are returned instead of NAs.

Using the AGGREGATE Function after Partial Rollups

When your batch window is not sufficiently long to preaggregate all of the data to generate, you can perform the aggregation in stages on consecutive days and use the AGGREGATE function to calculate the balance. For each stage, you must do the following:

  1. Change the PRECOMPUTE phrase of the RELATION statement in the aggmap so that new data is aggregated.

  2. Execute the AGGREGATE command with the FUNCDATA keyword.

  3. Verify that the $NATRIGGER property is set on the variables so that the AGGREGATE function calculates the balance of the data.

Using Multiple Aggmaps

Whenever possible, use only one aggmap to rollup a variable. However, in some situations, a variable requires multiple aggmaps to roll up the data in the desired manner. When a variable requires multiple aggmaps to rollup data problems are created when some data is calculated on the fly, because the metadata retained for the AGGREGATE function corresponds to the last aggmap. The AGGREGATE function needs metadata that is the union of all of the aggmaps used by the AGGREGATE command. The solution is to create an additional aggmap for use by the AGGREGATE function that correctly identifies the NA values. Be sure to compile this aggmap.

Do not use the AGGREGATE function with multiple aggmaps unless you feel comfortable answering the following question:

When the aggmap is compiled for use by the AGGREGATE function, does the status that results from each PRECOMPUTE clause accurately define the nodes within that dimension at which data has been pre-computed?

When you cannot answer "yes" to this question with confidence, do not use the AGGREGATE function with multiple aggmaps.

Examples

This section contains several examples of using the AGGREGATE function. For additional aggregation examples, see the examples for the AGGMAP command.

Example 6-5 Using the AGGREGATE Function as the Formula of an Expression

Example 8-31, "Capstone Aggregation" illustrates performing the final capstone aggregation using an AGGREGATE command. You could also perform the capstone aggregation at run time as the expression of a formula.

Assume that your analytic workspace contains the following object definitions.

DEFINE GEOG.D DIMENSION TEXT
DEFINE GEOG.PARENTREL RELATION GEOG.D <GEOG.D>
DEFINE TIME.D DIMENSION TEXT
DEFINE TIME.PARENTREL RELATION TIME.D <TIME.D>
DEFINE SALES_JAN76 VARIABLE INTEGER <GEOG.D>
DEFINE SALES_FEB76 VARIABLE INTEGER <GEOG.D>
DEFINE SALES_MAR76 VARIABLE INTEGER <GEOG.D>
DEFINE SALES_CAPSTONE76 VARIABLE INTEGER <GEOG.D TIME.D>
DEFINE CAPSTONE_SOURCE VARIABLE TEXT <TIME.D>

Now you create two aggmap objects with the following definitions. Note that in this case the capstone_aggmap consists of a RELATION statement with a PRECOMPUTE NA clause.

DEFINE LEAF_AGGMAP AGGMAP
AGGMAP
RELATION geog.parentrel OPERATOR SUM
END
 
DEFINE CAPSTONE_AGGMAP AGGMAP
AGGMAP
RELATION time.parentrel OPERATOR SUM PRECOMPUTE (NA)
END

In Example 8-31, "Capstone Aggregation", the final capstone aggregation is performed using an AGGREGATE command. In this example, the capstone aggregation is defined as a formula named f_sales_capstone76 that has an AGGREGATE function as the expression of the formula.

DEFINE F_SALES_CAPSTONE76 FORMULA INTEGER <GEOG.D TIME.D>
EQ AGGREGATE ( sales_capstone76 USING capstone_aggmap fromvar capstone_source)
 

When you report on the unaggregated variables and formulas in your analytic workspace, you see the following results.

GEOG.D          SALES_JAN76    SALES_FEB76    SALES_MAR76
-------------- -------------- -------------- --------------
Boston                  1,000          2,000          3,000
Medford                 2,000          4,000          6,000
San Diego               3,000          6,000          9,000
Sunnydale               4,000          8,000         12,000
Massachusetts              NA             NA             NA
California                 NA             NA             NA
United States              NA             NA             NA
 
               --------------------F_SALES_CAPSTONE76---------------------
               --------------------------TIME.D---------------------------
GEOG.D             Jan76          Feb76          Mar76           76Q1
-------------- -------------- -------------- -------------- --------------
Boston                  1,000          2,000          3,000          6,000
Medford                 2,000          4,000          6,000         12,000
San Diego               3,000          6,000          9,000         18,000
Sunnydale               4,000          8,000         12,000         24,000
Massachusetts              NA             NA             NA             NA
California                 NA             NA             NA             NA
United States              NA             NA             NA             NA
 
               ---------------------SALES_CAPSTONE76----------------------
               --------------------------TIME.D---------------------------
GEOG.D             Jan76          Feb76          Mar76           76Q1
-------------- -------------- -------------- -------------- --------------
Boston                  1,000          2,000          3,000             NA
Medford                 2,000          4,000          6,000             NA
San Diego               3,000          6,000          9,000             NA
Sunnydale               4,000          8,000         12,000             NA
Massachusetts              NA             NA             NA             NA
California                 NA             NA             NA             NA
United States              NA             NA             NA             NA
 

Now you aggregate the leaf variables using the following AGGREGATE statement.

AGGREGATE sales_jan76 sales_feb76 sales_mar76 USING leaf_aggmap

A report of the leaf variables shows that they are aggregated.

GEOG.D          SALES_JAN76    SALES_FEB76    SALES_MAR76
-------------- -------------- -------------- --------------
Boston                  1,000          2,000          3,000
Medford                 2,000          4,000          6,000
San Diego               3,000          6,000          9,000
Sunnydale               4,000          8,000         12,000
Massachusetts           3,000          6,000          9,000
California              7,000         14,000         21,000
United States          10,000         20,000         30,000

A report of the f_sales_capstone76 formula shows the aggregated values for 76Q1.

--------------------F_SALES_CAPSTONE76---------------------
               --------------------------TIME.D---------------------------
GEOG.D             Jan76          Feb76          Mar76           76Q1
-------------- -------------- -------------- -------------- --------------
Boston                  1,000          2,000          3,000          6,000
Medford                 2,000          4,000          6,000         12,000
San Diego               3,000          6,000          9,000         18,000
Sunnydale               4,000          8,000         12,000         24,000
Massachusetts           3,000          6,000          9,000         18,000
California              7,000         14,000         21,000         42,000
United States          10,000         20,000         30,000         60,000

While a report of the sales_capstone76 variable does not show the aggregated values for 76Q1 since they are not stored in the variable.

---------------------SALES_CAPSTONE76----------------------
               --------------------------TIME.D---------------------------
GEOG.D             Jan76          Feb76          Mar76           76Q1
-------------- -------------- -------------- -------------- --------------
Boston                  1,000          2,000          3,000             NA
Medford                 2,000          4,000          6,000             NA
San Diego               3,000          6,000          9,000             NA
Sunnydale               4,000          8,000         12,000             NA
Massachusetts           3,000          6,000          9,000             NA
California              7,000         14,000         21,000             NA
United States          10,000         20,000         30,000             NA

Example 6-6 Aggregating Data on the Fly for a Report

The units variable is aggregated entirely on the fly using the tp.agg aggmap.

This is the object definitions for the variable units.

DEFINE units VARIABLE INTEGER <time product>

The parent relation for time contains these values.

---TIME.PARENTREL----
      --TIME.HIERARCHIES---
TIME       STANDARD   YTD
---------- ---------- ----------
Jan01      Q1.01      Last.Ytd
Feb01      Q1.01      Last.Ytd
Mar01      Q1.01      Last.Ytd
Q1.01      2001       NA

The parent relation for the product dimension contains these values.

PRODUCT.PA
PRODUCT    RENTREL
---------- ----------
Food       Na
Snacks     Food
Drinks     Food
Popcorn    Snacks
Cookies    Snacks
Cakes      Snacks
Soda       Drinks
Juice      Drinks

In the units variable, data is stored only at the lowest level of each dimension hierarchy.

-------------------UNITS-------------------
  -------------------TIME--------------------
PRODUCT     Jan01      Feb01      Mar01      Q1.01
----------- ---------- ---------- ---------- ----------
Food        NA         NA         NA         NA
Snacks      NA         NA         NA         NA
Drinks      NA         NA         NA         NA
Popcorn     2          2          4          NA
Cookies     3          6          3          NA
Cakes       4          4          2          NA
Soda        7          3          9          NA
Juice       1          3          2          NA

The aggmap specifies that all data is calculated on the fly.

DEFINE tp.agg AGGMAP
LD <time product> Aggmap
AGGMAP
RELATION time.parentrel PRECOMPUTE (NA)
RELATION product.parentrel PRECOMPUTE (NA)
END

The following REPORT statement uses the AGGREGATE function to calculate the data.

REPORT aggregate(units USING tp.agg)

  -------AGGREGATE(UNITS USING TP.AGG)-------
  -------------------TIME--------------------
PRODUCT     Jan01      Feb01      Mar01      Q1.01
----------- ---------- ---------- ---------- ----------
Food        17         18         20         55
Snacks       9         12          9         30
Drinks       8          6         11         25
Popcorn      2          2          4          8
Cookies      3          6          3         12
Cakes        4          4          2         10
Soda         7          3          9         19
Juice        1          3          2          6

Example 6-7 Using $NATRIGGER to Aggregate Data

When the AGGREGATE function is added to units in the $NATRIGGER property, a simple REPORT statement displays aggregated results.

CONSIDER units
PROPERTY '$NATRIGGER' 'AGGREGATE(units USING tp.agg)'
REPORT units
 
  -------------------UNITS-------------------
  -------------------TIME--------------------
PRODUCT     Jan01      Feb01      Mar01      Q1.01
----------- ---------- ---------- ---------- ----------
Food        17         18         20         55
Snacks       9         12          9         30

Example 6-8 Calculating all but one Value on the Fly

The AGGREGATE function calculates the complement of the data specified in the PRECOMPUTE clause of the RELATION statement. It returns those values that are currently in status.

For example, when you are using an aggmap that contains this RELATION statement.

RELATION letter.letter PRECOMPUTE ('AA')

Then the AGGREGATE function calculates all aggregations except AA, as shown here.

REPORT AGGREGATE(units USING letter.aggmap)

                 AGGREGATE(UNITS
LETTER         USING LETTER.AGGMAP)
-------------- --------------------
A                                 3
AA                               NA
AB                                3
AAB                               2
ABA                               1
ABB                               2
AAAA                              1
AABA                              2
ABAA                              1
ABBB                              1
ABBA                              1
...

AGGREGATION

Within a model, the AGGREGATION function allows you to create a model that represents a custom aggregate. Such an aggmap can be used for dynamic aggregation with the AGGREGATE function.

Note:

Because the AGGREGATION function is intended only for dynamic aggregation, a model that contains such a function cannot be used with the AGGREGATE command.

Syntax

AGGREGATION(dimval-list)

Arguments

dimval-list

A list of one or more dimension values to include in the custom aggregation. The specified values must belong to the same dimension to which the target dimension value belongs. You must specify each dimension value as a text literal. That is, they cannot be represented by a text expression such as a variable.

Examples

Example 6-9 Using the AGGREGATION Function to Create a Custom Aggregate

The following lines of code from a program perform these steps:

  1. Add the new dimension value my_time to the time dimension.

    MAINTAIN time ADD 'My_Time'
    
  2. Define the model mytime_custagg and set the specification of the model using the AGGREGATION function.

    DEFINE mytime_custagg MODEL
    MODEL JOINLINES('DIMENSION time' 'My_Time = AGGREGATION(\'23\' \'24\')')
    

    (Note that backslash escape characters are required to include quotation marks within a quoted string.)

  3. Define the sales_aggmap aggmap.

    DEFINE sales_aggmap AGGMAP <time cpc <customer product channel> >
    AGGMAP
    RELATION prntrel.time
    RELATION prntrel.chan
    RELATION prntrel.prod
    RELATION prntrel.cust
    END
    
  4. Add the model mytime_custagg to sales_aggmap.

    AGGMAP ADD mytime_custagg TO sales_aggmap
    
  5. Limit the dimensions to the values of interest and run a report. 

    " Run a report
    LIMIT time TO 'My_Time' '23' '24'
    LIMIT channel TO '5'
    LIMIT product TO '70'
    LIMIT customer TO '114'
    REPORT DOWN time AGGREGATE(sales USING sales_aggmap)
    

    The report generates the following output.

    CHANNEL: 5
    PRODUCT: 70
                   --AGGREGATE(SALES---
                   USING SALES_AGGMAP)-
                   ------CUSTOMER------
    TIME                   114
    -------------- --------------------
    my_time                  682,904.34
    23                        84,982.92
    24                       597,921.42
    

AGGROPS

The AGGROPS function returns the keywords for all of the aggregation operators that you can specify in a RELATION (for aggregation) statement, listed one name on each line in a multiline text value.

Return Value

TEXT

Syntax

AGGROPS

Example

Example 6-10 Displaying a List of the Aggregation Operators

When you issue a a AGGROPS statement, Oracle OLAP returns a list of all of the aggregation operators.

SHOW AGGROPS

SUM
WSUM
SSUM
AND
OR
FIRST
LAST
HFIRST
HLAST
AVERAGE
WAVERAGE
HAVERAGE
HWAVERAGE
MIN
MAX
WFIRST
WLAST
HWFIRST
HWLAST
WMIN
WMAX
NOAGG

ALLOCOPS

The ALLOCOPS function returns the keywords for all of the allocation operators that you can specify in a RELATION (for allocation) statement, listed one name on each line in a multiline text value.

Return Value

TEXT

Syntax

ALLOCOPS

Examples

Example 6-11 Displaying a List of the Allocation Operators

When you issue a a ALLOCOPS statement, Oracle OLAP returns a list of all of the allocation operators.

SHOW ALLOCOPS

FIRST
LAST
HFIRST
HLAST
MIN
MAX
EVEN
HEVEN
COPY
HCOPY
PROPORTIONAL

ANTILOG

The ANTILOG function calculates the value of e (the base of natural logarithms) raised to a specific power.

Return Value

DECIMAL

Syntax

ANTILOG(n)

Arguments

n

The power of e to be returned by the ANTILOG function.

Examples

Example 6-12 Calculating the Value of e Raised to the Second Power

The following function calculates the value of e raised to the second power.

ANTILOG(2)

This function returns the following value.

7.38906

ANTILOG10

The ANTILOG10 function calculates the value of 10 raised to a specified power.

Return Value

DECIMAL

Syntax

ANTILOG10(n)

Arguments

n

The power of 10 to be returned by the ANTILOG10 function.

 

Examples

Example 6-13 Calculating the Value of Ten Raised to the Third Power

The following function calculates the value of 10  raised to the third power.

ANTILOG10(3)

This function returns the following value.

1,000.00

ANY

The ANY function returns YES when any values of a Boolean expression are TRUE, or NO when none of the values of the expression are TRUE.

Return Value

BOOLEAN.

Syntax

ANY(boolean-expression [CACHE] [dimension ...] )

Arguments

boolean-expression

The Boolean expression to be evaluated

CACHE

Specifies slightly different internal behavior. Specify this keyword only when the original performance is extremely slow.

dimension

The name of a dimension of the result; or, the name of a relation between one dimension of boolean-expression and another dimension that you want as a dimension of the result.

By default, ANY returns a single YES or NO value. When you indicate one or more dimensions for the result, ANY tests for TRUE values along the dimensions that are specified and returns an array of values. Each dimension must be either a dimension of boolean-expression or related to one of its dimensions.

Tip:

When you specify a dimension that is not an actual dimension of boolean-expression, but, instead, is dimension that is related to a dimension of boolean-expression and when there are multiple relations between the two dimensions, Oracle OLAP uses the default relation between the dimensions to perform the calculation. (See the RELATION command for more information on default relations.) When you do not want Oracle OLAP to use this default relation, specify the related dimension by specifying the name of a specify relation.

Notes

The Effect of NASKIP on ANY

ANY is affected by the NASKIP option. When NASKIP is set to YES (the default), ANY ignores NA values and returns YES when any of the values of the expression that are not NA are TRUE and returns NO when none of the values are TRUE. When NASKIP is set to NO, ANY returns NA when any value of the expression is NA. When all the values of the expression are NA, ANY returns NA for either setting of NASKIP.

Data with a Type of DAY, WEEK, MONTH, QUARTER, or YEAR

When boolean-expression is dimensioned by a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR, you can specify any other dimension of this type as a related dimension. Oracle OLAP uses the implicit relation between these dimensions. To control the mapping of one of these dimension to another (for example, from weeks to months), you can define an explicit relation between the dimensions and specify the name of the relation as the dimension argument to the ANY function.

For each time period in the related dimension, Oracle OLAP tests the data values for all the source time periods that end in the target time period. This method is used regardless of which dimension has the more aggregate time periods.

Examples

Example 6-14 Testing for Any True Values by District

Suppose you want to find out which districts had at least one month with sales greater than $150,000 for sportswear. You use the ANY function to determine whether the Boolean expression (sales GT 150000) is TRUE for any month. To have the result dimensioned by district, specify district as the second argument in the ANY function.

LIMIT product TO 'SPORTSWEAR'
REPORT HEADING 'High Sales' ANY(sales GT 150000, district)

The preceding statements produce the following output.

DISTRICT       High Sales
-------------- ----------
Boston                 NO
Atlanta               YES
Chicago                NO
Dallas                YES
Denver                 NO
Seattle                NO

Example 6-15 Testing for Any True Values by Region

You might also want to find out which regions had at least one month in which at least one district had sportswear sales greater than $150,000. Since the region dimension is related to the district dimension, you can specify region instead of district as a dimension for the results of ANY.

report heading 'High Sales' any(sales gt 150000, region)

The preceding statement produces the following output.

REGION         High Sales
-------------- ----------
East                  YES
Central               YES
west                   NO

ARCCOS

The ARCCOS function calculates the angle value (in radians) of a specified cosine.

Return Value

NUMBER

Syntax

ARCCOS(expression)

Arguments

expression

An expression that contains the decimal value of a cosine.

Notes

Invalid Cosine Values

When you provide an ineligible value for the cosine expression (that is, a value greater than 1 or less than -1), ARCCOS returns a value of NA.

Examples

Example 6-16 Calculating the Arc of a Cosine

This example calculates the arc of a cosine that has a value of 0.54030. The statement

SHOW ARCCOS(.54030)

produces the following result.

1.00

ARCSIN

The ARCSIN function calculates the angle value (in radians) of a specified sine.

Return Value

NUMBER

Syntax

ARCSIN(expression)

Arguments

expression

An expression that contains the decimal value of a sine.

Notes

Invalid Sine Values

When you provide an ineligible value for the sine expression (that is, a value greater than 1 or less than -1), ARCSIN returns a value of NA.

Examples

Example 6-17 Calculating the Arc of a Sine

This example calculates the arc of a sine that has a value of 0.84147. The statement

SHOW ARCSIN(.84147)

produces the following result.

1.00

ARCTAN

The ARCTAN function calculates the angle value (in radians) of a specified tangent.

To retrieve a full-range (0 - 2 pi) numeric value indicating the arc tangent of a given ratio, use ARCTAN2.

Return Value

NUMBER

Syntax

ARCTAN(expression)

Arguments

expression

An expression that contains the decimal value of a tangent.

Examples

Example 6-18 Calculating the Arc of a Tangent

This example calculates the arc of a tangent that has a value of 1.56. The statement

SHOW ARCTAN(1.56)

produces the following result.

1.00

ARCTAN2

The ARCTAN2 function returns a full-range (0 - 2 pi) numeric value indicating the arc tangent of a given ratio. The function returns values in the range of -pi to pi, depending on the signs of the arguments. The values are expressed in radians.

To calculate the angle value (in radians) of a specified tangent that is not a ratio, use ARCTAN.

Return Value

NUMBER

Syntax

ARCTAN2 (n / m)

Arguments

n

A numeric expression that specifies one component of the ratio. The argument n can be in an unbounded range.

m

A numeric expression that specifies the other component of the ratio.

Examples

Example 6-19 Finding the Arc Tanget

The following example returns the arc tangent of.3 and.2.

SHOW ARCTAN2(.3/.2) 

.982793723

ARG

Within an OLAP DML program, the ARG function lets you reference arguments passed to a program. The function returns one argument as a text value.

Note:

Typically users use an ARGUMENT statement to define arguments in a program, thereby negating the need for using the ARG function to reference arguments passed to the program. For more information on how to use ARGUMENT to define arguments that are passed to a program, see "Declaring Arguments that Are Passed Into a Program" .

Return Value

TEXT

Syntax

ARG(n)

Arguments

n

The number by position of the argument whose value you want to reference. ARG(1) returns the first argument to the program, ARG(2) returns the second argument, and so forth. When the program is called with fewer than n arguments, ARG returns a null value. ARG also returns a null value when n is zero or negative.

Examples

Example 6-20 Assigning Arguments

Suppose you have a program that produces a sales report. You want to be able to produce this report for any two periods of months, so you do not want to limit the month dimension to any particular month in the program. Instead, you use ARG functions in the LIMIT command so that the starting and ending months for the two periods can be supplied as arguments when the program is run.

Notice the UPCASE function preceding the ARG functions. UPCASE allows the arguments to be specified in upper- or lowercase, even though dimension values in the analytic workspace are in uppercase. A prefixed & (ampersand) would have a similar effect since it tells Oracle OLAP to substitute the values of ARG before the LIMIT command is executed -- in this case, a value of the month dimension. However, an & (ampersand) has the disadvantage of preventing compilation of program lines in which it appears, and slower execution results.

DEFINE salesrpt PROGRAM
PROGRAM
PUSH month product district
TRAP ON cleanup 
LIMIT month TO UPCASE(ARG(1)) TO UPCASE(ARG(2))
LIMIT product TO 'CANOES'
LIMIT district TO all
REPORT grandtotals DOWN district sales
LIMIT month TO UPCASE(ARG(3)) TO UPCASE(ARG(4))
REPORT grandtotals DOWN district sales
cleanup:
POP month product district
END

To run the program, you specify the program name (salesrpt) followed by two sets of months to mark the beginning and the end of the two periods of sales to be reported. Then, when the LIMIT MONTH statements are executed, Oracle OLAP passes the months specified on the command line as return values for ARG(1), ARG(2), ARG(3), and ARG(4) in the LIMIT commands.

salesrpt 'Jan95' 'Mar95' 'Jan96' 'Mar96'

This statement produces the following output.

PRODUCT: Canoes
              ------------SALES--------------
              ------------MONTH--------------
DISTRICT        Jan95       Feb95     Mar95
---------------------------------------------
Boston        66,013.92  76,083.84  91,748.16
Atlanta       49,462.88  54,209.74  67,764.20
Chicago       45,277.56  50,595.75  63,576.53
Dallas        33,292.32  37,471.29  43,970.59
Denver        45,467.80  51,737.01  58,437.11
Seattle       64,111.50  71,899.23  83,943.86
             ----------  ---------  ---------
             303,625.98 341,996.86 409,440.44
             ========== ========== ==========
PRODUCT: Canoes
             ------------SALES---------------
             ------------MONTH---------------
DISTRICT        Jan96      Feb96       Mar96
---------------------------------------------
Boston        70,489.44  82,237.68  97,622.28
Atlanta       56,271.40  61,828.33  77,217.62
Chicago       48,661.74  54,424.94  68,815.71
Dallas        35,244.72  40,218.43  46,810.68
Denver        44,456.41  50,623.19  57,013.01
Seattle       67,085.12  74,834.29  87,820.04
             ----------  ---------  ---------
             322,208.83 364,166.86 435,299.35
             ========== ========== ========== 

ARGCOUNT

Within an OLAP DML program, the ARGCOUNT function returns the number of arguments that were specified when the current program was invoked.

Return Value

INTEGER

Syntax

ARGCOUNT

Examples

Example 6-21 Checking the Number of Arguments

In the following example, the program, a user-defined function, verifies that three arguments are passed. When the number of arguments passed is not equal to 3, the program terminates with -1 as a return value.

DEFINE threearg PROGRAM INTEGER
LD User-defined function expecting three arguments
PROGRAM
ARGUMENT division TEXT
ARGUMENT product TEXT
ARGUMENT month MONTH
IF ARGCOUNT NE 3
   THEN RETURN -1
    ELSE
     DO
     ...

ARGFR

Within an OLAP DML program, the ARGFR function lets you reference the arguments that are passed to a program. The function returns a group of one or more arguments, beginning with the specified argument number, as a single text value. You can use ARGFR only within a program that is invoked as a command, not as a user-defined function or with a CALL statement.

Note:

Typically, users use an ARGUMENT statement to define arguments in a program, thereby negating the need for using the ARGFR function to reference arguments passed to the program. For more information on how to use ARGUMENT to define arguments that are passed to a program, see "Declaring Arguments that Are Passed Into a Program" .

Return Value

TEXT

Syntax

ARGFR(n)

Arguments

n

The number by position of the first argument in the group of arguments you want to reference. ARGFR(1) returns the first argument and all subsequent arguments, ARGFR(2) returns the second argument and all subsequent arguments, and so forth. When there are fewer than n arguments, ARGFR returns a null value. ARGFR also returns a null value when n is 0 (zero) or negative.

Examples

Example 6-22 Passing Arguments Using ARG and ARGFR

Suppose you have a program that produces a sales report. You want to be able to produce this report for any product and any period of months, so you do not want to limit the product and month dimensions to specific values in the program. Instead, you can use the LIMIT command using ARG for the product argument and an ARGFR function for the month argument. This way, these items can be specified when the program is run.

When ARGFR is included in the LIMIT command preceded by an ampersand (&), Oracle OLAP substitutes the values of &ARGFR before the command is executed and, consequently, treats the whole argument as a phrase of the LIMIT command. The salesrprt program has a LIMIT command that includes &ARGFR.

DEFINE salesrpt PROGRAM
PROGRAM
PUSH product month district
TRAP ON cleanup
LIMIT product TO UPCASE(ARG(1))
LIMIT month TO &ARGFR(2)
LIMIT district TO ALL
REPORT grandtotals DOWN district sales
cleanup:
POP product month district
END

The command line for the salesrpt program must include two or more arguments. The first argument is the product for the report, and the second and subsequent arguments are the months. In the LIMIT month statement, the &ARGFR(2) function returns the months that were specified as arguments on the command line.

The following statement executes the salesrpt program, specifying Jan96, Feb96, Mar96, and Apr96 for the values of month.

salesrpt 'Canoes' 'Jan96' TO 'Apr96'

The statement produces the following output.

PRODUCT: CANOES
        -------------------SALES------------------
        -------------------MONTH------------------
DISTRICT    Jan96     Feb96      Mar96     Apr96   
------- ---------- ---------- ---------- --------- 
Boston  70,489.44  82,237.68  97,622.28 134,265.60 
Atlanta 56,271.40  61,828.33  77,217.62 109,253.38 
Chicago 48,661.74  54,424.94  68,815.71  93,045.46 
Dallas  35,244.72  40,218.43  46,810.68  64,031.28 
Denver  44,456.41  50,623.19  57,013.01  78,038.13 
Seattle 67,085.12  74,834.29  87,820.04 119,858.56 
       ---------- ---------- ---------- ---------- 
       322,208.83 364,166.86 435,299.34 598,492.41 
       ========== ========== ========== ========== 

The following statement specifies the first three months of 1996.

salesrpt 'Tents' quarter 'Q1.96'

The statement produces the following output.

PRODUCT: TENTS
               -------------SALES-------------
               -------------MONTH-------------
DISTRICT          Jan96     Feb96      Mar96
-------------- ---------- ---------- ---------
Boston         50,808.96  34,641.59  45,742.21
Atlanta        46,174.92  50,553.52  58,787.82
Chicago        31,279.78  31,492.35  42,439.52
Dallas         50,974.46  53,702.75  71,998.57
Denver         35,582.82  32,984.10  44,421.14
Seattle        45,678.41  43,094.80  54,164.06
              ----------  ---------- ---------
              260,499.35 246,469.11 317,553.32
              ========== ========== ========== 

ARGS

Within an OLAP DML program, the ARGS function lets you reference the arguments that are passed to a program. The function returns all the arguments as a single text value. You can use the ARGS function only within a program that is be invoked as a command, not as a user-defined function or with a CALL statement.

Note:

Typically, programmers use an ARGUMENT statement to define arguments in a program, thereby negating the need for using the ARGS function to reference arguments passed to the program. For more information on how to use ARGUMENT to define arguments that are passed to a program, see "Declaring Arguments that Are Passed Into a Program" .

Return Value

TEXT

When no arguments have been specified for the program, ARGS returns a null value

Syntax

ARGS

Examples

Example 6-23 Passing Arguments Using ARGS

Assume you have a program that produces a simple sales report. You want to be able to produce this report for any month, so you do not want to limit the month dimension to any fixed month in the program. You can use the ARGS function in your LIMIT command so that the months for the report can be supplied as an argument when the program is run.

When ARGS is included in the LIMIT command preceded by an ampersand (&), Oracle OLAP substitutes the values of &ARGS before the command is executed and, consequently, treats the whole argument as a phrase of the LIMIT command. The salesreport program has a LIMIT command that includes &ARGS.

DEFINE salesrpt PROGRAM
PROGRAM
PUSH month product district
TRAP ON cleanup 
LIMIT month TO &ARGS
LIMIT product TO 'CANOES'
LIMIT district TO ALL
REPORT grandtotals DOWN district sales
cleanup:
POP month product district
END

When you execute the following statement, the LIMIT command uses the values Jan96 and Feb96 for the month dimension.

salesrpt 'Jan96' 'Feb96'

The statement produces the following output.

PRODUCT: CANOES
       --------SALES--------
       --------MONTH--------
DISTRICT       Jan96      Feb96
----------------------------------
Boston       70,489.44  82,237.68
Atlanta      56,271.40  61,828.33
Chicago      48,661.74  54,424.94
Dallas       35,244.72  40,218.43
Denver       44,456.41  50,623.19
Seattle      67,085.12  74,834.29
         ---------- ---------- --
            322,208.83 364,166.86
         ========== ========== == 

ASCII

The ASCII function returns the decimal representation of the first character of an expression.

Return Value

INTEGER

Syntax

ASCII (text-exp)

Arguments

text-exp

A text expression.

Notes

Returning EBCDIC Values

When your database character set is 7-bit ASCII, then this function returns an ASCII value. When your database character set is EBCDIC Code, then this function returns an EBCDIC value. There is no corresponding EBCDIC character function

Examples

Example 6-24 Finding the ASCII Decimal Equivalent of a Character

The following example returns the ASCII decimal equivalent of the letter "Q".

SHOW ASCII('Q') 
81

ASCISSTR

The ASCIISTR function takes a string in any character set and returns an ASCII version of that string.

Returns

NTEXT

Syntax

ASCIISTR(text-exp)

Arguments

text-exp

A text expression.

Notes

How ASCIISTR Converts Non-ASCII Characters

The ASCIISTR function converts non-ASCII characters to \xxxx, where xxxx represents a UTF-16 code unit.

See:

Oracle Database Globalization Support Guide for information on Unicode character sets and character semantics.

AVERAGE

The AVERAGE function calculates the average of the values of an expression.

Return Value

DECIMAL

Syntax

AVERAGE(expression [CACHE] [dimension ...] )

Arguments

expression

The expression whose values are to be averaged.

CACHE

Specifies slightly different internal behavior. Specify this keyword only when the original performance is extremely slow.

dimension

The name of a dimension of the result; or, the name of a relation between one dimension of expression and another dimension that you want as a dimension of the result.

By default, AVERAGE returns a single value. When you indicate one or more dimensions for the result, AVERAGE calculates values along the dimensions that are specified and returns an array of values. Each dimension must be either a dimension of expression or related to one of its dimensions.

Tip:

When you specify a dimension that is not an actual dimension of expression, but, instead, is dimension that is related to a dimension of expression and when there are multiple relations between the two dimensions, Oracle OLAP uses the default relation between the dimensions to perform the calculation. (See the RELATION command for more information on default relations.) When you do not want Oracle OLAP to use this default relation, specify the related dimension by specifying the name of a specify relation.

Notes

NA Values and AVERAGE

AVERAGE is affected by the NASKIP option in the same manner as other aggregate functions. When NASKIP is set to YES (the default), AVERAGE ignores NA values and returns the average of the values that are not NA. When NASKIP is set to NO, AVERAGE returns NA when any value of the expression is NA. When all the values of the expression are NA, AVERAGE returns NA for either setting of NASKIP.

Averaging Over a Dimension of Type DAY, WEEK, MONTH, QUARTER, or YEAR

When expression is dimensioned by a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, you can specify any other dimension that has one of these types as a related dimension. Oracle OLAP uses the implicit relation between the two dimensions. To control the mapping of one of these types of dimensions to another (for example, from weeks to months), you can define an explicit relation between the two dimensions and specify the name of the relation as the dimension argument to the AVERAGE function.

For each time period in the related dimension, Oracle OLAP averages the data for all the source time periods that end in the target time period. This method is used regardless of which dimension has the more aggregate time periods. To control the way in which data is aggregated or allocated between the periods of two dimensions, you can use the TCONVERT function.

Examples

Example 6-25 Calculating Average Monthly Sales

This example shows how to calculate the average monthly sales of sportswear for each sales district.

LIMIT product TO 'SPORTSWEAR'
REPORT W 14 HEADING 'Average Sales' AVERAGE(sales district)

The preceding statements produce the following output.

DISTRICT    Average Sales
----------- --------------
Boston      69,150.41
Atlanta    151,192.36
Chicago     95,692.99
Dallas     162,242.89
Denver      88,892.72
Seattle     54,092.32

You might also want to see the average monthly sales for each region. Since the region dimension is related to the district dimension, you can specify region instead of district as a dimension for the results of AVERAGE.


AW function

The AW function returns information about currently attached workspaces.

Return Value

The return value depends on the keyword you specify, as described in Table 6-2, "Keywords for AW Function".

Syntax

AW(keyword [workspace])

Arguments

keyword

Indicates the specific information you want. The keywords that you can use with the AW function are listed in Table 6-2, "Keywords for AW Function" with the data type of the value they return and the meaning of the information.

Table 6-2 Keywords for AW Function

Keyword Data Type Information Returned

ACQUIRED

TEXT

When an analytic workspace is attached in multiwriter mode, returns the names of any acquired variables and dimensions in the analytic workspace

AGGMAP

TEXT

A list of all aggmap objects in the workspace. When there are several, Oracle OLAP returns a multiline text value with each object name on a separate line.

ALIASLIST

TEXT

A list of currently assigned aliases for the workspace. When there are several, Oracle OLAP returns a multiline text value with each alias on a separate line.

ATTACHED

BOOLEAN

Indicates whether the specified workspace is attached. The workspace argument is required.

CHANGED

BOOLEAN

When you have read/write access to the workspace, indicates whether you have made changes since the last time the workspace was updated. When you have read-only access to the workspace, indicates whether another user has updated the workspace and committed the changes since you attached it.

COMPOSITE

TEXT

A list of all named composite objects in the specified workspace.

DATE

DATE

The date of your most recent update in the current session. When you have not updated in the current session, it returns the date of the most recent commit before you attached the workspace. When you have attached a shared workspace as read-only, DATE does not take into account any updates or commits that have occurred since you attached the workspace.

DIMENSION

TEXT

A list of all the dimensions defined in the workspace. When there are several dimensions, Oracle OLAP returns a multiline text value with each dimension name on a separate line.

EXISTS

BOOLEAN

Indicates whether the specified analytic workspace has been defined in the Oracle Database.

FORMULA

TEXT

A list of all the formulas defined in the workspace. When there are several formulas, Oracle OLAP returns a multiline text value with each formula name on a separate line.

FROZEN

Boolean

TRUE if the specified analytic workspace is currently frozen, or FALSE if it is not.

FULLNAME

TEXT

The full name of the specified workspace. The full name includes the schema that contains the workspace.

ISUPDATED

TEXT

When the specified analytic workspace is not attached in multiwriter mode, returns TRUE when the workspace is updated but not committed. When he specified analytic workspace is attached in multiwriter mode, returns TRUE when at least one variable or dimension belonging to the workspace is updated but not committed.

LIST

TEXT

A list of all currently attached workspaces. Each line of the multiline text value contains the name of an analytic workspace.

LISTNAMES

TEXT

A list of all the objects defined in the workspace. Each line of the multiline text value contains the name of an analytic workspace object.

MODEL

TEXT

A list of all the models defined in the workspace. When there are several models, Oracle OLAP returns a multiline text value with each model name on a separate line.

MULTI

TEXT

Indicates if you have multi-writer access to the analytic workspace.

NAME

TEXT

The name of the current workspace.

OPTION

TEXT

A list of all the Oracle OLAP options defined in the EXPRESS workspace. When the workspace is not EXPRESS, AW(OPTION) returns NA, because options are defined only in the EXPRESS workspace. For the EXPRESS workspace, AW(OPTION) returns a multiline text value with each option name on a separate line.

PAGESIZE

INTEGER

The size of the page, in bytes.

PROGRAM

TEXT

A list of all the programs defined in the workspace. When there are several programs, Oracle OLAP returns a multiline text value with each program name on a separate line.

READERS

INTEGER

The total number of current users of the Database who have read-only access.

RELATION

TEXT

A list of all the relations defined in the workspace. When there are several relations, Oracle OLAP returns a multiline text value with each relation name on a separate line

RO

TEXT

Indicates whether you have read-only access to the workspace.

RW

TEXT

Indicates whether you have read/write access to the workspace.

SEGMENTSIZE

DECIMAL

The current maximum segment size for the workspace. It is the most recent value specified using an AW SEGMENTSIZE statement.

SHARED

BOOLEAN

Indicates whether the workspace is being shared by other users.

TIME

ID

The time of your most recent update in the current session. When you have not updated in the current session, it returns the time of the most recent commit before you attached the workspace. When you have attached a shared workspace as read-only, TIME does not take into account any updates or commits that have occurred since you attached the workspace.

VALUSET

TEXT

A list of all the valuesets that are defined in the workspace. When there are several valuesets, Oracle OLAP returns a multiline text value with each valueset name on a separate line.

VARIABLE

TEXT

A list of all the variables defined in the workspace. When there are several variables, Oracle OLAP returns a multiline text value with each variable name on a separate line.

WORKSHEET

TEXT

A list of all the worksheet objects defined in the workspace. When there are several worksheets, Oracle OLAP returns a multiline text value with each worksheet name on a separate line. This keyword is not available in the Oracle10i release and later.

WRITERS

INTEGER

The number of current users of the Database who have write access.


workspace

A text expression that indicates the name of the workspace for which you want information. When you do not specify this argument, the AW function ordinarily returns information about the current workspace. The ATTACHED, LIST, and NAME keywords are exceptions to this rule.

Notes

Analytic Workspace Status Information

You can use the SHARED, CHANGED, RO, and RW keywords to get information about the current status of a shared workspace. You can check if SHARED, RO, and CHANGED are TRUE to find out if another user has updated an analytic workspace since you attached it.

Examples

Example 6-26 Ascertaining the Active Workspace

The following program line checks which workspace is currently active so the program can choose the appropriate data to report. With this method, you can use the same report program in several workspaces, each containing different data.

REPORT IF AW(NAME) EQ 'mysales' THEN mysales ELSE gensales 

BACK

The BACK function returns the names of all currently executing programs, listed one name on each line in a multiline text value. When multiple programs are executing, one program has called another in a series of nested executions.

The first name in the return value is that of the program containing the call to BACK. The last name is that of the initial program, which made the first call to another program.

BACK can only be used in a program.

Return Value

TEXT

Syntax

BACK

Examples

Example 6-27 Debugging a Program Using the BACK Function

The following example uses three programs. program1 calls program2, and program2 calls program3.

DEFINE program1 PROGRAM
PROGRAM
SHOW 'This is program number 1'
CALL program2
END
DEFINE program2 PROGRAM
PROGRAM
SHOW 'This is program number 2'
CALL program3
END
DEFINE program3 PROGRAM
PROGRAM
SHOW 'This is program number 3'
SHOW 'These programs are currently executing:'
SHOW BACK
END

Executing program1 produces the following output.

This is program number 1
This is program number 2
This is program number 3
These programs are currently executing:
PROGRAM3
PROGRAM2
PROGRAM1 

BASEDIM

The BASEDIM function loops over a concat dimension and returns the name of the dimension from which the current value of a concat dimension comes.

Return Value

TEXT

Syntax

BASEDIM(concatdim [LEAF])

Arguments

concatdim

Specifies the concat dimension for which you want the names of the base or component dimensions. The data type of the values returned is TEXT.

LEAF

The LEAF keyword causes BASEDIM to return the names of the component dimensions of the concatdim dimension. The base dimensions of a concat dimension are the simple, conjoint, or other concat dimensions that you specify with the basedimlist argument when you define the concat. Simple dimensions and conjoint dimensions are the bottom-level components, or leaves, of a concat dimension.

When you specify a concat dimension as a base dimension when defining a concat, then the base dimensions of that inner concat are component dimensions of the outer concat. Using the LEAF keyword results in BASEDIM returning the names of the component simple and conjoint dimensions of the inner concat dimension.

When the base dimensions are all simple dimensions or conjoint dimensions, then the base dimensions are the bottom-level components and therefore BASEDIM returns the names of those dimensions whether or not you use the LEAF keyword.

When the base dimensions are all simple dimensions or conjoint dimensions, then the base dimensions are the bottom-level components and therefore BASEDIM returns the names of those dimensions whether or not you use the LEAF keyword.

Examples

Example 6-28 Returning Base Dimension Names

In this example the product dimension is limited to two values, the district dimension is limited to its first three values and the region dimension has only three values. The example defines a nonunique concat dimension with region and district as its base dimensions and then defines another nonunique concat dimension with product and the first concat dimension as its base dimensions. The example then gets the names of the base dimensions of the outer concat.

LIMIT district TO 'Boston' TO 'Chicago'
LIMIT product TO 'Tents''Canoes'
DEFINE region.district DIMENSION CONCAT(region district)
DEFINE product.region.district DIMENSION CONCAT(product region.district)
REPORT BASEDIM(product.district.region)

The preceding statements return the following.

PRODUCT
PRODUCT
REGION.DISTRICT
REGION.DISTRICT
REGION.DISTRICT
REGION.DISTRICT
REGION.DISTRICT
REGION.DISTRICT

Example 6-29 Returning Component Dimension Names

This example uses the same objects as the previous example. It gets the names of the component dimensions of the concat dimension.

REPORT BASEDIM(product.region.district LEAF)

The preceding statement returns the following.

PRODUCT
PRODUCT
REGION
REGION
REGION
DISTRICT
DISTRICT
DISTRICT

BASEVAL

The BASEVAL function loops over a concat dimension and returns the values of the base dimensions of a concat dimension. When a base dimension is a concat dimension, then the values of its base dimensions are returned, also.

Return Value

The following are the rules that determine the data types of the values returned by BASEVAL:

Syntax

BASEVAL(concatdim)

Arguments

concatdim

Specifies the concat dimension for which you want the base values. The data types of the values returned depend on the data types of the base dimensions of the concat dimension.

Examples

Example 6-30 Returning NTEXT Values

The following example creates two simple dimensions and a nonunique concat dimension, then gets the values of the concat dimension.

DEFINE textdim DIMENSION TEXT
DEFINE ntextdim DIMENSION NTEXT
MAINTAIN textdim ADD 'v1' 'v2'
MAINTAIN ntextdim ADD 'n1' 'n2'
DEFINE concatdim DIMENSION CONCAT(textdim ntextdim)
REPORT w 18 BASEVAL(concatdim)

The preceding statement returns the following.

CONCATDIM            BASEVAL(CONCATDIM)
-------------------- ------------------
<textdim: v1>        v1
<textdim: v2>        v2
<ntextdim: n1>       n1
<ntextdim: n2>       n2

The data type of the returned values is NTEXT. The BASEVAL function converted the v1 and v2 TEXT values into NTEXT values before returning them.

Example 6-31 Returning the Base Values of a Base Concat Dimension

This example defines the simple dimensions state and city and adds values to them. It defines a nonunique concat dimension, statecity, with state and city as the bases and then defines another nonunique concat dimension, geog, with region, district, and statecity as its bases. Finally, the REPORT statement returns the values returned by the BASEVAL function.

DEFINE city DIMENSION TEXT
DEFINE state DIMENSION TEXT
MAINTAIN city ADD 'Boston' 'Worcester' 'Portsmouth' 'Portland' -
  'Burlington' 'Hartford' 'New York' 'Albany'
MAINTAIN state ADD 'MA' 'NH' 'ME' 'VT' 'CT' 'NY'
DEFINE statecity DIMENSION CONCAT(state city)
DEFINE geog DIMENSION CONCAT(region district statecity)
LCOLWIDTH = 20
REPORT W 16 BASEVAL(geog)

The preceding statement returns the following.

GEOG                 BASEVAL(GEOG)
-------------------- ----------------
<region: East>       East
<region: Central>    Central
<region: West>       West
<district: Boston>   Boston
<district: Atlanta>  Atlanta
<district: Chicago>  Chicago
<district: Dallas>   Dallas
<district: Denver>   Denver
<district: Seattle>  Seattle
<state: MA>          MA
<state: NH>          NH
<state: ME>          ME
<state: VT>          VT
<state: CT>          CT
<state: NY>          NY
<city: Boston>       Boston
<city: Worcester>    Worcester
<city: Portsmouth>   Portsmouth
<city: Portland>     Portland
<city: Burlington>   Burlington
<city: Hartford>     Hartford
<city: New York>     New York
<city: Albany>       Albany

BEGINDATE

For dimensions of type DAY, WEEK, MONTH, QUARTER, or YEAR, the BEGINDATE function returns the first date of the first time period in dimension status for which the expression has a non-NA value. For example, assume that an expression is dimensioned by month, and that Jan97 is the first dimension value for which the expression has a non-NA value. In this case, BEGINDATE returns the date January 1, 1997.

Note:

You cannot use this function for time dimensions that are implemented as hierarchical dimensions of type TEXT.

Return Value

DATE-only or text

When all the values of the expression are NA, BEGINDATE returns NA.

Syntax

BEGINDATE(expression)

Arguments

expression

The expression must have exactly one dimension that has a type of DAY, WEEK, MONTH, QUARTER, or YEAR.

Examples

Example 6-32 Finding the Beginning Date

The following statements limit the values in the month, product, and district dimensions, then send the first date for which the units variable contains a non-NA value for unit sales of tents in the Chicago district to the current outfile.

LIMIT month TO ALL
LIMIT product TO 'TENTS'
LIMIT district TO 'CHICAGO'
SHOW BEGINDATE(units)

These statements produce the following output.

01JAN95

BIN_TO_NUM

The BIN_TO_NUM function converts a bit vector to its equivalent number.

BIN_TO_NUM is useful in data warehousing applications for selecting groups of interest using grouping sets.

Return Values

NUMBER

Syntax

BIN_TO_NUM(expression [, expression ]... )

Arguments

expression

An expression that evaluates to either 0 (zero) or 1 (one) which is the value of a bit in the bit vector.

Examples

Example 6-33 Converting Bit Vectors to a Number

SHOW BIN_TO_NUM(1,0,1,0)
10.00

BITAND

The BITAND function computes a logical AND operation on the bits of two nonnegative values. This function is commonly used with the DECODE function.

An AND operation compares two bit values. When the values are the same, the operator returns 1. When the values are different, the operator returns 0. Only significant bits are compared. For example, an AND operation on the integers 5 (binary 101) and 1 (binary 001 or 1) compares only the rightmost bit, and results in a value of 1 (binary 1).

Return Value

INTEGER

Syntax

BITAND (argument1 , argument2)

Arguments

argument1

A nonnegative INTEGER expression.

argument2

A nonnegative INTEGER expression.

Examples

See Example 6-65, "DECODE with BITAND".


BLANKSTRIP

The BLANKSTRIP function removes leading or trailing blank spaces from text values. BLANKSTRIP is useful for such purposes as removing unwanted blank spaces from imported fixed-length fields.

Return Value

TEXT or NTEXT

Syntax

BLANKSTRIP(text-expression [TRAILING|LEADING|BOTH])

Arguments

text-expression

A text expression from which to remove blank spaces. When you specify a TEXT expression, the return value is TEXT. When you specify an NTEXT expression, the return value is NTEXT.

TRAILING

Removes blank spaces at the end of the text.

LEADING

Removes blank spaces at the beginning of the text.

BOTH

Removes both leading and trailing spaces.

Examples

Example 6-34 Stripping Leading and Trailing Blanks

In this example, we remove both leading and trailing blank spaces from the field prodlabel in an imported worksheet and store the results in a variable called product.

product = BLANKSTRIP(prodlabel, BOTH) 

CALLTYPE

Within an OLAP DML program, the CALLTYPE function indicates whether a program was invoked as a function, as a command, by using a CALL statement, or triggered by the execution of an OLAP DML statement.

Return Value

TEXT

The return value of CALLTYPE is:

Syntax

CALLTYPE

Examples

Example 6-35 Determining the Calling Method

This sample program, called myprog, demonstrates how CALLTYPE returns different values depending on how the program is invoked.

DEFINE myprog PROGRAM
PROGRAM
SHOW CALLTYPE
RETURN('This is the return value')
END

The following statements invoke myprog: 1) as command; 2) with a CALL statement; 3) as a function.

myprog
CALL myprog
SHOW myprog

The three statements send the following output to the current outfile. Note that the return value of myprog appears only when the program is called as a function.

COMMAND
CALL
FUNCTION
This is the return value

CATEGORIZE

The CATEGORIZE function groups the values of a numeric expression into categories. You define the categories by specifying a series of increasing numeric values. The result that CATEGORIZE returns is dimensioned by all the dimensions of expression. For each cell in expression, CATEGORIZE returns one of the following: the category in which the number falls, zero (0) for a value below the range of the first category, minus one (-1) for a value above the range of the last category, or NA for an NA value.

Return Value

DECIMAL

Syntax

CATEGORIZE(expression {values|group-expression})

where values has the following syntax:

     bottom-value [next-lowest-break-valuetop-value

Arguments

expression

The numeric expression whose values are to be categorized.

bottom-value

A number that specifies the lowest number in the series and sets the bottom limit of category 1.

next-lowest-break-value

A number that specifies the beginning of the range of the next category.

top-value

A number that specifies the highest number in the series and sets the upper limit of the highest category.

group-expression

A one-dimensional numeric expression that defines the break values for the categories.

Examples

Example 6-36 Specifying Category Range Values

Assume that your analytic workspace contains the following geography and items dimensions and sales2 variable.

DEFINE geography DIMENSION TEXT 
MAINTAIN geography ADD 'g1' 'g2' 'g3'
DEFINE items DIMENSION TEXT
MAINTAIN items ADD 'Item1' 'Item2' 'Item3' 'Item4' 'Item5'
DEFINE sales2 DECIMAL <geography items>

Assume the sales2 variable has the following data values.

-------------SALES2-------------
               -----------GEOGRAPHY------------
ITEMS              g1         g2         g3
-------------- ---------- ---------- ----------
Item1               30.00      15.00      12.00
Item2               10.00      20.00      18.00
Item3               15.00      20.00      24.00
Item4               30.00      25.00      25.00
Item5                  NA       7.00      21.00

This statement reports the result of categorizing the sales2 variable.

REPORT CATEGORIZE(sales2 10 15 20 25)

The preceding statement produces the following output.

-CATEGORIZE(SALES2 10 15 20 25)-
               -----------GEOGRAPHY------------
ITEMS              g1         g2         g3
-------------- ---------- ---------- ----------
Item1               -1.00       2.00       1.00
Item2                1.00       3.00       2.00
Item3                2.00       3.00       3.00
Item4               -1.00       3.00       3.00
Item5                  NA       0.00       3.00

Example 6-37 Specifying a Group-Expression

These statements define a groups dimension and a groupval variable.

DEFINE groups DIMENSION TEXT
MAINTAIN groups ADD 'Grp1' 'Grp2' 'Grp3' 'Grp4'
DEFINE groupvals DECIMAL <groups>
groupvals(groups 'Grp1') = 10
groupvals(groups 'Grp2') = 15
groupvals(groups 'Grp3') = 20
groupvals(groups 'Grp4') = 25

This statement reports the result of calling the CATEGORIZE function with the sales variable as the expression argument and the groupvals variable as the group-expression argument of the call.

REPORT CATEGORIZE(sales, groupvals)

The preceding statement produces the same output as the statement in the "Specifying Category Range Values" .


CEIL

The CEIL function returns the smallest whole number greater than or equal to a specified number.

Return Value

NUMBER

Syntax

CEIL(n)

Arguments

n

A number (NUMBER data type) that you specify.

Examples

Example 6-38 Displaying the Smallest Integer Greater Than or Equal to a Number

The following statements show results returned by CEIL.

  • The statement

    SHOW CEIL(15.7)
    

    produces the following result

    16
    
  • The statement

    SHOW CEIL(-6.457)
    

    produces the following result.

    -6
    

CHANGEBYTES

The CHANGEBYTES function changes one or more occurrences of a specified string in a text expression to another string.

Return Value

TEXT

Syntax

CHANGEBYTES(text-expression oldtext newtext [number])

Arguments

text-expression

A TEXT expression in which bytes are to be changed. When text-expression is a multiline TEXT expression, CHANGEBYTES preserves the line breaks in the returned value.

oldtext

A TEXT expression that contains one or more bytes that to be changed.

newtext

A TEXT expression that contains one or more bytes that to replace oldtext.

number

An INTEGER that represents the number of times oldtext should be replaced with newtext when oldtext appears more than once in text-expression. The default is to change all occurrences of oldtext.

Examples

Example 6-39 Changing Text Values Using Bytes

This example shows how to change one instance of a portion of a text value.

The statement

SHOW CHANGEBYTES('Hello there, Joe\nHello there, Jane', 
   'there', - 'to you', 1)

produces the following output.

Hello to you, Joe
Hello there, Jane 

CHANGECHARS

The CHANGECHARS function changes one or more occurrences of a specified string in a text expression to another string.

Return Value

When all arguments are TEXT values, the return value is TEXT. When all arguments are NTEXT values, the return value is NTEXT. When the arguments include both TEXT and NTEXT values, the function converts all TEXT values to NTEXT before performing the function operation, and the return value is NTEXT.

Syntax

CHANGECHARS(text-expression oldtext newtext [number] [UPCASE])

Arguments

text-expression

The TEXT or NTEXT expression in which characters are to be changed. When text-expression is a multiline text value, CHANGECHARS preserves the line breaks in the returned value.

oldtext

A TEXT or NTEXT expression that contains one or more characters to be changed.

newtext

A TEXT or NTEXT expression that contains one or more characters to replace oldtext.

number

An INTEGER that represents the number of times oldtext should be replaced with newtext when oldtext appears more than once in text-expression. The default is to change all occurrences of oldtext.

UPCASE

Specifies that CHANGECHARS should uppercase text-expression and oldtext before trying to find a match. CHANGECHARS does not uppercase the return value.

Examples

Example 6-40 Changing the Values of Text Characters

This example shows how to change one instance of a portion of a text value.

The statement

SHOW CHANGECHARS('Hello there, Joe\nHello there, Jane', 
   'there', - 'to you', 1)

produces the following output.

Hello to you, Joe
Hello there, Jane 

CHANGEDRELATIONS

For a given variable and aggmap object, the CHANGEDRELATIONS function determines if there are any changes in the aggmap and the relations in the aggmap since the last time the variable was aggregated.

Return Value

BOOLEAN.

TRUE when changes have occurred, FALSE when they have not, or NA when the function cannot determine if changes have occurred.

Syntax

CHANGEDRELATIONS( variable [ [(partition ...) ] aggmap] )

Arguments

variable

The name of the variable whose aggmap object you want to check for changes.

partition

The name of one or more partitions, separated by commas, whose aggmap you want to check for changes.

aggmap

The name of the aggmap object you want to check for changes. When you do not specify a value for aggmap, the function uses the aggmap specified in the $AGGMAP property for variable, if any.


CHANGEDVALUES

The CHANGEDVALUES function identifies if any value in a variable has changed (or the number of values that have changed) since the last time a variable was aggregated.

Return Value

BOOLEAN unless you specify NUMBER for returntype.

When the function returns a BOOLEAN value, that value is TRUE when any value has changed since the variable was last aggregated, FALSE when no values have changed, or NA when the function cannot determine if any values have changed or not.

When the function returns a NUMBER value, that value is the number of values that have changed since the variable was last aggregated.

Syntax

CHANGEDVALUES ( variable [(partition ...)] [returntype] )

Arguments

variable

The name of the variable to check for changed values.

partition

The name of one or more partitions, separated by commas, to check for changed values.

returntype

NUMBER when you want the function to return a numeric value that is the number of values that have changed. When you want the function to return whether or not any value has changed since the last aggregation, specify BOOLEAN or leave this argument empty as BOOLEAN is the default value for returntype.


CHARLIST

The CHARLIST function transforms an expression into a multiline text value with a separate line for each value of the original expression.

Return Value

NTEXT when the expression is NTEXT; otherwise, TEXT.

Syntax

CHARLIST(expression [dimensions])

Arguments

expression

The expression to be transformed into a multiline text value. When the expression has a data type other than TEXT or NTEXT, CHARLIST automatically converts the expression to TEXT.

dimensions

The dimensions of the return value. When you do not specify a dimension, CHARLIST returns a single value. When you provide one or more dimensions for the return value, CHARLIST returns a multiline text value for each value in the current status list of the specified dimension. Each dimension must be an actual dimension of the expression; it cannot be a related or base dimension.

Examples

Example 6-41 Deleting Workspace Objects

You can use CHARLIST with the NAME dimension to create lists of workspace objects. Suppose you want to delete all objects of a certain type in your workspace, for example, all worksheets. You can use CHARLIST and an ampersand (&) to do this.

LIMIT NAME TO OBJ(TYPE) EQ 'WORKSHEET'
DELETE &CHARLIST(NAME)

Example 6-42 Creating a List of Top Sales People

Assume you have stored the names of the sales people who sold the most for each product in product.memo, a text variable with the dimensions of product and . You then want to create a list of top sales people broken out by product. To do this, you can created a variable dimensioned by product and then use CHARLIST with the product to create a separate list of all of the top sales people for each product.

DEFINE topsales VARIABLE TEXT <product>
topsales = CHARLIST(product.memo product) 

CHARTOROWID

The CHARTOROWID function converts a value from a text data type to ROWID data type.

Return Value

ROWID

Syntax

CHARTOROWID(char)

Arguments

char

A text expression to convert.

Examples

Example 6-43 Converting a Value from Text to a Rowid

Assume that your analytic workspace contains the erowid dimension with the following definition.

DEFINE erowid DIMENSION ROWID

As the following code illustrates, you can add text values to it using the CHARTOROWID function.

MAINTAIN erowid ADD CHARTOROWID('AAAFd1AAFAAAABSAA/')
REPORT erowid
 
EROWID
------------------------------
AAAFd1AAFAAAABSAA/

CHGDIMS

The CHGDIMS function changes the dimensionality of an expression or changes the dimension status during the evaluation of expression.

Return Value

Data type of the original expression.

Syntax

CHGDIMS (expression, limit-type)

where limit-type is one of the following:

[CACHE] LIMITSAVE valueset-list

[CACHE] LIMIT valueset-list

TO dimension-list

ADD dimension-list

Arguments

expression

The expression you want to modify.

CACHE

Specifies that Oracle OLAP caches the result of the limit and saves it for use in subsequent executions of CHGDIMS until the OLAP DML statement that called CHGDIMS finishes execution.

LIMITSAVE

Specifies that Oracle OLAP sets the value of dimension status for expression to the position before the CHGDIMS command executed (that is, specifying LIMITSAVE does not change the current dimension status value). For example, you specify CHGDIMS with LIMITSAVE if expression is the LAG function so that the lag is from the current value; or if you are coding CHGDIMS inside of an outer loop, like a SQL SELECT statement, and you want to keep the dimension status value set by the outer loop.

LIMIT

Specifies the Oracle OLAP sets the value of dimension status for expression to the first position in the new status before evaluating expression in much the same way as if a LIMIT TO command was issued just before evaluating expression.

valueset-list

The name of a valueset or the LIMIT function.

TO dimension-list

Specifies that Oracle OLAP evaluate expression as though the dimensions of expression are the dimensions specified by dimension-list.

ADD dimension-list

Specifies that Oracle OLAP evaluateexpression as though the dimensions of expression are the dimensions of expression plus the dimensions specified by dimension-list

Examples

Assume that you have the following objects in your analytic workspace.

DEFINE PRODUCT DIMENSION TEXT
DEFINE GEOG DIMENSION TEXT
DEFINE SALES VARIABLE INTEGER <PRODUCT GEOG>
 

Assume, also, that the sales variable has the following values.

-------------------SALES-------------------
               ------------------PRODUCT------------------
GEOG            Trousers    Skirts    Dresses     Shoes
-------------- ---------- ---------- ---------- ----------
USA                    13         20         32         18
Canada                 17         32         15         28
 

The following lines of code show how the value returned by a TOTAL(sales) expression varies depending on how you qualify that expression.

"Total over all dims with standard status
SHOW TOTAL(sales)
175
 
"Total over all dims using new status for product
SHOW CHGDIMS(TOTAL(sales) LIMIT(product TO FIRST 2)))
82
 
"Total just over product
SHOW TOTAL(CHGDIMS(sales TO product))
83

CHR

The CHR function converts an integer value (or any value that can be implicitly converted to an integer value) into a character.

Note:

Use of this function results in code that is not portable between ASCII- and EBCDIC-based architectures.

Return Value

A text value. For single-byte character sets, if number > 256, the function returns the binary equivalent of number MOD 256. For multibyte character sets, number must resolve to one entire code point. Invalid code points are not validated, and the result of specifying invalid code points is indeterminate.

Syntax

CHR(number [ USING NCHAR_CS ])

Arguments

number

An integer value, or any value that can be implicitly converted to an integer value.

USING NCHAR_CS

Specifies that the function returns the value in the national character set. When you do not specify this clause, the function returns the value in the database character set.

Examples

Example 6-44 Converting an Integer Value Into a Character

Assume that you have an ASCII-based system with the WE8ISO8859P1 database character set. In this case, the following statement returns the letter C.

SHOW CHR(67)
C 

COALESCE

The COALESCE function returns the first non-NA expression in a list of expressions, or NA when all of the expressions evaluate to NA.

Return Value

Data type of the first argument.

Syntax

COALESCE (expr [, expr]...)

Arguments

expr

An expression.

Examples

Example 6-45 Using COALESCE to Determine the Sales Price of a Product

Assume that you have defined the following objects in your analytic workspace. (Note that the sale formula uses the COALESCE function for its calculations.)

DEFINE product_id DIMENSION TEXT
DEFINE supplier_id DIMENSION TEXT
DEFINE list_price VARIABLE DECIMAL <product_id supplier_id>
DEFINE min_price VARIABLE DECIMAL <product_id supplier_id>
 
DEFINE sale FORMULA DECIMAL <Product_id supplier_id>
EQ COALESCE(0.9*list_price, min_price, 5)
 

The following code illustrates limiting supplier_id to a single value and displaying a report that shows the list price, minimum price, and sale price for the products provided by that supplier.

LIMIT supplier_id TO '102050'
REPORT DOWN product_id list_price min_price sale

               ----------SUPPLIER_ID-----------
               -------------102050-------------
PRODUCT_ID     LIST_PRICE MIN_PRICE     SALE
-------------- ---------- ---------- ----------
2382               850.00     731.00     765.00
3355                   NA         NA       5.00
1770                   NA      73.00      73.00
2378               305.00     247.00     274.50
1769                48.00         NA      43.20
1660                16.45      16.45      14.80

COLVAL

The COLVAL function returns a numeric value from a column to the left of the current column in the same row of a report. COLVAL can only be used in the ROW command and the REPORT command.

Return Value

DECIMAL when the selected column contains numeric or Boolean data; NA when the column (n) contains only a TEXT or ID value; or an error when the specified column is the current column, a column to the right of the current column, or a nonexistent column

Syntax

COLVAL(n)

Arguments

n

The number of the column in the current row whose value you want; n can be any INTEGER expression.

Use a positive number to identify an absolute column number (counting left to right from the left margin of the report). In figuring an absolute column number, you must count all columns shown in the report. For example, when you are using a REPORT command that produces a column of labels down the left side of the report, you count this column of labels as column 1.For example, COLVAL(2) identifies the second column from the left margin of the report.

Use a negative number to identify a relative column number (counting right to left from the current column). For example, COLVAL(-2) identifies the column that is two columns to the left of the current column.

Examples

Example 6-46 Performing Column Calculations in a Report

Suppose in a report you want to show actual sales and planned sales, along with the difference between the two. You can use the COLVAL function to calculate this difference.

LIMIT month TO 'Jun96'
LIMIT district TO 'Boston'
FOR product
   ROW product sales sales.plan COLVAL(2)-COLVAL(3)

These statements produce the following output.

Tents          95,120.83  80,138.18  14,982.65
Canoes        157,762.08 132,931.39  24,830.69
Racquets       97,174.44  84,758.46  12,415.98
Sportswear     79,630.20  73,568.52   6,061.68
Footwear      153,688.02 109,219.15  44,468.87

CONTEXT function

The CONTEXT function lets you obtain information about object values that are saved in a context. You must first create the context with the CONTEXT command.

Return Value

The data type of the return value of the CONTEXT function depends on the arguments you provide. When you use the CONTEXT function without supplying any arguments, it returns a multiline text value that contains the names of all the contexts in the current session.

Syntax

CONTEXT ([context-name [UPDATE|name]])

Arguments

context-name

A text expression that contains the name of the context. Using the CONTEXT function with only the context-name returns a multiline text value that contains the names of all the objects saved in that context.

UPDATE

When you specify UPDATE with the CONTEXT function, the return value is the number of times values have been saved or dropped from the context.

name

The name of an object whose value is saved in the context. When you specify name with the CONTEXT function, the return value is the saved status or value of that object.

Examples

Example 6-47 Listing Context Names

In the following statement, the CONTEXT function returns the name of the only context in the current session which is the same context used in Example 8-71, "Saving Dimension Status".

SHOW CONTEXT

The statement produces the following output.

democontext1

Example 6-48 Listing Saved Values

In the following statement, the CONTEXT function returns the values of the product dimension that are saved in the context named democontext1.

SHOW CONTEXT('democontext1' product)

The statement produces the following output.

Tents
Canoes

CONVERT

The CONVERT function converts values from one type of data to another.

Return Value

The return value depends on the value of the type argument.

Syntax

CONVERT(expressiontype [argument...])

Arguments

expression

The expression or variable to be converted.

type

The type of data to which you want to convert expression. The keywords that represent the types are described in Table 6-3, "Keywords for the type Argument of the CONVERT Function".

Table 6-3 Keywords for the type Argument of the CONVERT Function

Keyword Description

BINARY

Does not indicate conversion to a standard Oracle data type but allows additional conversion capabilities. BINARY does no conversion. The internal representation of every value, regardless of data type, is returned as a text value.

  • For TEXT data types, the result is the value itself and is, therefore, of variable length.

  • For ID and DECIMAL data types, the result is 8 bytes long; ID values is blank filled, when necessary.

  • For BOOLEAN or INTEGER, the default result is 2 or 4 bytes long respectively (see the arguments explanation for an additional argument that lets you vary the width slightly).

  • For all other data types, the result is 4 bytes long.

See "PACKED and BINARY Conversion".

BOOLEAN

Conversion to Oracle OLAP BOOLEAN data type.

BYTE

Converts a single character into an ASCII INTEGER value in the range 0 to 255. Or BYTE converts an INTEGER within this range into a character. An INTEGER outside this range is taken modulo 256 and then converted; that is, 256 is subtracted from the INTEGER until the remainder is less than 256, and that within-range remainder is then converted into a character.

DATE

Conversion to Oracle OLAP DATE data type.

DATETIME

Conversion to Oracle OLAP DATETIME data type.

DECIMAL

Conversion to Oracle OLAP DECIMAL data type.

DSINTERVAL

Conversion to Oracle OLAP DML DSINTERVAL data type.

ID

Conversion to Oracle OLAP ID data type.

INFILE

Encloses an ID, TEXT, DATE, or RELATION value within single quotes, so that it can be read with an INFILE statement. Consequently, expression must have ID, TEXT, DATE, or RELATION value values. In the case of TEXT values with no alphanumeric equivalent, INFILE converts them to the correct escape sequences.

INTEGER

Conversion to Oracle OLAP INTEGER data type.

LONGINTEGER

Conversion to Oracle OLAP LONGINTEGER data type.

NTEXT

Conversion to standard Oracle OLAP data types. Corresponds to the NCHAR and NVARCHAR2 SQL data types. An NTEXT character is encoded in UTF8 Unicode. This encoding might be different from the NCHAR character set of the database, which can be UTF16. A conversion from NTEXT to TEXT can result in data loss when the NTEXT value cannot be represented in the database character set.

NUMBER [(p,[s])]

Conversion to Oracle OLAP NUMBER data type.

PACKED

Converts a number to a decimal value and then to packed format -- a text value 8 bytes long containing 15 digits and a plus or minus sign. Fractions cannot be represented in packed numbers; therefore the conversion process rounds decimal numbers to the nearest INTEGER. See "PACKED and BINARY Conversion".

ROWID

Converts a text value to a ROWID value.

SHORTDECIMAL

Conversion to Oracle OLAP SHORTDECIMAL data type.

SHORTINTEGER

Conversion to Oracle OLAP SHORTINTEGER data type.

TEXT

Conversion to standard Oracle OLAP data types. Corresponds to CHAR and VARCHAR2 data types in SQL. A TEXT character is encoded in the database character set.

TIMESTAMP

Conversion to Oracle OLAP DML TIMESTAMP data type.

TIMESTAMP_LTZ

Conversion to Oracle OLAP DML TIMESTAMP_LTZ data type.

TIMESTAMP_TZ

Conversion to Oracle OLAP DML TIMESTAMP_TZ data type.

UROWID

Converts a text value to a UROWID value.

YMINTERVAL

Conversion to Oracle OLAP DML YMINTERVAL data type.


argument

When you specify TEXT, NTEXT, ID, DATE, or INFILE for the type, you can specify additional arguments to determine how the conversion should be done as outlined in Table 6-4, "Syntax for Specifying Conversion to TEXT, NTEXT, ID, DATE, and INFILE".

Table 6-4 Syntax for Specifying Conversion to TEXT, NTEXT, ID, DATE, and INFILE

Keyword for type argument When Converting From Syntax for All Arguments

TEXT

Any numeric

TEXT [decimal-int|DECIMALS [comma-bool|COMMAS [paren-bool|PARENS]]]

NTEXT

Any numeric

NTEXT [decimal-int|DECIMALS [comma-bool|COMMAS [paren-bool|PARENS]]]

ID

Any numeric

ID [decimal-int|DECIMALS]

TEXT, NTEXT, or ID

Any datetime

ID|TEXT|NTEXT ['date_format']

TEXT, NTEXT, or ID

DATE

ID|TEXT|NTEXT ['dateformat']

ID or TEXT for a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR with VNF

DATE

ID [dwmqy-dimension]|TEXT [dwmqy-dimension|'vnf ']

DATE

TEXT, NTEXT, or ID

DATE [date-order|dwmqy-dimname]

NTEXT

TEXT

NOXLATE

TEXT

NTEXT

NOXLATE

INFILE

 

INFILE [width-exp|LSIZE [escape-int|0]]

IBINARY with BOOLEAN or INTEGER

 

BINARY [width-exp]


decimal-int

An INTEGER expression that controls the number of decimal places to be used when converting numeric data to TEXT or ID values. When this argument is omitted, CONVERT uses the current value of the DECIMALS option (the default is 2).

comma-bool

A Boolean expression that determines whether commas are used to mark thousands and millions in the text representation of the numeric data. When the value of the expression is YES, commas are used. When this argument is omitted, CONVERT uses the current value of the COMMAS option (the default is YES).

paren-bool

A Boolean expression that determines whether negative values are enclosed in parentheses in the text representation of the numeric data. When the value of the expression is YES, parentheses are used; when the value is NO, a minus sign precedes negative values. When this argument is omitted, CONVERT uses the current value of the PARENS option (the default is NO).

date_format

A text expression that specifies the template to use when converting a datetime expression to text. The valid formats for each date field are the same as the formats that you can specify using the DATE_FORMAT command.

When you do not include the date_format argument, the format of the result is determined by the default date format for the session as described in "Default Datetime Format Template".

dateformat

A text expression that specifies the template to use when converting a DATE-only expression to text. The template can include format specifications for any of the four components of a date (day, month, year, and day of the week). Each component in the template must be preceded by a left angle bracket (<)and followed by a right angle bracket (>). You can include additional text before, after, or between the components.

The valid formats for each date component are the same as the formats allowed in the DATEFORMAT option.

In the following statement, CONVERT returns today's date as a text value that is formatted by a dateformat argument.

SHOW CONVERT(TODAY TEXT '<MM>-<DD>-<YY>')

In this example, today's date is March 31, 1998, and the SHOW statement presents it in the following format.

03-31-98

When you do not include the dateformat argument, the format of the result is determined by the current setting of the DATEFORMAT option.

dwmqy-dimension

The name of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. Oracle OLAP uses the VNF of dwmqy-dimension when converting a DATE-only value to a TEXT or an ID value. When you have not specified the VNF of dwmqy-dimension, Oracle OLAP uses its default VNF.

In the following statement, CONVERT returns today's date as a text value that is formatted by the VNF of the YEAR dimension.

show convert(today text year)

In this example, today's date is March 31, 1998, and the SHOW statement presents it in the following format.

YR98
vnf

A text template that specifies the value name format to use when converting values of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR to text. The template can include format specifications for any of the components of a time period. Time period components include all the components of a date (day, month, year, and day of the week), plus the fiscal year and period components. The template can also include the name of the DAY, WEEK, MONTH, QUARTER, or YEAR dimension as a component. Each component in the template must be preceded by a left angle bracket and followed by a right angle bracket. You can include additional text before, after, or between the components.

The vnf argument to the CONVERT function is similar to the template in a VNF command. However, a VNF command template must be designed for precise and unambiguous interpretation of input, while the vnf argument is not so constrained. Therefore, the format styles allowed in the vnf argument are more extensive than those allowed in a VNF command template.

Valid format styles for a vnf argument include all the format styles allowed in the template of a VNF command, plus all the format styles allowed in a DATEFORMAT template. DATEFORMAT provides the following format styles that are not allowed in VNF command templates but that are valid in the vnf argument to the CONVERT function:

  • Ordinal styles for the day of the month (DT and DTL)

  • First-letter style for the month (MT)

  • Styles for the day of the week (W, WT, WTXT, WTXTL, WTEXT, and WTEXTL)

Append a B code to any of these formats to indicate that you want to display the beginning day or month of the period, rather than the final day or month.

You can use any combination of VNF and DATEFORMAT format styles with for any dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. This syntax contrasts with the template in a VNF command, in which only certain format combinations are valid for each type of dimension.

In the following statement, CONVERT returns the current value of the MONTH dimension as a text value that is formatted by a vnf argument.

SHOW CONVERT(month TEXT '<MTEXTL>, <YYYY>')

In this example, the first MONTH value in status is DEC97, and the SHOW statement presents it in the following format.

December, 1997

When you do not include the vnf argument, the format of the result is determined by the VNF of the dimension whose values you are converting. When the dimension has no VNF, the result is formatted according to the default VNF for the type of dimension being converted.

date-order

A text expression that specifies how to interpret the specified text value as a DATE-only value when the order of the text value's components (month, day, and year) is ambiguous. The expression can be one of the following: 'MDY', 'DMY', 'YMD', 'YDM', 'MYD', or 'DYM'. Each letter represents a component of the date: M stands for month, D stands for day, and Y stands for year.

When you do not include the date-order or dwmqy-dimname argument, any ambiguity in the interpretation of a text expression is resolved by the current setting of the DATEORDER option. Refer to the DATEORDER option for a complete description of DATE-only values and how they are interpreted.

dwmqy-dimname

The name of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR whose VNF or default date-order determines how to interpret the specified text value as a DATE-only value when the order of the text value's components is ambiguous.

When you do not include the date-order or dwmqy-dimname argument, any ambiguity in the interpretation of a text expression is resolved by the current setting of the DATEORDER option. Refer to the DATEORDER option for a complete description of DATE-only values and how they are interpreted.

width-exp

An INTEGER expression that indicates the width of the output from CONVERT. The minimum width is 7. The default width is the current value of the LSIZE option. This argument is required when you specify the escape-int argument.

escape-int

Indicates whether escape sequences are to be used in the output. For this argument you can specify a value listed in Table 6-5, "Values for escape-int Argument of the CONVERT".

Table 6-5 Values for escape-int Argument of the CONVERT

Value Description

-1

Do not use escapes. Precede -1 with a comma (,-1) so that Oracle OLAP does not subtract 1 from a preceding WIDTH argument.

0

(Default) Use escapes for unprintable characters.

1

Use escapes for all characters.


For more information on escape sequences in the OLAP DML, see "Escape Sequences".

width-exp

An INTEGER expression that controls the width of the converted result. It can evaluate to 1, 2, or 4 bytes. The default width is 2 for BOOLEAN, or 4 for INTEGER. When an INTEGER value is too large to fit in the specified width, the result is NA. When the width is invalid or specified for some other data type, an error occurs.

NOXLATE

A keyword indicating that no character set conversion should be performed. Instead, Oracle OLAP only tags the converted value with the target data type, leaving the data as it was before the CONVERT function was called. Use this keyword only when it is necessary to store binary data in a TEXT or NTEXT variable.

Notes

INFILE Conversion

The maximum number of characters in a line is 4000. An error occurs when you try an INFILE conversion that produces a line with more than 4000 characters. This type of error can occur when the source line exceeds 99 characters and enough of them need escape sequences.

Converting DATE-only Values to Numeric Values

The result of converting a value that has the DATE-only data type to a value with any numeric data type is the sequence number that represents the date (the sequence number 1 represents January 1, 1900).

Oracle OLAP first converts the DATE-only value to an INTEGER value that is the sequence number that represents the DATE-only value. When the target data type is a numeric data type other than INTEGER, Oracle OLAP then converts that INTEGER value to the specified numeric data type.

The value 32,767 is the largest possible value for a SHORTINTEGER, and (as an INTEGER value) represents the date September 17, 1992. Therefore, CONVERT returns NA when you attempt to convert any DATE-only later than September 17, 1992 to a SHORTINTEGER value.

Converting Numeric Values to DATE-only Values

The result is the DATE-only whose sequence number matches the specified number (January 1, 1900 is represented by the sequence number 1); or NA, when the result is outside the range of valid dates. Valid dates range from January 1, 1900 (sequence number 1) to December 31, 9999 (sequence number 2,958,464).

When the numeric data type is an INTEGER data type, Oracle OLAP converts the INTEGER value directly to the DATE-only value whose sequence number matches the specified number. When the numeric data type is not INTEGER, Oracle OLAP first converts the numeric value to an INTEGER value and then converts that INTEGER value to a DATE-only value.

Converting DATE-only Dimension Values to ID Values

When the result is more than eight characters long, the result is truncated.

Converting Relation Values to INTEGER Values

The result is an INTEGER value that represents the position of the value in the relation's dimension. This behavior reflects the fact that the values of a relation are dimension values, not TEXT values.

Converting Values From One Numeric Data Type to Another

The result is the value in the specified data type; or NA when the value is outside the range of valid values for the target data type.

Thus, when you try to convert a an INTEGER value that is larger than 32,767 or smaller than -32,767 to a SHORTINTEGER value, CONVERT returns NA.

String-to-Datetime Conversion Rules

The following formatting rules apply when converting string values to datetime values:

Table 6-6 Oracle Format Matching

Original Format Element Additional Format Elements to Try instead Of the Original
'MM'

'MON' and 'MONTH'

'MON

'MONTH'

'MONTH'

'MON'

'YY'

'YYYY'

'RR'

'RRRR'


Converting Null and Blank Text Values to BYTE Values

CONVERT returns the same value for a null string ('') as it does for a blank string (' '). In both cases, you get a result of 32.

PACKED and BINARY Conversion

The PACKED and BINARY types are useful for creating binary files that contain PACKED and BINARY data. To create such a file, use FILEOPEN statement with the BINARY keyword to open the file and FILEPUT to write values to it. You can use the ROW function as an argument to the FILEPUT statement to help format the file.

Examples

Example 6-49 Converting Decimal Values to Text

This example shows how to use the JOINCHARS and CONVERT functions to combine some text with the value of the variable price for a product and month, and show the price without decimal places.

LIMIT month TO 'Jul96'
LIMIT product to 'Canoes'
SHOW JOINCHARS('Price of Canoes = $' CONVERT(price TEXT 0))
Price of Canoes = $200

Example 6-50 Converting Text Values to Escape Sequences

This example shows how to use the CONVERT function with the ESCAPEBASE option to convert a TEXT value from its default decimal escape sequences to hexadecimal escape sequences.

DEFINE textvar VARIABLE TEXT
textvar = 'testvalue'
SHOW CONVERT(textvar INFILE 9 1)
'\d116\d101\d115\d116\d118\d097\d108\d117\d101'
ESCAPEBASE = 'x'
SHOW CONVERT(textvar INFILE 9 1)
'\x74\x65\x73\x74\x76\x61\x6C\x75\x65' 

CORRELATION

The CORRELATION function returns the correlation coefficients for the pairs of data values in two expressions. A correlation coefficient indicates the strength of relationship between the data values. The closer the correlation coefficient is to positive or negative 1, the stronger the relationship is between the data values in the expressions. A correlation coefficient of 0 (zero) means no correlation and a +1 (plus one) or -1 (minus one) means a perfect correlation. A positive correlation coefficient indicates that as the data values in one expression increase (or decrease), the data values in the other expression also increase (or decrease). A negative correlation coefficient indicates that as the data values in one expression increase, the data values in other expression decrease.

Return Value

DECIMAL

Syntax

CORRELATION(expression1 expression2 [PEARSON|SPEARMAN|KENDALL] -

     [BASEDON dimension-list])

Arguments

expression1

A dimensioned numeric expression with at least one dimension in common with expression2.

expression2

A dimensioned numeric expression with at least one dimension in common with expression1.

PEARSON

Calculates the Pearson product-moment correlation coefficient. Use this method when the data is interval-level or ratios, such as units sold and price for each unit, and the data values in the expressions have a linear relationship and are distributed normally.

SPEARMAN

Calculates Spearman's rho correlation coefficient. Use this nonparametric method when the expressions do not have a linear relationship or a normal distribution. In computing the correlation coefficient, this method ranks the data values in expression1 and in expression2 and then compares the rank of each element in expression1 to the corresponding element in expression2. This method assumes that most of the values in the expressions are unique.

KENDALL

Calculates Kendall's tau correlation coefficient. This nonparametric method is similar to the SPEARMAN method in that it also first ranks the data values in expression1 and in expression2. The KENDALL method, however, compares the ranks of each pair to the successive pairs. Use this method when few of the data values in expression1 and in expression2 are unique.

BASEDON dimension-list

An optional list of dimensions along which CORRELATION computes the correlation coefficient. Both expression1 and expression2 must be dimensioned by all of the dimension-list dimensions. CORRELATION correlates the data values of expression1 to those of expression2 along all of the dimension-list dimensions. CORRELATION returns an array that contains one correlation coefficient for each cell that is dimensioned by all of the dimensions of expression1 and expression2 except those in dimension-list.

When you do not specify a dimension-list argument, then CORRELATION computes the correlation coefficient over all of the common dimensions of expression1 and expression2. When all of the dimensions of the two expressions are the same, then CORRELATION returns a single correlation coefficient. When either expression contains dimensions that are not shared by the other expression, then CORRELATION returns an array that contains one correlation coefficient for each cell that is dimensioned by the dimensions of the expressions that are not shared.

Notes

The Effect of NASKIP on CORRELATION

CORRELATION is affected by the NASKIP option. When NASKIP is set to YES (the default), then CORRELATION ignores NA values. When NASKIP is set to NO, then an NA value in the expressions results in a correlation coefficient of NA.

Examples

Example 6-51 Correlating with the PEARSON Method

Assume that your analytic workspace contains two variables named units and price. The two dimensions of the price variable, month and product, are shared by the units variable, which has a third dimension, district.

The following CORRELATION statement does not specify a dimension-list argument. The output of the CORRELATION function in the statement is one correlation coefficient for each of the dimension values in the dimension that the variables do not have in common.

REPORT CORRELATION(units price pearson)

The preceding statement produces the following output.

CORRELATION
                 (UNITS
                  PRICE
DISTRICT        PEARSON)
-------------- -----------
Boston               -0.75
Atlanta              -0.85
Chicago              -0.83
Dallas               -0.66
Denver               -0.83
Seattle              -0.69

The following statements limit the month and product dimensions.

LIMIT month to 'Jan96' TO 'Mar96'
LIMIT product TO 'Tents' TO 'Racquets'

The following statement reports the correlation coefficient based on the product dimension for the limited dimension values that are in status.

REPORT CORRELATION(units price pearson basedon product)

               CORRELATION(UNITS PRICE PEARSON-
               --------BASEDON PRODUCT)--------
               -------------MONTH--------------
DISTRICT         Jan96      Feb96      Mar96
-------------- ---------- ---------- ----------
Boston              -0.96      -0.90      -0.89
Atlanta             -0.97      -0.97      -0.97
Chicago             -0.96      -0.95      -0.95
Dallas              -0.98      -0.98      -0.99
Denver              -0.97      -0.97      -0.97
Seattle             -0.89      -0.83      -0.83

The following statement reports the correlation coefficient based on the month dimension for the limited dimension values.

REPORT CORRELATION(units price pearson basedon month)

               CORRELATION(UNITS PRICE PEARSON-
               ---------BASEDON MONTH)---------
               ------------PRODUCT-------------
DISTRICT         Tents      Canoes    Racquets
-------------- ---------- ---------- ----------
Boston              -0.59      -0.92      -0.55
Atlanta             -0.73      -0.83       0.03
Chicago             -0.91      -0.84      -0.68
Dallas              -0.86      -0.92       0.31
Denver              -0.98      -0.94      -0.67
Seattle             -0.98      -0.89      -0.70

COS

The COS function calculates the cosine of an angle expression.

Return Value

NUMBER

The result returned by COS is a value with the same dimensions as the specified expression.

Syntax

COS(angle-expression)

Arguments

angle-expression

A numeric expression that contains an angle value, which is specified in radians.

Examples

Example 6-52 Calculating the Cosine of an Angle in Radians

This example calculates the cosine of an angle of 1 radian. The statements

DECIMALS = 5
SHOW COS(1)

produce the following result.

0.54030

Example 6-53 Calculating the Cosine of an Angle in Degrees

This example calculates the cosine of an angle of 60 degrees. Since 1 degree = 2 * (pi) / 360 radians, 60 degrees is about 60 * 2 * 3.14159 / 360 radians. The statement

SHOW COS(60 * 2 * 3.14159 / 360)

produces the following result.

0.50000

COSH

The COSH function calculates the hyperbolic cosine of an angle expression.

Return Value

NUMBER

Syntax

COSH(expression)

Arguments

expression

A numeric expression that contains an angle value, which is specified in radians.

Examples

Example 6-54 Calculating the Hyperbolic Cosine of an Angle

This example calculates the hyperbolic cosine of an angle of 1 radian. The statements

DECIMALS = 5
SHOW COSH(1)

produce the following result.

1.54030

COUNT

The COUNT function counts the number of TRUE values of a Boolean expression. It returns 0 (zero) when no values of the expression are TRUE.

Return Value

INTEGER

Syntax

COUNT(boolean-expression [CACHE] [dimension...])

Arguments

boolean-expression

The Boolean expression whose TRUE values are to be counted.

CACHE

Specifies slightly different internal behavior. Specify this keyword only when the original performance is extremely slow.

dimension

The name of a dimension of the result; or, the name of a relation between one dimension of boolean-expression and another dimension that you want as a dimension of the result.

By default, COUNT returns a single YES or NO value. When you indicate one or more dimensions for the result, COUNT tests for TRUE values along the dimensions that are specified and returns an array of values. Each dimension must be either a dimension of boolean-expression or related to one of its dimensions.

Tip:

When you specify a dimension that is not an actual dimension of boolean-expression, but, instead, is dimension that is related to a dimension of boolean-expression and when there are multiple relations between the two dimensions, Oracle OLAP uses the default relation between the dimensions to perform the calculation. (See the RELATION command for more information on default relations.) When you do not want Oracle OLAP to use this default relation, specify the related dimension by specifying the name of a specify relation.

Notes

The Effect of NASKIP on COUNT

COUNT is affected by the NASKIP option. When NASKIP is set to YES (the default), COUNT returns the number of TRUE values of the Boolean expression, regardless of how many other values are NA. When NASKIP is set to NO, COUNT returns NA when any value of the expression is NA. When all the values of the expression are NA, COUNT returns NA for either setting of NASKIP.

Examples

Example 6-55 Counting True Values by District

You can use COUNT to find the number of months in which each district sold more than 2,000 units of sportswear. To obtain a count for each district, specify district as the dimension for the result.

LIMIT product TO 'SPORTSWEAR'
REPORT HEADING 'Count' COUNT(units GT 2000, district)

The preceding statement statements produce the following output.

DISTRICT         Count
-------------- ----------
Boston                  0
Atlanta                23
Chicago                11
Dallas                 24
Denver                  7
Seattle                 0

CUMSUM

The CUMSUM function computes cumulative totals over time or over another dimension. When the data being totaled is one-dimensional, CUMSUM produces a single series of totals, one for all values of the dimension. When the data has dimensions other than the one being totaled over, CUMSUM produces a separate series of totals for each combination of values in the status of the other dimensions.

Return Value

DECIMAL

Syntax

CUMSUM(cum-expression [STATUS] total-dim [reset-dim] [INSTAT])

Arguments

cum-expression

A numeric variable or calculation whose values you want to total, for example UNITS.

STATUS

When cum-expression is multidimensional, CUMSUM creates a temporary variable to use while processing the function. When you specify the STATUS keyword, CUMSUM uses the current status instead of the default status of the dimensions for calculating the size of this temporary variable. When the dimensions of the expression are limited to a few values and are physically fragmented, you can improve the performance of CUMSUM by specifying STATUS.

When you use CUMSUM with the STATUS keyword in an expression that requires going outside of status for results (for example, with the LEAD or LAG functions or with a qualified data reference), the results outside of status are returned as NA.

Note:

When you specify the STATUS keyword when the data being totaled is one-dimensional, an error results
total-dim

The dimension of cum-expression over which you want to total.

reset-dim

Specifies that the cumulative totals in a series should start over with each new reset dimension value, for example at the start of each new year. The reset dimension can be any of the following:

  • Any dimension related to total-dim through an explicitly defined relation.

  • Any dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR, when total-dim also has a type of DAY, WEEK, MONTH, QUARTER, or YEAR. CUMSUM uses the implicit relation between the two dimensions, so they do not have to be related through an explicit relation. See "Overriding an Implicit Relation".

  • A relation dimensioned by total-dim. CUMSUM uses the related dimension as the reset dimension which enables you to choose which relation is used when there are multiple relations.

INSTAT

Specifies that CUMSUM uses only the values of total-dim that are currently in status. When you do not specify INSTAT, CUMSUM produces a total for all the values of total-dim, independent of its current status. See "INSTAT Ignores Current Status By Default".

Notes

Overriding an Implicit Relation

When you specify dimensions with a type of DAY, WEEK, MONTH, QUARTER, or YEAR for both the total-dim argument and the reset-dim argument, CUMSUM uses the implicit relation between the two dimensions even when an explicit relation exists. However, you can override the default and use the explicit relation by specifying the name of the relation for the reset-dim argument.

INSTAT Ignores Current Status By Default

Unless you specify the INSTAT keyword, CUMSUM ignores the current status in calculating totals. Suppose MONTH is the dimension being totaled over (and INSTAT has not been specified). The CUMSUM total for a given month uses the values for all preceding months, even when some are not in the status. When a reset dimension is specified, the total for a given month uses the values for all preceding months that correspond to the same value of the reset dimension (for example, all preceding months in the same year). To calculate year-to-date totals, specify YEAR as the reset dimension.

Examples

Example 6-56 Multiple CUMSUM Calculations

This example shows cumulative units totals for tents and canoes in the Atlanta district for the first six months of 1996. The report shows the units figures themselves, year-to-date totals calculated using year as the reset dimension, and totals calculated with no reset dimension using all preceding months. Assume that you issue the following statements.

LIMIT district TO 'Atlanta'
LIMIT product TO 'Tents' 'Canoes'
LIMIT month TO 'Jan96' TO 'Jun96'
REPORT DOWN month units CUMSUM(units, month year) -
   CUMSUM(units, month)

The following report is displayed.

DISTRICT: ATLANTA
         ------------------------PRODUCT------------------------ 
         ---------TENTS------------- ---------CANOES------------ 
               CUMSUM(UNI                   CUMSUM(UNI
                TS, MONTH CUMSUM(UNI         TS, MONTH CUMSUM(UNI
MONTH    UNITS    YEAR)   TS, MONTH)  UNITS    YEAR)   TS, MONTH)
-----  -------- --------- ---------- ------- --------- ----------
Jan96      279       279      5,999      281       281      5,162
Feb96      305       584      6,304      309       590      5,471
Mar96      356       940      6,660      386       976      5,857
Apr96      537     1,477      7,197      546     1,522      6,403
May96      646     2,123      7,843      525     2,047      6,928
Jun96      760     2,883      8,603      608     2,655      7,536

The totals for CUMSUM(UNITS, MONTH) include values for all months beginning with the first month, JAN95. The totals for CUMSUM(UNITS, MONTH YEAR) include only the values starting with JAN96.

Example 6-57 Resetting for a Quarter

This example shows cumulative totals for the same products and district, for the entire year 1996. Because quarter is specified as the reset dimension, totals start accumulating at the beginning of each quarter. The cumulative totals for Jan96, Apr96, Jul96, and Oct96 are the same as the units figures for those months. Assume that you issue the following statements.

LIMIT district TO 'Atlanta'
LIMIT product TO 'Tents' 'Canoes'
limit month TO year 'Yr96'
REPORT DOWN month units CUMSUM(units, month quarter)

A report displays.

DISTRICT: ATLANTA
             ------------------PRODUCT------------------
             --------TENTS-------- -------CANOES--------
                          CUMSUM(UNI          CUMSUM(UNI
                           TS, MONTH           TS, MONTH
MONTH           UNITS     QUARTER)    UNITS     QUARTER)
------------ ---------- ---------- ---------- ----------
Jan96             279        279        281        281
Feb96             305        584        309        590
Mar96             356        940        386        976
Apr96             537        537        546        546
May96             646      1,183        525      1,071
Jun96             760      1,943        608      1,679
Jul96             852        852        626        626
Aug96             730      1,582        528      1,154
Sep96             620      2,202        520      1,674
Oct96             554        554        339        339
Nov96             380        934        309        648
Dec96             284      1,218        288        936

CURRENT_DATE

The CURRENT_DATE function returns the current date in the session time zone, as a value in the Gregorian calendar.

Return Values

DATETIME

Syntax

CURENT_DATE

Examples

Example 6-58 Retrieving the Current Date

Assume you want to retrieve the date when the date is February 13, 2007.

SHOW NLS_DATE_FORMAT
DD-MON-RR
SHOW CURRENT_DATE
13-FEB-07

CURRENT_TIMESTAMP

The CURRENT_TIMESTAMP function returns the current date and time in the session time zone, as a value of data type TIMESTAMP_TZ. The time zone offset reflects the current local time of the session.

When you want to retrieve the current date and time in the session time zone as a TIMESTAMP value, use the LOCALTIMESTAMP function.

Return Values

TIMESTAMP_TZ

Syntax

CURRENT_TIMESTAMP [ (precision) ]

Arguments

precision

The fractional second precision of the time value returned. When you omit this argument, then the function uses a default value of 6.

Examples

Example 6-59 Retrieving the Current Timestamp

Assume you want to retrieve the current timestamp.

SHOW CURRENT_TIMESTAMP
13-FEB-07 09.11.33.454685 AM -08:00

DAYOF

The DAYOF function returns an INTEGER in the range of 1 through 7, giving the day of the week on which a specified date falls. A result of 1 refers to Sunday. The result has the same dimensions as the specified DATE expression.

Return Value

INTEGER

Syntax

DAYOF(date-expression)

Arguments

date-expression

An expression that has the DATE data type, or a text expression that specifies a date. Instead of a DATE expression, you can specify a text expression that has values that conform to a valid input style for dates. DAYOF automatically converts the values of the text expression to DATE values, using the current setting of the DATEORDER option to resolve any ambiguity.

Examples

Example 6-60 Finding Today's Weekday

The following statement sends the day of the week on which today's date falls to the current outfile.

SHOW DAYOF(TODAY)

When today's date is January 15, 1997, which is a Wednesday, this statement produces the following output.

4

Example 6-61 Finding the Weekday of a Date

The following statement sends the day of the week on which July 4 fell in 1996 to the current outfile.

SHOW DAYOF('04jul96')

This statement produces the following output.

5

DBTIMEZONE

The DBTIMEZONE function returns the value of the Database time zone.

Return Values

A time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name, depending on how the user specified the database time zone value in the most recent CREATE DATABASE or ALTER DATABASE statement.

Syntax

DBTIMEZONE

Examples

Example 6-62 Retrieving the Database Time Zone

SHOW DBTIMEZONE
-08:00

DDOF

The DDOF function returns an INTEGER in the range of 1 through 31, giving the day of the month on which a specified date falls. The result returned by DDOF has the same dimensions as the specified DATE expression.

Return Value

INTEGER

Syntax

DDOF(date-expression)

Arguments

date-expression

An expression that has the DATE data type, or a text expression that specifies a date. See "Date-only Input Values" for valid formats for a text expression.

Examples

Example 6-63 Finding Today's Day of the Month

The following statement returns the day of the month on which today's date falls.

SHOW DDOF(TODAY)

When today's date is September 8, 2000, this statement produces the following output.

8

DECODE

The DECODE function compares one expression to one or more other expressions and, when the base expression equals a search expression, returns the corresponding result expression; or, when no match is found, returns the default expression when it is specified, or NA when it is not.

Return Value

The data type of the first result argument.

Syntax

DECODE (expr , search, result [, search , result]... [, default])

Arguments

expr

The expression to be searched. The function automatically converts expr to the data type of the first search value before comparing

search

An expression to search for. The function automatically each search value to the data type of the first search value before comparing

result

The expression to return when expression equals search.

default

An expression to return when expression is not equal to search.

Notes

Order of Value Evaluation

The search, result, and default values can be derived from expressions. The function evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, the function never evaluates a search when a previous search equals expr.

Examples

Example 6-64 Decoding an ID Field

Assume that your analytic workspace contains the following objects. Note that the inventory_location formula uses the DECODE function to identify text values that correspond to the INTEGER values of warehouse_id.

DESCRIBE
DEFINE product_id DIMENSION TEXT
DEFINE warehouse_id DIMENSION INTEGER
DEFINE inventories VARIABLE DECIMAL <product_id warehouse_id>
DEFINE inventory_location FORMULA TEXT <warehouse_id>
EQ -
DECODE (warehouse_id, 1, 'Southlake', 2, 'San Francisco', 4, 'Seattle', -
'Non domestic')
 
REPORT inventories
                     ---------------INVENTORIES---------------
                     ---------------PRODUCT_ID----------------
WAREHOUSE_ID                 1770                 1775
-------------------- -------------------- --------------------
                   1                30.63                79.02
                   2                71.49                55.83
                   3                88.71                68.02
                   4                86.27                41.86
 
 
REPORT inventory_location
WAREHOUSE_ID          INVENTORY_LOCATION
-------------------- --------------------
                   1 Southlake
                   2 San Francisco
                   3 Non domestic
                   4 Seattle
 

The following reports illustrate how you can use inventory_location to display the decoded values of warehouse_id in a report rather than displaying the actual values of warehouse_id.

LIMIT product_id to '1775'
 
REPORT DOWN warehouse_id inventories
                     ----INVENTORIES-----
                     -----PRODUCT_ID-----
WAREHOUSE_ID                 1775
-------------------- --------------------
                   1                79.02
                   2                55.83
                   3                68.02
                   4                41.86
 
REPORT DOWN inventory_location inventories
                     ----INVENTORIES-----
                     -----PRODUCT_ID-----
INVENTORY_LOCATION           1775
-------------------- --------------------
Southlake                           79.02
San Francisco                       55.83
Non domestic                        68.02
Seattle                             41.86

Example 6-65 DECODE with BITAND

Assume that you have the following objects with the reported values within your analytic workspace.

DEFINE order_id DIMENSION TEXT
DEFINE customer_id DIMENSION TEXT
DEFINE order_customer COMPOSITE <order_id customer_id>
DEFINE order_status VARIABLE NUMBER(2) <order_customer<order_id customer_id>>
 
REPORT DOWN order_customer order_status
  ORDER_ID   CUSTOMER_ID  ORDER_STATUS
------------ ------------ ------------
2458         101                  0.00
2397         102                  1.00
2454         103                  1.00
2354         104                  0.00
2358         105                  2.00
2381         106                  3.00
2440         107                  3.00
2357         108                  5.00
2394         109                  5.00
2435         144                  6.00
2455         145                  7.00
2356         105                  5.00
2360         107                  4.00
 
 

Assume that the value of order_status is used as a bitmap where the first three bits hold information about the order and the other bits are always 0:

  • The first bit is used for location information:

    0 = Post Office, which corresponds to integer values of 0, 2, 4, and 6.

    1 = Warehouse, which corresponds to the integer values of 1, 3, 5, and 7.

  • The second bit is used for method:

    0 = Air, which corresponds to the integer values of 0, 1, 4, and 5.

    1 = Ground, which corresponds to the integer values of 2, 3, 6, and 7.

  • The third bit is used for receipt:

    0 = Certified, which corresponds the integer values of 0, 1, 2, and 3.

    1 =Insured, which corresponds to the integer values of 4, 5, 6, and 7.

The following formulas use DECODE to substitute the text values for the bit values.

DEFINE location FORMULA DECODE(BITAND(order_status, 1), 1, 'Warehouse', 'PostOffice')
DEFINE method FORMULA DECODE(BITAND(order_status, 2), 2, 'Ground', 'Air')
DEFINE receipt FORMULA DECODE(BITAND(order_status, 4), 4, 'Insured', 'Certified')

Now, you can issue a report to display the decoded values.

REPORT DOWN order_customer order_status location method receipt

  ORDER_ID   CUSTOMER_ID  ORDER_STATUS   LOCATION      METHOD      RECEIPT
------------ ------------ ------------ ------------ ------------ ------------
2458         101                  0.00 PostOffice   Air          Certified
2397         102                  1.00 Warehouse    Air          Certified
2454         103                  1.00 Warehouse    Air          Certified
2354         104                  0.00 PostOffice   Air          Certified
2358         105                  2.00 PostOffice   Ground       Certified
2381         106                  3.00 Warehouse    Ground       Certified
2440         107                  3.00 Warehouse    Ground       Certified
2357         108                  5.00 Warehouse    Air          Insured
2394         109                  5.00 Warehouse    Air          Insured
2435         144                  6.00 PostOffice   Ground       Insured
2455         145                  7.00 Warehouse    Ground       Insured
2356         105                  5.00 Warehouse    Air          Insured
2360         107                  4.00 PostOffice   Air          Insured

DEPRDECL

The DEPRDECL function calculates the depreciation expenses for a series of assets. DEPRDECL uses the declining balance method, as described in "Calculation Method Used by DEPRDECL", to depreciate the assets over the specified lifetime of the assets. The starting value and ending value are specified for the assets acquired in each time period.

Tip:

The pure declining-balance method of depreciation used by DEPRDECL is not the most widely used form of the declining-balance method. For a more commonly used form of the declining-balance method, see the DEPRDECLSW function, which uses a combination of the declining-balance and straight-line methods.

Return Value

DECIMAL

The return value is dimensioned by all the dimensions of start-exp.

Syntax

DEPRDECL(start-exp end-exp n [STATUS] [decline-factor [ {FULL|HALF|portion-exp}[time-dimension] ] ])

Arguments

start-exp

A numeric expression that contains the starting values of the assets. The start-exp expression must be dimensioned by a time dimension. For each value of the time dimension, start-exp contains the initial value of the assets acquired during that time period. In addition to a time dimension, start-exp can also have non-time dimensions.

end-exp

A numeric expression that contains the ending values of the assets. The end-exp expression must be dimensioned by the same dimensions as start-exp. For each value of the time dimension, end-exp contains the final (or salvage) value for the assets acquired during that time period. Each value of start-exp must have a corresponding end-exp value. For example, when the assets acquired in 1996 have a salvage value of $200, then the value of end-exp for 1996 is $200.

n

An INTEGER expression that contains the number of periods for the depreciation life of the assets. The n expression can have any of the non-time dimensions of start-exp, but it cannot have a time dimension.

STATUS

Specifies that DEPRDECL should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the depreciation expenses. By default DEPRDECL uses the default status list.

decline-factor

A numeric expression that gives the declining balance rate to use for calculating the depreciation expenses. The decline-factor expression can have any of the non-time dimensions of start-exp, but it cannot have a time dimension.

A factor of 2 indicates a double declining balance. The default is 2.

FULL

(Default) Specifies that the full amount of a time period's depreciation expense is charged to the time period in which assets were acquired. Charges the full amount to all of the assets in the series.

HALF

Specifies that half of the full amount of a time period's depreciation expense is charged to the time period in which assets were acquired. Charges half the full amount to all of the assets in the series. When you specify HALF as the portion of depreciation expenses to charge to the period of acquisition, the HALF factor is applied to each period. Half of each period's full depreciation is rolled to the next period, and the final half period of depreciation takes place in the time period n + 1. You might want to use HALF when assets are acquired during the second half of the time period.

portion-exp

When you want to charge the full amount for some assets and half the amount for other assets, you can supply a portion-exp expression that is dimensioned by any of the non-time dimensions of start-exp. The portion-exp expression must be a text expression with values of FULL or HALF.

time-dimension

The name of the time dimension by which start-exp and end-exp are dimensioned. When the time dimension has a type of DAY, WEEK, MONTH, QUARTER, or YEAR, the time-dimension argument is optional.

Notes

Calculation Method Used by DEPRDECL

DEPRDECL calculates the depreciation expense for a given time period as the sum of that period's depreciation expenses for all assets in the series that are not yet fully depreciated. The first period of depreciation for an asset is the period in which it was acquired.

For each time period, DEPRDECL calculates the declining balance depreciation expense by multiplying the current value of an asset by the decline-factor and dividing the result by the number of periods in the lifetime of an asset. However, when the calculation for a specific time period results in an asset's current value going below the ending value, then the depreciation expense is adjusted. In this instance, the depreciation expense is calculated as the current value minus the ending value.

Low Ending Value

When the ending value specified for an asset is low enough that the depreciation expense for the last period does not have to be adjusted, then the total depreciation expense over all the periods is typically less than the starting value minus the specified ending value.

High Ending Value

When the ending value specified for an asset is relatively high, then an asset might be totally depreciated in fewer periods than were specified for the lifetime of the depreciation. In this instance, when you want the depreciation expense applied across the specified lifetime of the depreciation, you can lower the decline-factor.

DEPRDECL and NA Values

When a value of start-exp is NA and the corresponding value of end-exp is not NA, an error occurs. Similarly, when a value of end-exp is NA and the corresponding value of start-exp is not NA, an error occurs.

DEPRDECL is affected by the NASKIP option when a value of start-exp and the corresponding value of end-exp are both NA. When NASKIP is YES (the default), DEPRDECL treats the values as zeros when calculating the depreciation expenses. When NASKIP is NO, DEPRDECL returns NA for all affected time periods.

Examples

Example 6-66 Using DEPRDECL to Calculate Depreciation Expenses for Assets Acquired in a Single Period

This example shows how to use DEPRDECL to calculate depreciation expenses for assets acquired in a single time period.

The following statements create two variables called assets and salvage.

DEFINE assets DECIMAL <year>
DEFINE salvage DECIMAL <year>

Suppose you assign the following values to the variables assets and salvage.

YEAR             ASSETS    SALVAGE
-------------- ---------- ----------
Yr95             1,000.00     100.00
Yr96                 0.00       0.00
Yr97                 0.00       0.00
Yr98                 0.00       0.00
Yr99                 0.00       0.00
Yr00                 0.00       0.00

The assets variable contains the starting value of the assets acquired in 1995. The salvage variable contains the ending value of the assets acquired in 1995.

The following statement reports asset and salvage values, along with depreciation expenses for the assets. Note that the call to DEPRDECL to calculate the depreciation expenses specifies an asset lifetime of 5 periods (in this case, years) and a decline factor of 2 (double-declining balance).

REPORT assets salvage W 12 HEADING 'Depreciation' -
   DEPRDECL(assets salvage 5 2 FULL year)

This statement produces the following output.

YEAR             ASSETS    SALVAGE   Depreciation 
-------------- ---------- ---------- ------------
Yr95             1,000.00     100.00       400.00
Yr96                 0.00       0.00       240.00
Yr97                 0.00       0.00       144.00
Yr98                 0.00       0.00        86.40
Yr99                 0.00       0.00        29.60
Yr00                 0.00       0.00         0.00 

In this example, the depreciation expense for 1999 is adjusted so that the current asset value does not fall below the salvage value. The current asset value is calculated by subtracting the accumulated depreciation expense from the starting asset value. For example, for 1998 the accumulated depreciation expense is $870.40 ($400.00 + $240.00 + $144.00 + $86.40 = $870.40). Thus, the current asset value for 1998 is $129.60 ($1,000.00 - $870.40 = $129.60). In this example, the depreciation expense is usually calculated by multiplying the current asset value by 2 and then dividing the result by 5. Now, if $129.60 is multiplied by 2, then divided by 5, the resulting depreciation expense is $51.84. If this depreciation expense is subtracted from the 1998 current asset value of $129.60, the current asset value for 1999 would be $77.76, which is below the salvage value of $100. Instead of letting the current asset value fall below the salvage value, the DEPRDECL function subtracts the salvage value ($100.00) from the current asset value ($129.60) to calculate the depreciation expense ($29.60).

Example 6-67 Using DEPRDECL to Calculate the Depreciation Expenses for Assets Acquired in Multiple Periods

You can also use DEPRDECL to calculate the depreciation expenses for a series of assets.

Suppose you change the values for the year 1997 in the variables assets and salvage to the values shown in the following report.

YEAR             ASSETS    SALVAGE
-------------- ---------- ----------
Yr95             1,000.00     100.00
Yr96                 0.00       0.00
Yr97               500.00      50.00
Yr98                 0.00       0.00
Yr99                 0.00       0.00
Yr00                 0.00       0.00
Yr01                 0.00       0.00
Yr02                 0.00       0.00

Now assets and salvage contain nonzero values for 1995 and for 1997

The following statement reports the values of assets and salvage, and uses DEPRDECL to calculate depreciation expenses for each year, specifying an asset lifetime of 5 years, and a decline factor of 2 (double declining balance).

REPORT assets SALVAGE W 12 HEADING 'Depreciation'  - 
   DEPRDECL(assets salvage 5 2 FULL year)

This statement produces the following output. (Notice that the depreciation expense increases in 1997 due to the assets acquired in that year.)

YEAR             ASSETS    SALVAGE   Depreciation 
-------------- ---------- ---------- ------------
Yr95             1,000.00     100.00       400.00
Yr96                 0.00       0.00       240.00
Yr97               500.00      50.00       344.00
Yr98                 0.00       0.00       206.00
Yr99                 0.00       0.00       101.00
Yr00                 0.00       0.00        43.20
Yr01                 0.00       0.00        14.80
Yr02                 0.00       0.00         0.00

DEPRDECLSW

The DEPRDECLSW function calculates the depreciation expenses for a series of assets. DEPRDECLSW uses a variation on the declining balance method, as described in "Calculation Method Used by DEPRDECLSW", to depreciate assets over the specified lifetime of the assets. DEPRDECLSW begins by using the declining balance method, then switches over to the straight-line method at one of the following points in the time series:

This variation on the declining-balance method is the most commonly used form of declining-balance depreciation methods.

Return Value

DECIMAL, dimensioned by all the dimensions of start-exp.

Syntax

DEPRDECLSW(start-exp end-exp n [STATUS]

     [decline-factor [{FULL|HALF| portion-exp [switch-period [time-dimension]]]])

Arguments

start-exp

A numeric expression that contains the starting values of the assets. The start-exp expression must be dimensioned by a time dimension. For each value of the time dimension, start-exp contains the initial value of the assets acquired during that time period. In addition to a time dimension, start-exp can also have non-time dimensions.

end-exp

A numeric expression that contains the ending value of the assets. The end-exp expression must be dimensioned by the same dimensions as start-exp. For each value of the time dimension, end-exp contains the final (or salvage) value for the assets acquired during that time period. Each value of start-exp must have a corresponding end-exp value. For example, when the assets acquired in 1990 have a salvage value of $200, then the value of end-exp for 1990 is $200.

n

An INTEGER expression that contains the number of periods for the depreciation life of the assets. The n expression can have any of the non-time dimensions of start-exp, but it cannot have a time dimension.

STATUS

Specifies that DEPRDECLSW should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the depreciation expenses. By default DEPRDECLSW uses the default status list.

decline-factor

A numeric expression that gives the declining balance rate to use for calculating the depreciation expenses. The decline-factor expression can have any of the non-time dimensions of start-exp, but it cannot have a time dimension.

A factor of 2 indicates a double declining balance. The default is 2.

FULL

(Default) Specifies that the full amount of a time period's depreciation expense is charged to the time period in which assets were acquired. Charges the full amount to all of the assets in the series. This argument is optional; however, when you include it, you must also include the preceding optional arguments.

HALF

Specifies that half of the full amount of a time period's depreciation expense is charged to the time period in which assets were acquired. Charges half the full amount to all of the assets in the series. You might want to use HALF when assets are acquired during the second half of the time period. When you specify HALF as the portion of depreciation expenses to charge to the period of acquisition, the HALF factor is applied to each period. Half of each period's full depreciation is rolled to the next period, and the final half period of depreciation takes place in the time period n + 1. This argument is optional; however, when you include it, you must also include the preceding optional arguments.

portion-exp

When you want to charge the full amount for some assets and half the amount for other assets, you can supply a portion-exp expression that is dimensioned by any of the non-time dimensions of start-exp. The portion-exp expression must be a text expression with values of FULL or HALF. This argument is optional; however, when you include it, you must also include the preceding optional arguments.

switch-period

An INTEGER expression that indicates the time period in which the calculation should switch to the straight-line method. This argument is optional; however, when you include it, you must also include the preceding optional arguments.

A common accounting practice is to switch to a straight-line method in the first period for which straight-line depreciation over the remaining periods exceeds the declining-balance depreciation. You can specify this behavior by not specifying the switch-period argument.

When the switch-period argument is not specified or has a value of NA or 0, the calculation switches from the declining method to the straight-line method in the first period for which straight-line depreciation over the remaining periods exceeds the declining-balance depreciation. In this case, the DEPRDECLSW function behaves just like the DEPRDECL function.

When you want to specify different switch periods for different assets, you can supply an expression that is dimensioned by any of the non-time dimensions of start-exp.

time-dimension

The name of the time dimension by which start-exp and end-exp are dimensioned. When the time dimension has a type of DAY, WEEK, MONTH, QUARTER, or YEAR, the time-dimension argument is optional. When you include this argument, you must also include the preceding optional arguments

Notes

Calculation Method Used by DEPRDECLSW

DEPRDECLSW calculates the depreciation expense for a given time period as the sum of that period's depreciation expenses for all assets in the series that are not yet fully depreciated. The first period of depreciation for an asset is the period in which it was acquired.

For each time period in which DEPRDECLSW is calculating depreciation according to the declining balance method, it calculates the depreciation expense by multiplying the current value of an asset by the decline-factor and dividing the result by the number of periods in the lifetime of the asset. When DEPRDECLSW switches to the straight-line method, it subtracts the depreciation expense (from previous periods) from the value of an asset and divides the resulting amount by the number of periods left in the lifetime of the asset. However, when the depreciation expense calculated for a specific time period would result in an asset's current value going below its ending value, then the depreciation expense is adjusted. In this instance, the depreciation expense is calculated as the current value minus the ending value.

The straight-line method as used by DEPRDECLSW differs from the traditional straight-line method as used by DEPRSL. Unlike other methods of depreciation, the declining-balance methods of depreciation ignore the salvage value for an asset until the period in which the calculated depreciation would exceed the remaining depreciable value. Even DEPRDECLSW ignores the salvage value in this manner after it switches from the declining-balance method to the straight-line method. For example, suppose the beginning value for an asset is 16,000 and the salvage value is 1,000 over 5 periods. The total depreciation through the periods using declining balance method (here the first three) is 11,544. The straight-line calculations for the remaining periods would be based on the overall remaining value of 16,000 minus 11,544 (3,456), rather than the overall value minus the salvage value (2,456). Thus the depreciation for the last two periods would be 1,728; but for the very last period the salvage value is subtracted out and thus is 728.

Unexpected-Balance Method

When the ending value specified for an asset is relatively high, then an asset might be totally depreciated in fewer periods than were specified for the lifetime of the depreciation. In this instance, when you want the depreciation expense applied across the specified lifetime of the depreciation, you can lower the decline-factor.

DEPRDECLSW and NA Values

When a value of start-exp is NA and the corresponding value of end-exp is not NA, an error occurs. Similarly, when a value of end-exp is NA and the corresponding value of start-exp is not NA, an error occurs.

DEPRDECLSW is affected by the NASKIP option when a value of start-exp and the corresponding value of end-exp are both NA. When NASKIP is YES (the default), DEPRDECLSW treats the values as zeros when calculating the depreciation expenses. When NASKIP is NO, DEPRDECLSW returns NA for all affected time periods.

Examples

Example 6-68 Calculating Depreciation Expenses for Assets Acquired in a Single Period

This example shows how to use DEPRDECLSW to calculate depreciation expenses for assets acquired in a single time period. It also shows the behavior of DEPRDECLSW when you do not specify a switch period.

The following statements create two variables called assets and salvage.

DEFINE assets DECIMAL <year>
DEFINE salvage DECIMAL <year>

Suppose you assign the following values to the variables assets and salvage.

YEAR        ASSETS     SALVAGE
------- ---------- -----------
Yr95      1,000.00      100.00
Yr96          0.00        0.00
Yr97          0.00        0.00
Yr98          0.00        0.00
Yr99          0.00        0.00
Yr00          0.00        0.00

The variable assets contains the starting value of the assets acquired in 1995. salvage contains the ending value of the assets acquired in 1995.

The following statement reports the values of assets and salvage, and uses DEPRDECLSW to calculate depreciation expenses for each year, specifying an asset lifetime of 5 years, and a decline factor of 2 (double declining balance). The statement does not specify a switch-period argument. Because of this, DEPRDECLSW uses the default for switch-period, which is to switch from the declining balance method of depreciation in the first period for which straight-line depreciation over the remaining periods exceeds the declining-balance depreciation.

REPORT assets salvage W 12 HEADING 'Depreciation' -
    DEPRDECLSW (assets salvage 5 2 FULL)

This statement produces the following report.

YEAR        ASSETS     SALVAGE   Depreciation
------- ---------- ----------- --------------
Yr95      1,000.00      100.00      400.00
Yr96          0.00        0.00      240.00
Yr97          0.00        0.00      144.00
Yr98          0.00        0.00      108.00
Yr99          0.00        0.00        8.00
Yr00          0.00        0.00        0.00

Example 6-69 Specifying the Switch Period

Alternatively, you can specify the period in which the switch occurs.

To switch from the declining balance method to the straight-line method of depreciation in the third year (Yr97), specify 3 as the switch period, as shown in the following statement.

REPORT assets salvage W 12 HEADING 'DEPRECIATION' -
    DEPRDECLSW (assets salvage 5 2 FULL 3 year)

This statement produces the following report.

YEAR        ASSETS     SALVAGE   Depreciation
-------- ---------- ----------- --------------
Yr95      1,000.00      100.00      400.00
Yr96          0.00        0.00      240.00
Yr97          0.00        0.00      120.00
Yr98          0.00        0.00      120.00
Yr99          0.00        0.00       20.00
Yr00          0.00        0.00        0.00

Example 6-70 Calculating the Depreciation Expenses for Assets Acquired in Multiple Periods

You can use DEPRDECLSW to calculate the depreciation expenses for a series of assets. Suppose you change the values for the year 1997 in the variables assets and salvage to the values shown in the following report.

YEAR             ASSETS    SALVAGE
-------------- ---------- ----------
Yr95             1,000.00     100.00
Yr96                 0.00       0.00
Yr97               500.00      50.00
Yr98                 0.00       0.00
Yr99                 0.00       0.00
Yr00                 0.00       0.00
Yr01                 0.00       0.00
Yr02                 0.00       0.00

Now assets and salvage contain nonzero values for 1995 and for 1997.

The following statement reports asset and salvage values along with depreciation expenses for the assets. Note that the call to DEPRDECLSW to calculate the depreciation expenses specifies an asset lifetime of 5 periods (in this case, years) and a decline factor of 2 (double-declining balance). The statement does not specify a switch-period argument. Because of this, DEPRDECLSW uses the default for switch-period, which is to switch from the declining balance method of depreciation in the first period for which straight-line depreciation over the remaining periods exceeds the declining-balance depreciation.

REPORT assets salvage W 12 HEADING 'Depreciation'  -
          DEPRDECLSW(assets salvage 5 2 FULL)

This statement produces the following output.

YEAR             ASSETS    SALVAGE   Depreciation
-------------- ---------- ---------- ------------
Yr95             1,000.00     100. 00       400.00
Yr96                 0.00       0.00        240.00
Yr97               500.00      50.00        344.00
Yr98                 0.00       0.00        228.00
Yr99                 0.00       0.00         80.00
Yr00                 0.00       0.00         54.00
Yr01                 0.00       0.00          4.00
Yr02                 0.00       0.00          0.00

Notice that the depreciation expense increases in 1997 due to the assets acquired in that year.


DEPRSL

The DEPRSL function calculates the depreciation expenses for a series of assets. DEPRSL uses the straight-line method, as described in "DEPRSL Calculation Method", to depreciate the assets over the specified lifetime of the assets. The starting and ending values are specified for the assets acquired in each time period.

Return Value

DECIMAL, dimensioned by all the dimensions of start-exp.

Syntax

DEPRSL(start-exp end-exp n [STATUS] [{FULL|HALF| portion-exp [time-dimension]])

Arguments

start-exp

A numeric expression that contains the starting values of the assets. The start-exp expression must be dimensioned by a time dimension. For each value of the time dimension, start-exp contains the initial value of the assets acquired during that time period. In addition to a time dimension, start-exp can also have non-time dimensions.

end-exp

A numeric expression that contains the ending values of the assets. The end-exp expression must be dimensioned by the same dimensions as start-exp. For each value of the time dimension, end-exp contains the final (or salvage) value for the assets acquired during that time period. Each value of start-exp must have a corresponding end-exp value. For example, when the assets acquired in 1995 have a salvage value of $200, then the value of end-exp for 1995 is $200.

n

An INTEGER expression that contains the depreciation lifetime of the assets. The n expression can have any of the non-time dimensions of start-exp, but it cannot have a time dimension.

STATUS

Specifies that DEPRSL should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the depreciation expenses. By default DEPRSL uses the default status list.

FULL

(Default) Specifies that the full amount of a time period's depreciation expense is charged to the time period in which assets were acquired. Charges the full amount to all of the assets in the series.

HALF

Specifies that half of the full amount of a time period's depreciation expense is charged to the time period in which assets were acquired. Charges half the full amount to all of the assets in the series. When you specify HALF as the portion of depreciation expenses to charge to the period of acquisition, the HALF factor is applied to each period. Half of each period's full depreciation expense is rolled to the next period, and the final half period of depreciation takes place in the time period n + 1. You might want to use HALF when assets are acquired during the second half of the time period.

portion-exp

When you want to charge the full amount for some assets and half the amount for other assets, you can supply a portion-exp expression that is dimensioned by any of the non-time dimensions of start-exp. The portion-exp expression must be a text expression with values of FULL or HALF.

time-dimension

The name of the time dimension by which start-exp and end-exp are dimensioned. When the time dimension has a type of DAY, WEEK, MONTH, QUARTER, or YEAR, the time-dimension argument is optional.

Notes

DEPRSL Calculation Method

DEPRSL calculates the depreciation expense for a given time period as the sum of that period's depreciation expenses for all assets in the series that are not yet fully depreciated. The first period of depreciation for an asset is the period in which it was acquired.

DEPRSL and NA Values

When a value of start-exp is NA and the corresponding value of end-exp is not NA, an error occurs. Similarly, when a value of end-exp is NA and the corresponding value of start-exp is not NA, an error occurs.

DEPRSL is affected by the NASKIP option when a value of start-exp and the corresponding value of end-exp are both NA. When NASKIP is YES (the default), DEPRSL treats the values as zeros when calculating the depreciation expenses. When NASKIP is NO, DEPRSL returns NA for all affected time periods.

Examples

Example 6-71 Using DEPRSL to Calculate Depreciation Expenses for Assets Acquired in a Single Period

This example shows how to use DEPRSL to calculate depreciation expenses for assets acquired in a single time period.

The following statements create two variables called assets and salvage.

DEFINE assets DECIMAL <year>
DEFINE salvage DECIMAL <year>

Suppose you assign the following values to the variables assets and salvage.

YEAR               ASSETS    SALVAGE
-------------- ---------- ----------
Yr95             1,000.00     100.00
Yr96                 0.00       0.00
Yr97                 0.00       0.00
Yr98                 0.00       0.00
Yr99                 0.00       0.00
Yr00                 0.00       0.00

The variable assets contains the starting value of assets acquired in 1995. The variable salvage contains the ending value of the assets acquired in 1995.

The following statement reports the values of assets and salvage, and uses DEPRSL to calculate depreciation expenses for each year, specifying an asset lifetime of 5 years.

REPORT assets salvage W 12 HEADING 'Depreciation' -
   DEPRSL(assets salvage 5 FULL year)

This statement produces the following output.

YEAR               ASSETS    SALVAGE Depreciation
-------------- ---------- ---------- ------------
Yr95             1,000.00     100.00       180.00
Yr96                 0.00       0.00       180.00
Yr97                 0.00       0.00       180.00
Yr98                 0.00       0.00       180.00
Yr99                 0.00       0.00       180.00
Yr00                 0.00       0.00         0.00

Example 6-72 Using DEPRSL to Calculate the Depreciation Expenses for Assets Acquired in Multiple Periods

You can also use DEPRSL to calculate the depreciation expenses for a series of assets. Suppose you change the values for the year 1997 in the variables assets and salvage to the values shown in the following report.

YEAR               ASSETS    SALVAGE
-------------- ---------- ----------
Yr95             1,000.00     100.00
Yr96                 0.00       0.00
Yr97               500.00      50.00
Yr98                 0.00       0.00
Yr99                 0.00       0.00
Yr00                 0.00       0.00
Yr01                 0.00       0.00
Yr02                 0.00       0.00

Now assets and salvage contain nonzero values for 1995 and for 1997.

The following statement reports asset and salvage values along with depreciation expenses for the assets. Note that the call to DEPRSL to calculate the depreciation expenses specifies an asset lifetime of 5 periods (in this case, years).

REPORT assets salvage W 12 HEADING 'Depreciation' -
    DEPRSL(assets salvage 5 FULL year)

This statement produces the following report.

YEAR              ASSETS        SALVAGE         Depreciation
-------------- ---------- ------------- --------------------
Yr95             1,000.00       100.00                180.00
Yr96                 0.00         0.00                180.00
Yr97               500.00        50.00                270.00
Yr98                 0.00         0.00                270.00
Yr99                 0.00         0.00                270.00
Yr00                 0.00         0.00                 90.00
Yr01                 0.00         0.00                 90.00
Yr02                 0.00         0.00                  0.00

The assets acquired in 1995 were fully depreciated in 1999. Therefore, for 2000 and 2001, DEPRSL returns a figure that includes the depreciation expense for the assets acquired in 1997 only.


DEPRSOYD

The DEPRSOYD function calculates the depreciation expenses for a series of assets. DEPRSOYD uses the sum-of-years'-digits method, as described in "Calculation Method Used by DEPRSOYD", to depreciate the assets over the specified lifetime of the assets. The starting and ending values are specified for the assets acquired in each time period.

Return Value

DECIMAL, dimensioned by all the dimensions of start-exp.

Syntax

DEPRSOYD(start-exp end-exp n [STATUS] [{FULL|HALF| portion-exp} [time-dimension]])

Arguments

start-exp

A numeric expression that contains the starting values of the assets. The start-exp expression must be dimensioned by a time dimension. For each value of the time dimension, start-exp contains the initial value of the assets acquired during that time period. In addition to a time dimension, start-exp can also have non-time dimensions.

end-exp

A numeric expression that contains the ending values of the assets. The end-exp expression must be dimensioned by the same dimensions as start-exp. For each value of the time dimension, end-exp contains the final (or salvage) value for the assets acquired during that time period. Each value of start-exp must have a corresponding end-exp value. For example, when the assets acquired in 1995 have a salvage value of $200, then the value of end-exp for 1995 is $200.

n

An INTEGER expression that contains the depreciation lifetime of the assets. The n expression can have any of the non-time dimensions of start-exp, but it cannot have a time dimension.

STATUS

Specifies that DEPRSOYD should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the depreciation expenses. By default DEPRSOYD uses the default status list.

FULL

(Default) Specifies that the full amount of a time period's depreciation expense is charged to the time period in which assets were acquired. Charges the full amount to all of the assets in the series.

HALF

Specifies that half of the full amount of a time period's depreciation expense is charged to the time period in which assets were acquired. Charges half the full amount to all of the assets in the series. When you specify HALF as the portion of depreciation expenses to charge to the period of acquisition, the HALF factor is applied to each period. Half of each period's full depreciation expense is rolled to the next period, and the final half period of depreciation expense takes place in the n + 1 time period. You might want to use HALF when assets are acquired during the second half of the time period.

portion-exp

When you want to charge the full amount for some assets and half the amount for other assets, you can supply a portion-exp expression that is dimensioned by any of the non-time dimensions of start-exp. The portion-exp expression must be a text expression with values of FULL or HALF.

time-dimension

The name of the time dimension by which start-exp and end-exp are dimensioned.When the time dimension has a type of DAY, WEEK, MONTH, QUARTER, or YEAR, the time-dimension argument is optional.

Notes

Calculation Method Used by DEPRSOYD

DEPRSOYD calculates the depreciation expense for a given time period as the sum of that period's depreciation expenses for all assets in the series that are not yet fully depreciated. The first period of depreciation for an asset is the period in which it was acquired.

For each time period in the lifetime of an asset, DEPRSOYD bases the depreciation expense calculation on a specific cut of the total amount to be depreciated. The value of the cut is such that the full depreciation expense can be achieved over the lifetime of an asset by multiplying the cut by the number of time periods not yet depreciated.

For example, when the lifetime of an asset is 5 years, then DEPRSOYD calculates the cut, x, as follows.

5x + 4x + 3x + 2x + 1x = total depreciation 

In this case, the cut is 1/15th of the total depreciation. When the initial asset is $1,000 and its salvage value is $100, then the total depreciation is $900.00, and x is $60 ($900/15). For the first time period, the depreciation is $300 ($60 x 5). For the second time period, the depreciation is $240 ($60 x 4) and so on.

DEPRSOYD and NA Values

When a value of start-exp is NA and the corresponding value of end-exp is not NA, an error occurs. Similarly, when a value of end-exp is NA and the corresponding value of start-exp is not NA, an error occurs.

DEPRSOYD is affected by the NASKIP option when a value of start-exp and the corresponding value of end-exp are both NA. When NASKIP is YES (the default), DEPRSOYD treats the values as zeros when calculating the depreciation expenses. When NASKIP is NO, DEPRSOYD returns NA for all affected time periods.

Examples

Example 6-73 Using DEPRSOYD to Calculate Depreciation Expenses for Assets Acquired in a Single Period

This example shows how to use DEPRSOYD to calculate depreciation expenses for assets acquired in a single time period.

The following statements create two variables called assets and salvage.

DEFINE assets DECIMAL <year>
DEFINE salvage DECIMAL <year>

Suppose you assign the following values to the variables assets and salvage.

YEAR               ASSETS    SALVAGE
-------------- ---------- ----------
Yr95             1,000.00     100.00
Yr96                 0.00       0.00
Yr97                 0.00       0.00
Yr98                 0.00       0.00
Yr99                 0.00       0.00
Yr00                 0.00       0.00

The variable assets contains the starting value of assets acquired in 1995. The variable salvage contains the ending value of the assets acquired in 1995.

The following statement reports the values of assets and salvage, and uses DEPRSOYD to calculate depreciation expenses for each year, specifying an asset lifetime of 5 years.

REPORT assets salvage W 12 HEADING 'Depreciation' -
     DEPRSOYD(assets salvage 5 FULL year)

This statement produces the following report.

YEAR               ASSETS    SALVAGE Depreciation
-------------- ---------- ---------- ------------
Yr95             1,000.00     100.00       380.00
Yr96                 0.00       0.00       240.00
Yr97                 0.00       0.00       180.00
Yr98                 0.00       0.00       120.00
Yr99                 0.00       0.00        60.00
Yr00                 0.00       0.00         0.00

Example 6-74 Using DEPRSOYD to Calculate the Depreciation Expenses for Assets Acquired in Multiple Periods

You can also use DEPRSOYD to calculate the depreciation expenses for a series of assets. Suppose you change the values for the year 1997 in the variables assets and salvage to the values shown in the following report.

YEAR             ASSETS    SALVAGE
   -------------- ---------- ----------
   Yr95             1,000.00     100.00
   Yr96                 0.00       0.00
   Yr97               500.00      50.00
   Yr98                 0.00       0.00
   Yr99                 0.00       0.00
   Yr00                 0.00       0.00
   Yr01                 0.00       0.00
   Yr02                 0.00       0.00

Now assets and salvage contain nonzero values for 1995 and for 1997.

The following statement reports asset and salvage values along with depreciation expenses for the assets. Note that the call to DEPRSOYD to calculate the depreciation expenses specifies an asset lifetime of 5 periods (in this case, years).

REPORT assets salvage W 12 HEADING 'Depreciation' -
     DEPRSOYD(assets salvage 5 FULL year)

This statement produces the following output.

YEAR             ASSETS    SALVAGE   Depreciation
   -------------- ---------- ---------- ------------
   Yr95             1,000.00     100.00       300.00
   Yr96                 0.00       0.00       240.00
   Yr97               500.00      50.00       330.00
   Yr98                 0.00       0.00       240.00
   Yr99                 0.00       0.00       160.00
   Yr00                 0.00       0.00        60.00
   Yr01                 0.00       0.00        30.00
   Yr02                 0.00       0.00         0.00

Notice that as a result of the second asset, the depreciation expenses increase in 1997. The depreciation is the total depreciation of $180.00 ($60 x 3) for the first asset and $150.00 ($30 x 5) for the second asset.


ENDDATE

For expressions dimensioned by a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, the ENDDATE function returns the final date of the last time period in the dimension status for which the expression has a non-NA value. For example, when an expression is dimensioned by a dimension of type MONTH, and when DEC98 is the last dimension value for which the expression has a non-NA value, ENDDATE returns the date December 31, 1998.

Return Value

DATE-only or text

Syntax

ENDDATE(expression)

Arguments

expression

The expression must have exactly one dimension that has the type of DAY, WEEK, MONTH, QUARTER, or YEAR. When all the values of the expression are NA, ENDDATE returns NA.

Examples

Example 6-75 Finding the End Date

The following statements limit the values of the dimensions of the units variable, then sends the last date associated with a non-NA value to the current outfile.

LIMIT month TO ALL
LIMIT product TO 'Tents'
LIMIT district TO 'Chicago'
SHOW ENDDATE(units)

These statements produce the following output.

31DEC96

ENDOF

For expressions dimensioned by a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, the ENDOF function returns the last date of a time period that is first in the current status list of the dimension.

ENDOF is particularly useful when the dimension has a phase that differs from the default or when the time periods are formed from multiple weeks or years. For example, when the dimension has four-week time periods, the ENDOF function identifies the final date of a particular four-week period.

Return Value

DATE-only or text

Syntax

ENDOF(dwmqy-dimension)

Arguments

dwmqy-dimension

A dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. When you have explicitly defined your own relation between dimensions of this type, you can use the name of this time relation here.

Examples

Example 6-76 Finding the Fiscal Year End Date

The following statements define a year dimension (called taxyear, for a tax year that begins in July), add dimension values for tax years 1998 through 2000, and produce a report showing the last date of each tax year.

DEFINE taxyear DIMENSION YEAR BEGINNING july
VNF 'TY<ffb>'
MAINTAIN taxyear ADD '01july98' '01july00'
REPORT W 14 ENDOF(taxyear)

These statements produce the following output.

TAXYEAR        ENDOF(TAXYEAR)
-------------- --------------
TY98           30JUN99
TY99           30JUN00
TY00           30JUN01

EVERY

The EVERY function returns YES when every value of a Boolean expression is TRUE, or NO when any value of the expression is FALSE.

Return Value

BOOLEAN

Syntax

EVERY(boolean-expression [CACHE] [dimension...])

Arguments

boolean-expression

The Boolean expression whose values are to be evaluated.

CACHE

Specifies slightly different internal behavior. Specify this keyword only when the original performance is extremely slow.

dimension

The name of a dimension of the result; or, the name of a relation between one dimension of boolean-expression and another dimension that you want as a dimension of the result.

By default, EVERY returns a single YES or NO value. When you indicate one or more dimensions for the result, EVERY tests for TRUE values along the dimensions that are specified and returns an array of values. Each dimension must be either a dimension of boolean-expression or related to one of its dimensions.

Tip:

When you specify a dimension that is not an actual dimension of boolean-expression, but, instead, is dimension that is related to a dimension of boolean-expression and when there are multiple relations between the two dimensions, Oracle OLAP uses the default relation between the dimensions to perform the calculation. (See the RELATION command for more information on default relations.) When you do not want Oracle OLAP to use this default relation, specify the related dimension by specifying the name of a specify relation.

Notes

The Effect of NASKIP on EVERY

EVERY is affected by the NASKIP option. When NASKIP is set to YES (the default), EVERY ignores NA values and returns YES when every value of the expression that is not NA is TRUE and returns NO when any values are not TRUE. When NASKIP is set to NO, EVERY returns NA when any value of the expression is NA. When all the values of the expression are NA, EVERY returns NA for either setting of NASKIP.

Examples

Example 6-77 Testing for All-True Values by District

You can use the EVERY function to test whether each district's sales of sportswear have exceeded $50,000 in every month. To have the results dimensioned by district, specify district as the second argument to EVERY.

LIMIT product TO 'Sportswear'
REPORT HEADING 'Top Sales' EVERY(sales GT 50000, district)

The preceding statements produce the following output.

DISTRICT       Top Sales
-------------- ----------
Boston                 No
Atlanta               Yes
Chicago               Yes
Dallas                Yes
Denver                Yes
Seattle                NO

Example 6-78 Testing for All-True Values by Region

You might also want to find out the regions for which every district has sportswear sales that exceed $50,000 in every month. Since the region dimension is related to the district dimension, you can specify region instead of district as a dimension for the results of EVERY.

REPORT HEADING 'Top Sales' EVERY(sales GT 50000, region)

The preceding statement produces the following output.

REGION         Top Sales
-------------- ----------
East                   No
Central               Yes
West                   NO

EXISTS

The EXISTS function determines whether an object is defined in any attached workspace. The EXISTS function is useful in a program to test whether a definition exists before you try to use it.

Return Value

BOOLEAN

Syntax

EXISTS(name-expression)

Arguments

name-expression

A text expression that specifies the name you want to test.

Notes

Specifying More Than One Name

When name-expression contains multiple object names, EXISTS returns NO even when all the objects specified by name-expression exist in attached workspaces.

Examples

Example 6-79 Using EXISTS

This example tests whether the variable actual has been defined in any attached workspace. The statement

SHOW EXISTS('actual')

produces the following result.

YES

EXP

The EXP function returns e raised to the nth power, where e equals 2.71828183....

Return Value

NUMBER

Syntax

EXP (n)

Arguments

n

The power by which you want to raise e.

Examples

Example 6-80 Raising an Expression to a Power

The following example returns e to the 4th power.

SHOW EXP(4) 

54.59815 

EVERSION

The EVERSION function returns a text value that specifies the internal Oracle OLAP build number.

Return Value

TEXT

Syntax

EVERSION

Notes

EVERSION and Major Releases

The build number in the output of the EVERSION function is not the Oracle Database version number. The EVERSION value does not change only with major releases of the Database.

Examples

Example 6-81 Obtaining the Version Number

The following statement produces text output that indicates the Oracle OLAP build number.

SHOW EVERSION

This statement produces output like the following.

Oracle OLAP Build 80020

EXTBYTES

The EXTBYTES function extracts a portion of a text expression.

Return Value

TEXT

Syntax

EXTBYTES(text-expression [start [length]])

Arguments

text-expression

A TEXT expression from which a portion is to be extracted. When text-expression is a multiline TEXT value, EXTBYTES preserves the line breaks in the returned value.

start

An INTEGER that represents the byte position at which to begin extracting. The position of the first byte in text-expression is 1. When you omit this argument, EXTBYTES starts with the first byte.

length

An INTEGER that represents the number of bytes to be extracted. When length is not specified, or exceeds the number of bytes from start to the end of text-expression, the part from start to the end of text-expression is extracted.

Examples

Example 6-82 Extracting Text Characters Using Bytes

This example shows how to extract portions of text from the TEXT value 'hellotherejoe'.

  • The statement

    SHOW EXTBYTES('hellotherejoe', 6, 5)
    

    produces the following output.

    there
    
  • The statement

    SHOW EXTBYTES('hellotherejoe', 11)
    

    produces the following output.

    joe 
    

EXTCHARS

The EXTCHARS function extracts a portion of a text expression.

Tip:

When you are using a multibyte character set, you can use the EXTBYTES function instead of the EXTCHARS function.

Return Value

TEXT or NTEXT

Syntax

EXTCHARS(text-expression [start [length]])

Arguments

text-expression

A TEXT or NTEXT expression from which a portion is to be extracted. When text-expression is a multiline text value, EXTCHARS preserves the line breaks in the returned value.

start

An INTEGER that represents the character position at which to begin extracting. The position of the first character in text-expression is 1. When you omit this argument, EXTCHARS starts with the first character.

length

An INTEGER that represents the number of characters to be extracted. When length is not specified, or exceeds the number of characters from start to the end of text-expression, the part from start to the end of text-expression is extracted.

Examples

Example 6-83 Extracting Text Characters

This example shows how to extract portions of text from the TEXT value 'hellotherejoe'.

  • The statement

    SHOW EXTCHARS('hellotherejoe', 6, 5)
    

    produces the following output.

    there
    
  • The statement

    SHOW EXTCHARS('hellotherejoe', 11)
    

    produces the following output.

    joe 
    

EXTCOLS

The EXTCOLS function extracts specified columns from each line of a multiline text value. The function returns a multiline text value that includes only the extracted columns. Columns refer to the character positions in each line of a multiline text value. The first character in each line is in column one, the second is in column two, and so on.

Return Value

TEXT or NTEXT

EXTCOLS always returns a text value that has the same number of lines as text-expression, though some lines may be empty.

Syntax

EXTCOLS(text-expression [start [numcols]])

Arguments

text-expression

The TEXT or NTEXT expression from which the specified columns should be extracted. When text-expression is a multiline text value, the characters in the specified columns are extracted from each one of its lines.

start

An INTEGER, between 1 and 4000, that represents the column position at which to begin extracting. The column position of the first character in each line of text-expression is 1. When you specify a starting column that is to the right of the last character in a given line in text expression, the corresponding line in the return value is empty.

numcols

An INTEGER that represents the number of columns to be extracted. When you do not specify numcols, EXTCOLS extracts all the characters from the starting column to the end of each line. When you specify a length that exceeds the number of characters that follow the starting position in a given line in text expression, the corresponding line in the return value includes only existing characters. EXTCOLS does not return spaces at the end of the line to fill in the missing columns.

Examples

Example 6-84 Extracting Text Columns

In this example, four columns are extracted from each line of citylist, starting from the second column.

DEFINE citylist VARIABLE TEXT
citylist = 'Boston\nHouston\nChicago'
  • The statement

    SHOW citylist
    

    produces the following output.

    Boston
    Houston
    Chicago
    
  • The statement

    SHOW EXTCOLS(citylist 2 4)
    

    produces the following output.

    osto
    oust
    hica 
    

EXTLINES

The EXTLINES function extracts lines from a multiline text expression.

Return Value

TEXT or NTEXT

Syntax

EXTLINES(text-expression [start [numlines]])

Arguments

text-expression

A multiline TEXT or NTEXT expression from whose values one or more lines are to be extracted.

start

An INTEGER that represents the line number at which to begin extracting. The position of the first line in text-expression is 1. When you omit this argument, EXTLINES begins with line 1.

numlines

An INTEGER representing the number of lines to be extracted. When you do not specify numlines, or when you specify a number greater than the number of lines from start to the end of text-expression, all the lines from start to the end of text-expression are copied.

Examples

Example 6-85 Extracting One Text Line

This example shows how to extract the second line from a multiline text value in a variable called mktglist. The mktglist variable has the following values.

Salespeople
Products
Services

The statement

SHOW EXTLINES(mktglist 2 1)

produces the following output.

Products 

EXTRACT

The EXTRACT function extracts and returns the value of a specified datetime value from a datetime or interval value expression. This function can be very useful for manipulating datetime values in very large variables.

Return Values

The value returned varies:

Syntax

EXTRACT(time |timezone_hour_or_nimute |timezone_regn_or_abbr FROM datetime_exp| interval_exp )

Arguments

time

One of the following keywords: YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND which specify the portion of the time that you want the function to return.

timezone_hour_or_minute

One of the following keywords: TIMEZONE_HOUR or TIMEZONE_MINUTE which specify that you want the function to return either the hour or minute portion of a TIMESTAMP_TZ expression.

timezone_regn_or_abbr

One of the following keywords: TIMEZONE_REGION or TIMEZONE_ABBR which specify that you want the function to return a string that is either the region name or its abbreviation.

datetime_exp

A DATETIME, TIMESTAMP, TIMESTAMP_TZ, or TIMESTAMP_LTZ expression. See "Datetime Expressions" for information on how to specify these expressions.

interval_exp

A DSINTERVAL or YMINTERVAL expression. See "Interval Expressions" for information on how to specify these expressions.

Notes

The value you are extracting must be a value of the appropriate datetime_exp or interval_exp. For example, you can extract only YEAR, MONTH, and DAY from a DATETIME value. Likewise, you can extract TIMEZONE_HOUR and TIMEZONE_MINUTE only from the TIMESTAMP_TZ data type.

Examples

Example 6-86 Extracting the Hour from a Timestamp

DEFINE mytimestamptz VARIABLE TIMESTAMP_TZ
REPORT mytimestamptz
 
MYTIMESTAMPTZ
------------------------------
  26-MAR-06 12.00.00 AM -04:00

SHOW EXTRACT (TIMEZONE_HOUR FROM mytimestamptz)
-4.00

FCOPEN

The FCOPEN function creates a forecasting context and returns a handle to this context.

You must use the FCOPEN function in combination with other OLAP DML statements as outlined in "Forecasting Programs".

Return Value

INTEGER

Syntax

FCOPEN(text-expression [prototype-handle])

Arguments

text-expression

The name of the forecasting context.

prototype-handle

An INTEGER expression that is the handle to a different forecasting context that was previously-created using the FCOPEN function. Oracle OLAP initializes the new forecasting context with the same options as the forecasting context specified by this parameter. (See the FCSET command for descriptions of the options that specify the characteristics of a forecasting context.)

Examples

For an example of a forecasting program, see Example 8-118, "A Forecasting Program".


FCQUERY

The FCQUERY function queries the results of a forecast created when the FCEXEC command executed.

You must use the FCQUERY function in combination with other OLAP DML statements as outlined in "Forecasting Programs".

Return Value

The return value depends on the option that you use as described in the tables for this entry.

Syntax

FCQUERY(HANDLELIST|handle-expression option -

     [TRIAL trial-num] [CYCLE cycle-num])

Arguments

HANDLELIST

When you specify the HANDLELIST keyword, the FCQUERY function returns a multiline text expression that is a list of the handles to forecasting contexts that are currently open.

handle-expression

An INTEGER expression that is the handle to forecast context that you want to query and that was previously opened using the FCOPEN function.

option

The specific information to retrieve:

  • When you want information about the options specified for the entire forecast, do not use the TRIAL keyword. In this case, option can be any of the options that you can specify using the FCSET command and any of the options listed in Table 6-7, "Options That You Can Specify for the Entire Forecast".

    Table 6-7 Options That You Can Specify for the Entire Forecast

    Keyword Return type Description

    HANDLEID

    TEXT

    The name of the forecasting context when a value was specified when the forecasting context was opened using the FCOPEN command; or NA when no name was specified at that time.

    TRIALSRUN

    INTEGER

    The number of trials for which data is available; or NA when no trials were run.


  • When you want information about a specific trial, use the TRIAL trial-num phrase. In this case, option can be any of the options listed in Table 6-8, "Options That You Can Specify for an Individual Trial".

    Table 6-8 Options That You Can Specify for an Individual Trial

    Option Return Value Description

    ALLOCLAST

    BOOLEAN

    Indicates whether the risk of over-adjustment should be reduced by allocating, instead of forecasting, the last cycle.

    ALPHA

    DOUBLE

    The value of Alpha for this trial of the forecast. Alpha is the level or baseline parameter that is used for the Single Exponential Smoothing, Double Exponential Smoothing, and Holt-Winters forecasting methods.

    BETA

    DOUBLE

    The value of Beta for this trial of the forecast. Beta is the trend parameter that controls the estimate of the trend. Beta is used for the Double Exponential Smoothing and Holt-Winters forecasting methods.

    COMPSMOOTH

    BOOLEAN

    Indicates whether optimization should be done on the median smoothed data series.

    CYCDECAY

    DOUBLE

    The value of the cyclic decay parameter for this trial of the forecast. Cyclical decay pertains to how seriously Oracle OLAP considers deviations from baseline activity when it performs linear and nonlinear regressions.

    GAMMA

    DOUBLE

    The value of Gamma for this trial of the forecast. Gamma is the seasonal parameter that is used for the Holt-Winters forecasting method.

    HISTUSED

    INTEGER

    The number of historical periods actually used, after all leading NA values are bypassed.

    MAD

    DOUBLE

    The mean absolute deviation (MAD) for this trial of the forecast.

    MAPE

    DOUBLE

    The mean average percent error (MAPE) for this trial of the forecast.

    MAXFCFACTOR

    DECIMAL

    The upper bound of the forecast data.

    METHOD

    TEXT

    The forecasting method that Oracle OLAP used for this trial of the forecast. See the METHOD option of the FCSET command for descriptions of the various methods.

    MINFCFACTOR

    DECIMAL

    The lower bound of the forecast data.

    MPTDECAY

    DOUBLE

    The value of the parameter that Oracle OLAP used when it adjusted the decay of estimates of base values that were used when it unraveled the predictions on the moving periodic total (MPT) series for this trial of the forecast.

    NCYCLES

    INTEGER

    The number of cycles specified using the PERIODICITY argument to FCSET.

    PERIODICITY

    INTEGER

    The length, in periods, of one or more cycles. The return value depends on the way you call the FCQUERY function:

    When you specify the CYCLE argument, PERIODICITY returns the number of periods in the specified cycle.

    When you do not specify the CYCLE argument and FCSET ALLOCLAST is NO, PERIODICITY returns the product of all cycle lengths.

    When you do not specify the CYCLE argument and FCSET ALLOCLAST is YES, PERIODICITY returns the product of all cycle lengths leaving out the length of the last (least aggregate) cycle.

    RMSE

    DOUBLE

    The root mean squared error (RMSE) for this trial of the forecast.

    SMOOTHING

    BOOLEAN

    Indicates whether Oracle OLAP smoothed the data for this trial of the forecast. YES indicates that Oracle OLAP smoothed the data; NO indicates that Oracle OLAP did not smooth the data.

    TRANSFORM

    TEXT

    The data filter that Oracle OLAP used for this trial of the forecast. See the TRANSFORM option of the FCSET command for descriptions of the various filters.

    TRENDHOLD

    DOUBLE

    The value of the trend hold parameter for this trial of the forecast. trend hold parameter that indicates trend reliability in Double Exponential Smoothing and Holt-Winters forecasting methods.


trial-num

An INTEGER expression that is the number of the trial for which you want to retrieve information.

cycle-num

An INTEGER expression that specifies a cycle for which you want information from the PERIODICITY option (see Table 6-8, "Options That You Can Specify for an Individual Trial"). When you specified a series of cycles using the PERIODICITY argument in the FCSET command, then the value of cycle-num indicates the position of the cycle of interest in the specified series. For example, assume that FCSET PERIODICITY <52,7> was specified. In this case, a cycle-num of 1 returns 52 and a cycle-num of 2 returns 7. When you did not specify a series of cycles using the PERIODICITY argument in the FCSET command, then it is unnecessary to specify this argument.

Notes

Using Options

You can retrieve information about the options specified for the entire forecast or information about a specific trial.

Accessing Dimensioned Data

When multiple time series are in status when the FCEXEC command executes, then the TRIALSRUN and the NTRIAL-dimensioned data are also be dimensioned by the extra dimensions of the time-series expression. Although Oracle OLAP treats the value returned by the FCQUERY function as a scalar expression, you can access its dimensioned data in any of the following ways:

Examples

Example 6-87 Querying a Forecast

The autofcst program illustrated in Example 8-118, "A Forecasting Program" calls a program named queryall. The queryall program retrieves the characteristics of the trials of the forecast using the following code.

DEFINE queryall PROGRAM
PROGRAM
VARIABLE numtrials INTEGER
VARIABLE loopindx INTEGER
numtrials = FCQUERY(hndl trialsrun)
row numtrials 'TRIALS'
loopindx = 1
WHILE loopindx LE numtrials
  DO
    ROW loopindx 'METHOD' FCQUERY(hndl method trial loopindx)
    ROW loopindx 'TRANSFORM' FCQUERY(hndl transform trial loopindx)
    ROW loopindx 'SMOOTHING' FCQUERY(hndl smoothing trial loopindx)
    ROW loopindx 'ALPHA' FCQUERY(hndl alpha trial loopindx)
    ROW loopindx 'BETA' FCQUERY(hndl beta trial loopindx)
    ROW loopindx 'GAMMA' FCQUERY(hndl gamma trial loopindx)
    ROW loopindx 'TRENDHOLD' FCQUERY(hndl trendhold trial loopindx)
    ROW loopindx 'CYCDECAY' FCQUERY(hndl cycdecay trial loopindx)
    row loopindx 'MPTDECAY' FCQUERY(hndl mptdecay trial loopindx)
    ROW loopindx 'MAD' FCQUERY(hndl mad trial loopindx)
    ROW loopindx 'MAPE' FCQUERY(hndl mape trial loopindx)
    ROW loopindx 'RMSE' FCQUERY(hndl rmse trial loopindx)
    loopindx = loopindx + 1
  DOEND
END

A sample report created from the output of the QUERYALL program follows.

3 TRIALS
             1 METHOD     HOLT/WINTERS
             1 TRANSFORM  TRNOSEA
             1 SMOOTHING          NO
             1 ALPHA             0.2
             1 BETA              0.3
             1 GAMMA             0.3
             1 TRENDHOLD         0.8
             1 CYCDECAY           -1
             1 MPTDECAY           -1
             1 MAD         324.97047
             1 MAPE       23.6192147
             1 RMSE        389.40202
             2 METHOD     HOLT/WINTERS
             2 TRANSFORM  TRNOSEA
             2 SMOOTHING          NO
             2 ALPHA             0.2
             2 BETA              0.3
             2 GAMMA             0.2
             2 TRENDHOLD         0.8
             2 CYCDECAY           -1
             2 MPTDECAY           -1
             2 MAD         324.97047
             2 MAPE       23.6192147
             2 RMSE        389.40202
             3 METHOD     HOLT/WINTERS
             3 TRANSFORM  TRNOSEA
             3 SMOOTHING          NO
             3 ALPHA             0.2
             3 BETA              0.3
             3 GAMMA             0.1
             3 TRENDHOLD         0.8
             3 CYCDECAY           -1
             3 MPTDECAY           -1
             3 MAD         324.97047
             3 MAPE       23.6192147
             3 RMSE        389.40202 

FILEERROR

The FILEERROR function returns information about the first error that occurred when you are processing a record from an input file with the data reading statements FILEREAD and FILEVIEW. It can tell you what type of error occurred and where Oracle OLAP was in the record. The keyword you specify as an argument determines the kind of information that is returned.

Call FILEERROR once to find out the type of error. Then, you can call FILEERROR again to get more details about what caused the error. The return values for the type of error are also FILEERROR keywords. When FILEERROR returns a value other than NA, then you would probably call FILEERROR a second time using the return value itself as an argument.

The abbreviation for FILEERROR is FILEERR

Return Value

Varies depending on the specified keyword.

Syntax

FILEERROR (TYPE|POSITION|WIDTH|VALUE|DIMENSION)

Arguments

TYPE

Returns a text expression that specifies the type of error that has occurred. The types of errors and their meanings are listed in Table 6-9, "Types of Errors Returned by FILEERROR".

Table 6-9 Types of Errors Returned by FILEERROR

Return Value Meaning

DIMENSION

The data reading statements tried to set the status of a dimension (through an implicit or explicit MATCH attribute), but the specified position or value did not exist.

NA

No error occurred in the processing of the current record.

POSITION

The data reading program tried to read from an invalid location in the record. A POSITION error can occur when the field or column is before the beginning of the record or when the field extends past the end of the record. An error beyond the end of the record occurs only for binary or packed data; for symbolic (textual) data, the data reading statements pad short records with blanks.

VALUE

The value could not be converted to the requested data type. For packed data, the record had an invalid hexadecimal digit.

WIDTH

The data reading statements specified an invalid field width. Invalid widths depend on the format of the data, which can be symbolic, packed, or binary:

  • For symbolic format, the width is invalid when it is less than 1 or when it is NA. Note that NA is acceptable for ID data.

  • For packed format, the width is invalid when it is less than 1, greater than 8, or NA.

For binary format, the width requirement depends on whether the data is INTEGER or DECIMAL (floating-point). Integer data must have a width of 1, 2, or 4. Decimal data must have a width of 4 or 8.


POSITION

Returns an INTEGER that is the column number (for RULED records) or field number (for STRUCTURED records) when the error occurred.

WIDTH

Returns an INTEGER that is the current field width. It returns NA when NA was specified as the width or the error was a POSITION error. A POSITION error stops processing before the width can be evaluated.

VALUE

When the error type is VALUE, it returns a text expression that is the value that could not be converted. When the data is packed, the invalid value is shown as hexadecimal escapes. When the error type is DIMENSION, it returns the value that did not match any existing dimension value. For other error types, it returns NA.

DIMENSION

When the error type was DIMENSION, it returns a text expression that is the name of the dimension that had no matching dimension values. For other error types, it returns NA.

Notes

Flow of Control

When an error occurs in FILEREAD or FILEVIEW, processing of the current record stops and Oracle OLAP displays an appropriate error message. Then, when your program has a trap label, control branches to the label where you might call FILEERROR to investigate the problem. When you branch back to a FILEREAD or FILENEXT function, processing continues with the next record. When there are more errors in the record, those errors are not evaluated.

Displaying Error Messages in the Current Outfile

Set ECHOPROMPT to YES in your data reading program when you want error messages to be displayed in the current outfile. When the error occurred during FILEREAD or FILEVIEW, any evaluation by FILEERROR occurs after the error message.

Examples

Example 6-88 Error-Handling with TRAP

This example shows a sample trap label (ERROR:) and the error-handling code that follows it. (For information on error trapping and trap labels, see the TRAP command.) The code checks whether the file has been opened. If so, it checks whether the error that caused the branch is a data reading error. When it is, the program calls FILEERROR in a SHOW command to display information about the error. The body of the program (not shown) contains code that opens the file and assigns a file unit number to the variable fil.unit. ERRTYPE is a local variable that is declared at the beginning of the program.

error:
IF fil.unit EQ NA
  THEN DO
    POPLEVEL 'save'
    RETURN
  DOEND
IF ERRORNAME NE 'attn'
  THEN DO
    ERRTYPE = FILEERROR(TYPE)
    IF ERRTYPE NE NA
      THEN SHOW JOINCHARS('Error in record ' RECNO(fil.unit) -
         ' in column ' FILEERROR(POSITION) ': ' -
         ERRTYPE ' ' FILEERROR(&ERRTYPE))
    TRAP ON ERROR
    GOTO NEXT
  DOEND
FILECLOSE fil.unit
POPLEVEL 'save'
RETURN

FILEGET

The FILEGET function returns text from a non-binary file that has been opened for reading. When FILEGET reaches the end of the file, it returns NA. All text read with FILEGET is translated into the database character set. FILEGET cannot read data that cannot be represented in the database character set.

Return Value

TEXT

Syntax

FILEGET(fileunit [LENGTH int-expression])

Arguments

fileunit

An INTEGER value that was assigned to a file opened for reading in a previous call to the FILEOPEN function.

LENGTH int-expression

An INTEGER expression specifying the number of bytes FILEGET returns from the file. When an end-of-line character is reached in the input file, FILEGET simply starts a new line in the result it is constructing. When LENGTH is omitted, FILEGET reads one line or record regardless of how many bytes it contains.

Notes

Difference Between Number of Bytes Read and Number of Bytes Returned

The value specified by LENGTH refers to the number of bytes that the FILEGET function returns, not to the number of bytes that it reads. In some cases, these values may differ. For example, when the file being read contains a tab character, the number of bytes returned by FILEGET includes the bytes for tab expansion (if any); consequently, the number of bytes returned by FILEGET could be larger than the number of bytes read by FILEGET.

Examples

Example 6-89 Program for Reading a File

Suppose you have a program called readfile that takes a file name as its argument. It opens the file, reads the lines of the file, adds them to a multiline text variable named wholetext, then closes it. readfile uses local variables to store the fileunit number and each line of the file as it is read.

DEFINE wholetext VARIABLE TEXT
LD Multiline text variable
DEFINE readfile PROGRAM
LD Program to store data from a file in a multiline text variable
PROGRAM
VARIABLE fil.unit INTEGER  "Local Var To Store File Unit
VARIABLE fil.text TEXT     "Local Var To Store Single Lines
FIL.UNIT = FILEOPEN(ARG(1) READ)
FIL.TEXT = FILEGET(fil.unit)        "Read The First Line
WHILE fil.text NE NA                "Test For End-of-file
  DO
  wholetext = JOINLINES(wholetext, fil.text)
  fil.text = FILEGET(fil.unit)      "Read The Next Line
  DOEND
FILECLOSE fil.unit
END 

FILENEXT

The FILENEXT function makes a record available for processing by the FILEVIEW command. It returns YES when it was able to read a record and NO when it reached the end of the file.

Return Value

BOOLEAN

Syntax

FILENEXT(fileunit)

Arguments

fileunit

An INTEGER value that is assigned to a file that is opened for reading in a previous call to the FILEOPEN function or by the OUTFILE command.

Notes

Opening and Closing Files

Before you can get records from a file with FILENEXT, use the FILEOPEN function to open the file for reading (READ mode). When you are finished, close the file with a FILECLOSE statement.

Processing Data

After reading a record with FILENEXT, use a FILEVIEW statement to process the record. FILEVIEW processes input data and assigns the data to analytic workspace objects or local variables according to a description of each field. You can call FILEVIEW more than once for continued processing of the same record. To process another record, call FILENEXT again.

Automatic Looping

When all the records are being processed in essentially the same way, the FILEREAD command is easier to use because it loops over the records in a file automatically.

Writing Records

To write selected records to an output file, see the FILEPUT command.

Record Numbers

Use the RECNO function to get the current record number for any file that is opened for read-only access.

Reading Binary and Text Files

When you did not specify BINARY for the file when you opened it, FILENEXT reads data up to and including the next newline character. When you specified BINARY for the file when you opened it, you must use FILESET to set LSIZE to the appropriate record length before using the FILENEXT function. Then, FILENEXT reads data one record at a time.

Examples

Example 6-90 Program That Uses FILENEXT

Suppose you receive monthly sales data in a file with the following record layout.

Column        Width         Format             Data
 
1             1             Text               Division code
2             10            Text               District name
12            10            Text               Product name
30            4             Packed binary      Sales in dollars
34            4             Packed binary      Sales in units

You want to process records only for your division, whose code is A. The following program excerpt opens the file, reads the lines of the file, determines if the data is for division A and, if so, reads the sales data, then closes the file. The file name is given as an argument on the statement line after the program name.

VARIABLE fil.unit INTEGER
. . .
fil.unit = FILEOPEN(arg(1) READ)
LIMIT month TO &arg(2)
 
WHILE FILENEXT(fil.unit)
  DO
    FILEVIEW fil.unit WIDTH 1 rectype
    IF rectype EQ 'A'
      THEN FILEVIEW fil.unit COLUMN 2 WIDTH 10 district -
                             WIDTH 10 product -
                             COLUMN 30 WIDTH 4 BINARY sales -
                             WIDTH 4 BINARY UNITS
  DOEND
FILECLOSE fil.unit

FILEOPEN

The FILEOPEN function opens a file, assigns it a fileunit number (an arbitrary INTEGER), and returns that number. You use the fileunit number, rather than a file name, in any further references to the file. When Oracle OLAP cannot open the file, an error occurs.

See also:

OUTFILE

Return Value

INTEGER

Syntax

FILEOPEN(file-name {READ|WRITE|APPEND} [BINARY]) [NLS_CHARSET charset-exp]

Arguments

file-name

A text expression specifying the name of the file you want to open. Unless the file is in the current directory, you must include the name of the directory object in the name of the file.

Note:

Directory objects are defined in the Database, and they control access to directories and file in those directories. You can use a CDA statement to identify and specify a current directory object. Contact your Oracle DBA for access rights to a directory object where your database user name can read and write files.
READ

(Abbreviated R) Opens the file for reading.

WRITE

(Abbreviated W) Opens the file for writing. File access begins at the top of the file. Therefore, opening an existing file in WRITE mode erases its contents completely even before anything is written to the file.

APPEND

Opens the file for writing. File access begins at the end of the file, and data is added to the existing contents.

BINARY

Opens a binary-format file (a file with packed or binary data). When you specify BINARY, Oracle OLAP considers every character in the file to be data. Rather than using newline characters to tell when records end, it assumes records of a fixed length, which you can set with FILESET(...LSIZE). The default record length is 80.

NLS_CHARSET charset-exp

Specifies the character set that Oracle OLAP uses when reading data from the file specified by file-name. When this argument is omitted, then Oracle OLAP handles the data in the file as having the database character set, which is recorded in the NLS_LANG option.

Notes

Multiple File Units

You can open as many files at the same time as your operating system allows.

Access Modes

The mode of access, READ, WRITE, or APPEND, must be appropriate to the file.

Examples

Example 6-91 FILEOPEN with an Argument Passed into a Program

The following line from a program opens a file whose name was specified as a program argument and saves the fileunit number in the variable fil.unit.

fil.unit = FILEOPEN(ARG(1), READ)

Example 6-92 FILEOPEN with a Binary File

The following statements open a binary file and set the record length.

VARIABLE filenum INTEGER
filenum = FILEOPEN('mydata' READ BINARY)
FILESET filenum LSIZE 132 

FILEQUERY

The FILEQUERY function returns information about a file. The attribute argument you specify in your FILEQUERY function call determines the type of information that is returned.

Return Value

The data type of the return value depends on the attribute you specify. See Table 6-10, "File Attributes Returned by FILEQUERY" for more information.

Syntax

FILEQUERY(file-id attrib-arg)

Arguments

file-id

A fileunit number or a file name.

  • A fileunit number is a number that Oracle OLAP assigned to a file you opened through a previous call to the FILEOPEN function or through the OUTFILE command. You can use the return value of the FILEOPEN function or the value of the OUTFILEUNIT option.

  • A file name is a text expression specifying the name of the file you want to move or rename. Unless the file is in the current directory, you must include the name of the directory object in the name of the file.

    Note:

    Directory objects are defined in the Database, and they control access to directories and file in those directories. You can use a CDA statement to identify and specify a current directory object. Contact your Oracle DBA for access rights to a directory object where your database user name can read and write files.

Some attributes require that you specify a fileunit number; others require the file name. In many cases, you can specify either. See Table 6-10, "File Attributes Returned by FILEQUERY" for more information.

attrib-arg

Specifies the type of information you want to retrieve about the file. The data type of FILEQUERY's return value depends on the attribute you specify. The attribute you specify must be appropriate for the file; otherwise, an error occurs. Table 6-10, "File Attributes Returned by FILEQUERY" lists the valid keywords for attrib-arg and, for each keyword, provides a description and indicates whether you specify a file-unit-number of a file-name for the file-id argument.

Table 6-10 File Attributes Returned by FILEQUERY

Keyword Return Values Return Data Type file-id Argument

APPEND

TRUE when the file is open for writing at the end (that is, TRUE for APPEND and WRITE); FALSE when it is not.

BOOLEAN

Fileunit number

BMARGIN

The number of blank lines that form the bottom margin.

INTEGER

Fileunit number

CHANGED

TRUE when the file's archive bit is set; FALSE when it is not.

BOOLEAN

Fileunit number or file name

EOF

TRUE when end-of-file has been reached; FALSE when it is not.

BOOLEAN

Fileunit number

EXISTS

TRUE when the file exists; FALSE when it is not.

BOOLEAN

Fileunit number or file name

FILENAME

The file name associated with the fileunit.

TEXT

Fileunit number

LINENUM

The current line number. Resets after each page break when PAGING is on; keeps incrementing when PAGING is off. When file is currently open in READ mode, returns the current record number.

INTEGER

Fileunit number

LINESLEFT

The number of lines left on the page.

INTEGER

Fileunit number

LSIZE

For a file that is open for writing, the line length for the standard Oracle OLAP page heading. (See the STDHDR program.) For a fileunit that is open for reading, specifies the record length for binary input files.

INTEGER

Fileunit number

NLS_CHARSET

The character set being used for this fileunit. See the FILEOPEN function for more information.

TEXT

Fileunit number

NUMBYTES

The size of the file in bytes.

INTEGER

Fileunit number or file name

ORIGIN

The type of computer on which the file was created. The ORIGIN attribute, which is relevant only for files that are open for reading, is set when you issue a FILESET statement.

TEXT

Fileunit number

PAGENUM

The current page number. See "Paging Attributes".

INTEGER

Fileunit number

PAGEPRG

The Oracle OLAP program or statement that produces headings when output is paged. See "Paging Attributes".

TEXT

Fileunit number

PAGESIZE

The number of lines on each page. See "Paging Attributes".

INTEGER

Fileunit number

PAGING

TRUE when the output is formatted in pages; FALSE when it is not. See "Paging Attributes".

BOOLEAN

Fileunit number

PAUSEATPAGEEND

TRUE when Oracle OLAP pauses after each page; FALSE when it does not. See "Paging Attributes".

BOOLEAN

Fileunit number

R[EAD]

TRUE when the file is open for reading; FALSE when it is not.

BOOLEAN

Fileunit number

RO

TRUE when the file's read-only attribute is set; FALSE when it is not.

BOOLEAN

Fileunit number or file name

TABEXPAND

TRUE when the tab characters are expanded when the file is read by FILEGET or FILEREAD; FALSE when they are not. See "Tab Treatment".

BOOLEAN

Fileunit number or file name

TMARGIN

The number of blank lines that form the top margin.

INTEGER

Fileunit number

UNIT

The file unit for the specified file name.

INTEGER

File name

W[RITE]

TRUE when the file is open for writing; FALSE when it is not.

BOOLEAN

Fileunit number


Notes

Tab Treatment

When you want tab characters in the source file to be expanded when read by FILEGET or FILEREAD, you can specify the TABEXPAND attribute with the FILESET command. When TABEXPAND is zero, tab characters are not expanded. A value greater than 0 indicates the distance, in bytes, between tab stops. The default value of TABEXPAND is 8.

Paging Attributes

The paging attributes apply only to files that currently, unless otherwise noted, have PAGING set to YES and are open in WRITE mode -- such as files opened with FILEOPEN(...WRITE) or FILEOPEN(...APPEND). You can set any of the paging attributes with the FILESET command.

Wildcard Characters

(UNIX only) When querying for UNIX file names, wildcard characters (that is, * ?) are allowed when searching with the EXISTS attribute argument.

Examples

Example 6-93 Setting Paging Options for a File Opened for Writing

The following statements show how the paging options are set for a file opened for writing.

DEFINE fil.unit INTEGER
fil.unit = FILEOPEN('REPORT' WRITE)
  • The statement

    SHOW FILEQUERY(fil.unit PAGING)
    

    produces the following output.

    YES
    
  • The statement

    SHOW FILEQUERY(fil.unit PAGESIZE)
    

    produces the following output.

    66
    
  • The statement

    SHOW FILEQUERY(fil.unit TMARGIN)
    

    produces the following output.

    5
    

The following statement closes the file.

FILECLOSE fil.unit

FILTERLINES

The FILTERLINES function applies a filter expression that you create to each line of a multiline text expression.

Return Value

TEXT or NTEXT

This function accepts TEXT values and NTEXT values as arguments. The data type of the return value depends on the data type of the values specified for the arguments:

Syntax

FILTERLINES(source-expression filter-expression)

Arguments

source-expression

A multiline text expression whose lines should be modified according to filter-expression.

filter-expression

An expression to be applied as a filter to each line of source-expression. The terms of the filter expression dictate the processing that FILTERLINES performs on each line of the source expression.

The filter expression may produce NA, which means that there is no line in the resulting text expression corresponding to the current line of the source expression.

You can use the keyword VALUE in your filter expression to represent the current line of the source expression.

Notes

The Result of FILTERLINES

FILTERLINES returns a text expression composed of the lines that result from the action of the filter expression on each line of the source expression. The filter expression may return multiline text for any or all of the input source lines. None of these lines are acted on again by the filter expression.

Examples

Example 6-94 Removing Extension From File Names

The following example shows how FILTERLINES could be used on a list of file names to produce a list of those same file names without extensions.

With a multiline text variable named filelist that evaluates to

myfile1.txt
file2.txt
myfile3
file4.txt

the statement

SHOW FILTERLINES(FILELIST -
   IF FINDCHARS(VALUE '.') GT 0 -
      THEN EXTCHARS(VALUE 1 FINDCHARS(VALUE '.') -1) -
      ELSE VALUE)

produces the following output.

myfile1
file2
myfile3
file4

FINDBYTES

The FINDBYTES function returns the byte position of the beginning of a specified group of bytes within a text expression.

Tip:

When you are using a single-byte character set, you can use the FINDCHARS function instead of the FINDBYTES function.

Return Value

INTEGER

Syntax

FINDBYTES(text-expressionbytes [starting-pos [LINENUM]])

Arguments

text-expression

The TEXT expression in which you are searching for the specified bytes. The value of text-expression can be a multiline value. In this case, FINDBYTES searches all lines for the specified bytes. The match must be exact, including a match of upper- and lowercase characters.

Tip:

When you must use this function on NTEXT values, use the CONVERT or TO_CHAR function to convert the NTEXT value to TEXT.
bytes

The group of bytes for which you are searching. When bytes is a multiline value, FINDBYTES ignores all lines except the first one.

When bytes is not found in text-expression, FINDBYTES returns zero. When the group of bytes occurs more than once, FINDBYTES returns the position of its first occurrence.

starting-pos

An INTEGER expression that specifies the byte position where the search in text-expression should start. The default is at position 1 (the first byte) in text-expression.

LINENUM

Specifies that FINDBYTES should return the line number instead of the byte position of the beginning of the specified text.

Examples

Example 6-95 Finding the Starting Position of a Byte Group

This example shows how to find the starting position of various groups of bytes in the literal TEXT value hellotherejoe.

The statement

SHOW FINDBYTES('hellotherejoe', 'joe')

produces the following output.

11

The statement

SHOW FINDBYTES('hellotherejoe', 'al')

produces the following output.

0 

FINDCHARS

The FINDCHARS function returns the character position of the beginning of a specified group of characters within a text expression.

Tip:

When you are using a multibyte character set, you can use the FINDBYTES function instead of the FINDCHARS function.

Return Value

INTEGER

Syntax

FINDCHARS(text-expressioncharacters [starting-pos [LINENUM]])

Arguments

text-expression

The text expression in which you are searching for the specified characters. Text-expression can be a multiline value. In this case, FINDCHARS searches all lines for the specified characters. The match must be exact, including a match of upper- and lowercase characters.

FINDCHARS accepts TEXT values and NTEXT values as arguments. When only one argument is NTEXT, then FINDCHARS automatically converts the other argument to NTEXT before performing the function operation

characters

The group of characters for which you are searching. When characters is a multiline value, FINDCHARS ignores all lines except the first one.

When characters is not found in text-expression, FINDCHARS returns zero. When the group of characters occurs more than once, FINDCHARS returns the position of its first occurrence.

starting-pos

An INTEGER expression that specifies the character position where the search in text-exp should start. The default is at position 1 (the first character) in text-exp.

LINENUM

Specifies that FINDCHARS should return the line number instead of the character position of the beginning of the specified text.

Examples

Example 6-96 Finding the Starting Position of a Character Group

This example shows how to find the starting position of various groups of characters in the literal TEXT value hellotherejoe.

The statement

SHOW FINDCHARS('hellotherejoe', 'joe')

produces the following output.

11

The statement

SHOW FINDCHARS('hellotherejoe', 'al')

produces the following output.

0 

FINDLINES

The FINDLINES function determines the position of one or more lines in a multiline text expression.

Return Value

INTEGER

Syntax

FINDLINES(text-expressionlines)

Arguments

text-expression

A text expression within whose values you want to locate a certain line or group of lines. FINDLINES searches text-expression for the specified lines. The match must be exact, including a match of uppercase and lowercase characters. Also, when you specify two or more lines, FINDLINES searches for all the specified lines as a single continuous block in text-expression. When all the lines occur in text-expression, but are not in a continuous block, FINDLINES returns 0 (not found).

FINDLINES accepts TEXT values and NTEXT values as arguments. When only one argument is NTEXT, then FINDLINES automatically converts the other argument to NTEXT before performing the function operation.

Note that when the value of text-expression is NA, FINDLINES returns NA.

lines

A second text expression containing the line(s) for which you are searching. When lines is not found in text-expression, FINDLINES returns 0. When lines occurs more than once, FINDLINES returns the line number of its first occurrence.

Examples

Example 6-97 Finding Two Sequential Lines

This example shows how to find the location of the two lines "products" and "services" in a multiline value in a TEXT variable called newlist. The newlist variable has the following values.

salespeople
products
services
regions
priorities

The characters "\n" in the lines argument to the following FINDLINES function call indicates a line break to show that "product" and "services" are separate lines.

SHOW FINDLINES(newlist, 'products\nservices')

The result of this statement is

2 

FINTSCHED

The FINTSCHED function calculates the interest portion of the payments on a series of fixed-rate installment loans that are paid off over a specified number of time periods. For each time period, you specify the amount of the loans incurred during that time period and a single interest rate that applies to those loans over their lifetime.

FINTSCHED calculates the result for a given time period as the sum of the interest due on each loan that is incurred or outstanding in that period.

Return Value

DECIMAL

The result returned by the FINTSCHED function is dimensioned by the union of all the dimensions of loans, rates, n, and the dimension used as the time-dimension argument.

Syntax

FINTSCHED(loansratesn, [time-dimension] [STATUS])

Arguments

loans

A numeric expression that contains the initial amounts of the loans. When loans does not have a time dimension, or when loans is dimensioned by multiple time dimensions, the time-dimension argument is required.

rates

A numeric expression that contains the interest rates charged for loans. When rates is a dimensioned variable, it can be dimensioned by any dimension, including a different time dimension. When rates is dimensioned by a time dimension, you specify the interest rate in each time period that applies to the loans incurred in that period. The interest rate for the time period in which a loan is incurred applies throughout the lifetime of that loan. The rates are expressed as decimal values; for example, a 5 percent rate is expressed as.05.

n

A numeric expression that specifies the number of payments required to pay off the loans in the series. The n expression can be a dimensioned variable, but it cannot be dimensioned by the time dimension argument. One payment is made in each time period of the time dimension by which loans is dimensioned or in each time period of the dimension specified in the time-dimension argument. For example, one payment is made each month when loans is dimensioned by MONTH.

time-dimension

The name of the dimension along which the interest payments are calculated. When the time dimension has a type of DAY, WEEK, MONTH, QUARTER, or YEAR, the time-dimension argument is optional, unless loans has multiple time dimensions.

STATUS

Specifies that FINTSCHED should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the interest portion of the payments. By default FINTSCHED uses the default status list.

Notes

FINTSCHED and NA Values

When loans has a value other than NA and the corresponding value of rates is NA, an error occurs.

FINTSCHED is affected by the NASKIP option. When NASKIP is set to YES (the default), and a loan value is NA for the affected time period, the result returned by FINTSCHED depends on whether the corresponding interest rate has a value of NA or a value other than NA. Table 6-11, "Effect of NASKIP When Loan or Rate Values are NA for a Time Period" illustrates how NASKIP affects the results when a loan or rate value is NA for a given time period.

Table 6-11 Effect of NASKIP When Loan or Rate Values are NA for a Time Period

Loan Value Rate Value Result When NASKIP = YES Result When NASKIP = NO

Non-NA

NA

Error

Error

NA

Non-NA

Interest values

(NA loan value is treated as zero)

NA for the affected time periods

NA

NA

NA for affected time periods

NA for the affected time periods


As an example, suppose a loan expression and a corresponding interest expression both have NA values for 1997 but both have values other than NA for succeeding years. When the number of payments is 3, FINTSCHED returns NA for 1997, 1998, and 1999. For 2000, FINTSCHED returns the interest portion of the payment due for loans incurred in 1998, 1999, and 2000.

FINTSCHED Ignores the Status of the Time Dimension

The FINTSCHED calculation begins with the first time dimension value, regardless of how the status of that dimension may be limited. For example, suppose loans is dimensioned by year, and the values of year range from Yr95 to Yr99. The calculation always begins with Yr95, even when you limit the status of year so that it does not include Yr95.

However, when loans is not dimensioned by the time dimension, the FINTSCHED calculation begins with the first value in the current status of the time dimension. For example, suppose loans is not dimensioned by year, but year is specified as time-dimension. When the status of year is limited to Yr97 to Yr99, the calculation begins with Yr97 instead of Yr95.

Examples

Example 6-98 Calculating Interest

The following statements create two variables called loans and rates.

DEFINE loans DECIMAL <year>
DEFINE rates DECIMAL <year>

Suppose you assign the following values to the variables loans and rates.

YEAR             LOANS      RATES
-------------- ---------- ----------
Yr95               100.00       0.05
Yr96               200.00       0.06
Yr97               300.00       0.07
Yr98                 0.00       0.00
Yr99                 0.00       0.00

For each year, loans contains the initial value of the fixed-rate loan incurred during that year. For each year, the value of rates is the interest rate that is charged for any loans incurred in that year; for those loans, this same rate is charged each year until the loans are paid off.

The following statement specifies that each loan is to be paid off in three payments, calculates the interest portion of the payments on the loans,

REPORT W 20 HEADING 'Payment' FINTSCHED(loans, rates, 3, year)

and produces the following report.

YEAR                        Payment
--------------   --------------------
Yr95                           5.00
Yr96                          15.41
Yr97                          30.98
Yr98                          18.70
Yr99                           7.48

The interest payment for 1995 is interest on the loan of $100 incurred in 1995, at 5 percent. The interest payment for 1996 is the sum of the interest on the remaining principal of the 1995 loan, at 5 percent, plus interest on the loan of $200 incurred in 1996, at 6 percent. The 1997 interest payment is the sum of the interest on the remaining principal of the 1995 loan, at 5 percent; interest on the remaining principal of the 1996 loan, at 6 percent; and interest on the loan of $300 incurred in 1997, at 7 percent. Since the 1995 loan is paid off in 1997, the payment for 1998 represents interest on the remaining principal of the 1996 and 1997 loans. In 1999, the interest payment is on the remaining principal of the 1997 loan.


FLOOR

The FLOOR function returns the largest whole number equal to or less than a specified number.

Return Value

NUMBER

Syntax

FLOOR(n)

Arguments

n

A number.

Examples

Example 6-99 Displaying the Largest Integer Equal to or Less Than a Number

The following statements show results returned by the FLOOR function.

  • The following SHOW FLOOR statement produces the result that follows it.

    SHOW FLOOR(15.7)
    
    15
    
  • The following SHOW FLOOR statement produces the result that follows it.

    SHOW FLOOR(4)
    
    4
    
  • The following SHOW FLOOR statement produces the result that follows it.

    SHOW FLOOR(-6.457)
    
    -7
    

FPMTSCHED

The FPMTSCHED function calculates a payment schedule (principal plus interest) for paying off a series of fixed-rate installment loans over a specified number of time periods. For each time period, you specify the amount of the loans incurred during that time period and a single interest rate that applies to those loans over their lifetime.

FPMTSCHED calculates the payment for a given time period as the sum of the principal and interest due on each loan that is incurred or outstanding in that period.

Return Value

DECIMAL

The result returned by the FPMTSCHED function is dimensioned by the union of all the dimensions of loans and rates and the dimension used as the time-dimension argument.

Syntax

FPMTSCHED(loansratesn, [time-dimension] [STATUS])

Arguments

loans

A numeric expression that contains the initial amounts of the loans. When loans does not have a time dimension, or when loans is dimensioned by multiple time dimensions, the time-dimension argument is required.

rates

A numeric expression that contains the interest rates charged for loans. When rates is a dimensioned variable, it can be dimensioned by any dimension, including a different time dimension. When rates is dimensioned by a time dimension, you specify the interest rate in each time period that applies to the loans incurred in that period. The interest rate for the time period in which a loan is incurred applies throughout the lifetime of that loan. The rates are expressed as decimal values; for example, a 5 percent rate is expressed as.05.

n

A numeric expression that specifies the number of payments required to pay off the loans in the series. The n expression can be dimensioned, but it cannot be dimensioned by the time dimension argument. One payment is made in each time period of the time dimension by which loans is dimensioned or in each time period of the dimension specified in the time-dimension argument. For example, one payment each month is made when loans is dimensioned by month.

time-dimension

The name of the dimension along which the interest payments are calculated. When the time dimension for loans has a type of DAY, WEEK, MONTH, QUARTER, or YEAR, the time-dimension argument is optional, unless loans has multiple time dimensions.

STATUS

Specifies that FPMTSCHED should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the payment schedule. By default FPMTSCHED uses the default status list.

Notes

FPMTSCHED and NA Values

When loans has a value other than NA and the corresponding value of rates is NA, an error occurs.

FPMTSCHED is affected by the NASKIP option. When NASKIP is set to YES (the default), and a loan value is NA for the affected time period, the result returned by FPMTSCHED depends on whether the corresponding interest rate has a value of NA or a value other than NA. Table 6-11, "Effect of NASKIP When Loan or Rate Values are NA for a Time Period" illustrates how NASKIP affects the results when a loan or rate value is NA for a given time period.

As an example, suppose a loan expression and a corresponding interest expression both have NA values for 1997 but both have values other than NA for succeeding years. When the number of payments is 3, FPMTSCHED returns NA for 1997, 1998, and 1999. For 2000, FPMTSCHED returns the payment due for loans incurred in 1998, 1999, and 2000.

FPMTSCHED Ignores the Status of the Time Dimension

The FPMTSCHED calculation begins with the first time dimension value, regardless of how the status of that dimension may be limited. For example, suppose loans is dimensioned by year, and the values of year range from Yr95 to Yr99. The calculation always begins with Yr95, even when you limit the status of year so that it does not include Yr95.

However, when loans is not dimensioned by the time dimension, the FPMTSCHED calculation begins with the first value in the current status of the time dimension. For example, suppose loans is not dimensioned by year, but year is specified as time-dimension. When the status of year is limited to Yr97 to Yr99, the calculation begins with Yr97 instead of Yr95.

Examples

Example 6-100 Calculating a Payment Schedule

The following statements create two variables called loans and rates.

DEFINE loans DECIMAL <year>
DEFINE rates DECIMAL <year>

Suppose you assign the following values to the variables loans and rates.

year             loans         rates
-------------- ---------- ----------
Yr95               100.00       0.05
Yr96               200.00       0.06
Yr97               300.00       0.07
Yr98                 0.00       0.00
Yr99                 0.00       0.00

For each year, loans contains the initial value of the fixed-rate loan incurred during that year. For each year, the value of rates is the interest rate that is charged for any loans incurred in that year; for those loans, this same rate is charged each year until the loans are paid off.

The following statement specifies that each loan is to be paid off in three payments, calculates the schedule for paying off the principal and interest on the loans,

REPORT W 20 HEADING 'Payment' FPMTSCHED(loans, rates, 3, year)

and produces the following report.

YEAR                        Payment
-------------- --------------------
Yr95                          36.72
Yr96                         111.54
Yr97                         225.86
Yr98                         189.14
Yr99                         114.32

The payment for 1995 is the principal due on the loan of $100 incurred in 1995, plus interest on the loan at 5 percent. The payment due in 1996 is the sum of the second payment on the loan incurred in 1995 (principal plus 5 percent interest), plus the first payment on the loan of $200 incurred in 1996 (principal plus 6 percent interest). The 1997 payment is the sum of the third and final payment on the loan incurred in 1995, the second of the three payments on the 1996 loan, and the first payment on the loan of $300 incurred in 1997 (principal plus 7 percent interest). Since the 1995 loan is paid off in 1997, the payment for 1998 covers the principal and interest for the 1996 and 1997 loans. The payment for 1999 is the final payment of principal and interest for the 1997 loan.

Example 6-101 Determining Monthly Payments

The following statement determines what the monthly payments would be on a $125,000 loan with an 8.75 percent annual interest rate,

SHOW FPMTSCHED(125000, .0875/12, 360, month)

and produces the following output.

983.38

FROM_TZ

The FROM_TZ function converts a timestamp value and a time zone to a TIMESTAMP_TZ value.

Return Values

TIMESTAMP_TZ

Syntax

FROM_TZ (timestamp_value , time_zone_value)

Arguments

timestamp_value

A text expression with a TIMESTAMP data type.

time_zone_value

A text expression that returns a string in the format TZH:TZM or in TZR with optional TZD format.

See also:

See "Datetime Expressions" for information on specifying timestamp and time zone values.

Examples

Example 6-102 Creating a TIMESTAMP_TZ Value from a Timestamp Value and a Time Zone

DEFINE mytimestamp VARIABLE TIMESTAMP
DEFINE mytimezone VARIABLE TEXT
DEFINE mytimestamptz VARIABLE TIMESTAMP_TZ
mytimestamp = '26-MAR-06'
mytimezone = '-04:00'
mytimestamptz = FROM_TZ (mytimestamp mytimezone)
REPORT mytimestamptz
 
MYTIMESTAMPTZ
------------------------------
  26-MAR-06 12.00.00 AM -04:00

GET

The GET function requests input from the current input stream. The input may be a single item of data, a dimension value, an analytic workspace object, or simply the next item in the input stream. The simplest form of the GET function requests a value of a certain data type.

GET(datatype)


GET also provides several arguments that verify the input.

Because GET is a function, it must be used in an OLAP DML command. It also may be used in an assignment statement to store the input in a variable for later use, or in a LIMIT command to set the status of a dimension. GET can be used in programs to request information necessary for the completion of the program.

Return Value

The return value depends on the input that you request, as described in the syntax.

Syntax

GET({RAW TEXT|[NEW|VALID|POSLIST] input} -

     [VERIFY condition-exp [IFNOT result-exp]])

where input is one of the following:

dim-name     
NAME
datatype

Arguments

dim-name

A text expression specifying the name of a dimension. When you specify dim-name, GET requests a value of this dimension as input and verifies that the input is a valid value of the dimension.

RAW TEXT

Specifies that GET should return the next item in the input stream exactly as it is entered. See "GET with RAW TEXT".

NEW dim-name

The NEW keyword with the dim-name argument causes GET to request a new value for the dimension. When requesting a dimension value with NEW, GET verifies that the input is not already a value of the dimension.

VALID dim-name

The VALID keyword with the dim-name argument causes GET to request either a new value or an existing value of the dimension. When requesting a dimension value with VALID, GET verifies that the input is either an existing dimension value or a valid new dimension value.

POSLIST dim-name

The POSLIST keyword with the dim-name argument causes GET to request a dimension value identified by its position in the dimension. When requesting a dimension value with POSLIST, GET verifies that the input is an existing position number in the dimension. See "GET with POSLIST".

NAME

Indicates that GET is requesting the name of an object in the current analytic workspace. When you specify NAME, GET verifies that the input is an object that exists in the current analytic workspace. The object name must not be enclosed in single quotes, and it must follow the rules for valid object names explained in the main DEFINE entry. GET automatically converts the object name to uppercase.

NEW NAME

The NEW NAME keywords cause GET to request a name for a new analytic workspace object. When requesting an analytic workspace object name with NEW, GET verifies that the input is not already the name of an object in any attached analytic workspace (including EXPRESS.DB).

VALID NAME

The VALID NAME keywords cause GET to request a name for an analytic workspace object. When requesting an analytic workspace object name with VALID, GET verifies that the input follows the rules for valid object names, even when there is no current analytic workspace and regardless of whether the name exists.

POSLIST NAME

The POSLIST NAME keywords cause GET to request an analytic workspace object name identified by its position in the NAME dimension. When requesting an analytic workspace object name with POSLIST, GET verifies that the input is an existing position number in the NAME dimension.

datatype

Specifies the type of data being requested by GET which can be any of the Oracle OLAP data types: INTEGER, SHORTINTEGER, DECIMAL, SHORTDECIMAL, BOOLEAN, ID, TEXT, or DATE. GET accepts a value of NA when requesting any data type.

VERIFY condition-exp [IFNOT result-exp]

With VERIFY, you can specify a Boolean condition that must be satisfied by the input to GET. The keyword VALUE may be used in condition-exp to test the input before any assignment is made. For example, when requesting a value of LSIZE, the Boolean condition might be as follows.

VALUE NE NA AND VALUE GE 1 AND VALUE LE 80

The IFNOT clause specifies a text expression to provide for occasions when the input does not satisfy condition-exp. For example, you might jump to an error-handling routine in your program. When you do not use IFNOT and an error occurs, GET produces an error message and then resumes waiting for input.

Notes

Current Input Stream

Oracle OLAP obtains statements for processing from the current input stream. You can override your default input stream with an INFILE statement. INFILE causes Oracle OLAP to read input from a file. Each line of the infile must contain a single statement.

Input from INFILE

When the GET function is in an infile, Oracle OLAP considers the next line in the infile to be the input to GET. You must be sure you supply the expected input for GET in the line or lines following the statement that invokes the GET function.

For example, suppose your infile contains a line invoking a report program that calls GET to obtain the number of decimal places to use. The infile then continues with other statements. When you do not put the desired number of decimal places on the line following the program call, GET examines line after line in the infile looking for the expected numeric response, rather than executing those lines as statements. See "Using GET to Obtain Textual Value".

INTEGER Dimension Values

When GET requests a value of an INTEGER dimension, the input should usually be in the form of a dimension-value position number

Non-INTEGER Dimension Values

Non-integer dimension values must be entered in uppercase and enclosed in single quotes.

Entering Values for DWMQY Dimensions

Values of DAY, WEEK, MONTH, QUARTER, or YEAR dimensions may be entered in the format of the dimension's VNF (or in the format of the default VNF when the dimension does not have a VNF of its own) or as a date. See the VNF command for an explanation of how to enter values in a VNF format. See "Date-only Input Values" for an explanation the valid input styles for entering values as dates.

Whether you use the VNF format or specify the value as a date, you must specify only the date components that are relevant for this type of time dimension. For example, for a MONTH dimension, you must supply only the month and year.

TEXT or ID Values

TEXT and ID values provided as input to GET retain the case in which they were entered. You do not have to enclose TEXT and ID values in quotes unless they begin with single or double quotes, or contain embedded blanks or escape sequences, such as \dnnn or \n. (Remember to precede any single quote in the value with a backslash (\') so Oracle OLAP interprets it literally.)

DATE-only Values

When GET requests a DATE value, you can provide the input in any of the valid styles for dates, as explained in "Date-only Input Values". Oracle OLAP uses the current value of the DATEORDER option to resolve any ambiguity in the DATE-only value.

Numeric Values

GET rounds a SHORTDECIMAL or DECIMAL value when converting it into an INTEGER value. When GET requests an INTEGER or SHORTINTEGER value and the input is a number beyond the range for that data type, GET produces an error message and resumes waiting for input.

GET with RAW TEXT

When GET requests RAW TEXT input and no input is provided, GET returns a null string (''). For any type of information other than RAW TEXT, GET waits until input is provided.

GET with POSLIST

When you use the POSLIST keyword with the GET function, Oracle OLAP requires that you enter a position value to identify the dimension value rather than the dimension name. The syntax for the POSLIST keyword depends on whether you are using the GET function with either an assignment statement created using an assignment statement or the LIMIT command. When you want to set a variable equal to the result of a GET function, use the following syntax.

expression = GET(POSLIST dimension)

When you want to limit a dimension to a value returned by a GET function, you specify the POSLIST keyword twice, as shown in the following syntax.

LIMIT dimension TO POSLIST GET(POSLIST dimension)

Examples

Example 6-103 Using GET to Obtain Textual Value

Suppose you have written an Oracle OLAP program called myconn. This program contains a call to GET that requests a textual value.

DEFINE myconn PROGRAM
PROGRAM
...
MYTEXT = GET(TEXT)
...
END

GREATEST

The GREATEST function returns the largest expression in a list of expressions. All expressions after the first are implicitly converted to the data type of the first expression before the comparison.

To retrieve the smallest expression in a list of expressions, use LEAST.

Return Value

The data type of the first expression.

Syntax

GREATEST (expr [, expr]...)

Arguments

expr

An expression.

Examples

Example 6-104 Finding the Text Expression that is Last Alphabetically

The following statement selects the string that is last in alphabetic sequence.

SHOW GREATEST ('Harry', 'Harriot', 'Harold')
Harry

Example 6-105 Finding the Largest Numerical Expression

The following statement selects the number with the greatest value.

SHOW GREATEST (5, 3, 18)
18

GROUPINGID function

The GROUPINGID function retrieves a grouping id for the value of a hierarchical dimension using a grouping relation previously created by the GROUPINGID command.

Return Values

NUMBER

Syntax

GROUPINGID (gidrel...)

Arguments

gidrel

A grouping id relation for the hierarchical dimension that you previously created using the GROUPINGID command.

Examples

Example 6-106 Retrieving the Value of a Single GroupingID

Assume that you have use the GROUPINGID command to define grouping ids for the two hierarchies in the geog dimension as described in Example 8-144, "Using GROUPINGID Command to Populate a Relation with Grouping Ids". Now you can use the GROUPINGID function to retrieve the grouping id of a value in the geog dimension.

" For the Political Geog hierarchy
LIMIT geog TO 'Hartford'
LIMIT geog_hierlist TO 'Political_Geog'
SHOW GROUPINGID(geog_gidrel)
0.00
SHOW OBJ(PROPERTY '$GID_DEPTH' 'geog_gidrel')
4
LIMIT geog TO ALL
LIMIT geog TO 'Canada'
SHOW GROUPINGID(geog_gidrel)
3.00
SHOW OBJ(PROPERTY '$GID_DEPTH' 'geog_gidrel')
4
 
" For the Sales Geog hierarchy
LIMIT geog TO 'Hartford'
LIMIT geog_hierlist TO 'Sales_Geog'
SHOW GROUPINGID(geog_gidrel)
0.00
SHOW OBJ(PROPERTY '$GID_DEPTH' 'geog_gidrel')
4
LIMIT geog TO ALL
LIMIT geog TO 'West'
SHOW GROUPINGID(geog_gidrel)
3.00
SHOW OBJ(PROPERTY '$GID_DEPTH' 'geog_gidrel')
4

GROWRATE

The GROWRATE function calculates the growth rate of a time-series expression, based on the first and last values of the series.

GROWRATE bases its calculation on the values of expression that correspond to the first and last values in the status of time-dimension. The intervening values of expression are ignored. GROWRATE uses the following calculation.

GROWRATE = ((last/first)**(1/(n-1))-1

In the exponent, n is the number of values in the status of the time dimension.

Return Value

DECIMAL

The result returned by GROWRATE is dimensioned by all the dimensions of expression except the dimension specified by time-dimension.

Syntax

GROWRATE(expression [time-dimension])

Arguments

expression

A numeric expression for which you want to calculate the growth rate. The expression must be dimensioned by a time dimension.The following rules apply to the first and last values of expression:

  • The first value of expression cannot be zero. (This is to avoid a division by zero in the GROWRATE calculation.)

  • The first and last values of expression must both be positive or both negative. (Or the last value of expression can be zero, regardless of whether the first value is positive or negative.)

  • Neither the first value nor the last value of expression can be NA.

time-dimension

The name of the time dimension by which expression is dimensioned. When the time dimension has a type of DAY, WEEK, MONTH, QUARTER, or YEAR, the time-dimension argument is optional, unless loans has multiple time dimensions.

Examples

Example 6-107 Determining Growth Rate

The following statements limit the dimensions of the actual variable and produce a report.

LIMIT month TO 'Dec95' TO 'Mar96'
LIMIT line TO 'net.income'
REPORT DOWN division ACROSS month: actual

These statements produce the following report.

LINE: NET.INCOME
               ------------------ACTUAL-------------------
               -------------------MONTH-------------------
DIVISION         Dec95      Jan96      Feb96      Mar96
-------------- ---------- ---------- ---------- ----------
Camping          4,378.09  19,915.13  22,510.38  34,731.63
Sporting         6,297.02  13,180.29  17,429.17  18,819.14
Clothing        87,471.74 107,257.85 133,566.01 127,132.55

The statement REPORT W 20 GROWRATE(actual)produces a report that shows the growth rate of the actual net income in the demo workspace between December 1995 and March 1996.

--GROWRATE(ACTUAL)--
               --------LINE--------
DIVISION            NET.INCOME
-------------- --------------------
Camping                        0.99
Sporting                       0.44
Clothing                       0.13

HEXTORAW

The HEXTORAW function converts a character string of hexadecimal digits to a raw value.

See also:

"RAW Data Type" and the RAWTOHEX function.

Returns

RAW

Syntax

HEXTORAW(text-exp)

Arguments

text-exp

A text expression containing hexadecimal digits.


HIERCHECK

The HIERCHECK function checks the hierarchy in the specified relation or all of the relations of the specified aggmap to see if there is any circularity. A hierarchical dimension's parent relation specifies the parent for each of the dimension's values. (Circularity occurs when a dimension value has inadvertently been specified as its own ancestor or descendant in the parent relation. )

You can also specify that HIERCHEK check the hierarchy for other conditions.

See also:

HIERSHAPE function

Return Value

BOOLEAN

Syntax

As Command

HIERCHECK parent-relation [STATUS|NOSTATUS|valueset-name] [MULTIPATH] [CONSISTENT]-

[BALANCED levelrelation-name]

or

HIERCHECK aggmap-name [MULTIPATH] [CONSISTENT]levelrelation-name]

Arguments

parent-relation

A text expression indicating the name of the parent relation to be checked.

aggmap-name

A text expression indicating the name of the aggmap. HIERCHECK checks all of the relations in the aggmap.

STATUS

Specifies that HIERCHECK uses the current status of the relation dimension.

valueset

Specifies the values of the relation dimension that HIERCHECK considers in status.

NOSTATUS

Specifies that HIERCHECK uses the default status of the relation dimension.

MULTIPATH

Specifies that HEIRCHECK checks whether there are multiple paths from any child to its parent.

CONSISTENT

Specifies that HIERHECK checks whether the hierarchy is consistent. If the hierarchy is consistent, that means all nodes in the different hierarchies should have the same children.

BALANCED levelrel-name

Using the level relation identified by levelrel-name, specifies that HIERHECK checks to see if all of the following are true:

  • All of the elements of a hierarchy which have an NA level are either roots with no leaves or leaves.

  • All of the elements of a hierarchy at the same (non NA) level have the same depth from the root (roots) of the hierarchy.

  • Elements of a hierarchy for different levels (non NA) have a different depth.

Notes

Why Use HIERCHECK

It is a good strategy to use HIERCHECK at the time you build your hierarchies as a way to verify that they are valid. In other words, do not attempt to roll up a variable's data unless you have verified that its dimensions' hierarchies are structured correctly. For example, the AGGREGATE command uses HIERCHECK to prevent infinite looping once the statement has been executed. Check a parent relation for loops after you set up the levels of a hierarchical dimension, before you load data into any variable that is dimensioned by the hierarchical dimension, or before you use the AGGREGATE command for the first time with a variable. Although it is possible to roll up a variable without first having checked the parent relations of all of its hierarchical dimensions with HIERCHECK, make it a practice to use HIERCHECK first.

Status When Using HEIRCHECK with an Aggmap

When there is any valueset inside a relation in aggmap, HIERCHECK uses this valueset to determine the status of the dimension of the relation. In all other cases, HIERCHECK uses the default status of the relation dimension.

For all dimensions other than relation dimensions, HIERCHECK uses the current status of the dimension.

Error Messages Triggered by HIERCHECK

When you use HIERCHECK, it signals an error when it finds a loop in the parent relation and stops execution (that is, HIERCHECK always stops in the first error message). The error message identifies the dimension values that are involved in the loop, the name of the hierarchy (referred to as the "extra dimension values") in which the loop occurs (when the parent relation has one or more named hierarchies), and the name of the parent relation in which the loop was found. When a parent relation has no loops, no message is displayed. See Example 6-108, "Checking for Loops".

Examples

Example 6-108 Checking for Loops

This example shows how to create a parent relation and check it for loops. You would begin by defining a dimension and adding values to it.

DEFINE geography DIMENSION ID
MAINTAIN geography ADD 'U.S.'
MAINTAIN geography ADD 'East' 'Central' 'West'
MAINTAIN geography ADD 'Boston' 'Atlanta' 'Chicago' 'Dallas' 'Denver' 'Seattle'

Next, relate the dimension to itself. The following statement defines a parent relation called GEOG.GEOG, which relates the GEOGRAPHY dimension to itself.

define geog.geog RELATION geography <geography>

You would then specify the hierarchy of the dimension values. In this example, there are three levels in the hierarchy: country, regions, and cities. When you specify the hierarchy, you assign parent dimension values (such as East) to child dimension values (such as Boston) for every level except the highest level. To do this, you store values in the relation. First, group the children with a LIMIT command, then assign a parent to those children.

LIMIT geography TO 'East' 'Central' 'West'
geog.geog = 'U.S.'
LIMIT geography TO 'Boston' 'Atlanta'
geog.geog = 'East'
LIMIT geography TO 'Chicago' 'Dallas'
geog.geog = 'Central'
LIMIT geography TO 'Denver' 'Seattle'
geog.geog = 'West'

Now you can check for loops in the parent relation geog.geog, as shown by the following statement.

HIERCHECK geog.geog

In this case, HIERCHECK produces no message output, which means there are no loops in geog.geog. It sets HIERCHK.LOOPFND to NO, and leaves HIERCHK.LOOPVALS and HIERCHK.XTRADIMS set to NA.

Now suppose the following mistake had been made in the storing of values in the relation.

LIMIT geography TO 'East' 'Central' 'West'
geog.geog = 'East'

The preceding statements inadvertently make East its own parent, which would cause an aggregation to loop infinitely. When you now check the geog.geog relation for loops, the following statement produces the following error message.

HIERCHECK geog.geog
ERROR: HIERCHECK has detected one or more loops in the hierarchy represented by GEOG.GEOG. The values involved are 'East'.

HIERHEIGHT function

The HIERHEIGHT function returns the value of a node at a specified level for the first value in the current status list of a hierarchical dimension.

To populate a previously-defined relation with the values of a specified hierarchical dimension by level, use the HIERHEIGHT command.

Return Value

The data type returned by HIERHEIGHT is the data type of the dimension value of parentrel

Syntax

HIERHEIGHT(fparentrel [,] level)

Arguments

parentrel

A child-parent self-relation for the hierarchical dimension. See "Parentrel Relation" for more information.

level

An INTEGER value that represents a level of the hierarchical dimension. The value 1 (one) represents the lowest-level of the hierarchical dimension.

Notes

Limiting the Hierarchical Dimension

The HIERHEIGHT function always returns a single value of the hierarchical dimension. When you do not limit the hierarchical dimension to a single value before calling the HIERHEIGHT function, the HIERHEIGHT function executes against the first value in the current status list of the dimension. Typically, you either limit the hierarchical dimension to a single value before you call the HIERHEIGHT function or you use the HIERHEIGHT function after a FOR statement to execute the HIERHEIGHT function for each value of the hierarchical dimension.

Examples

Example 6-109 Using HIERHEIGHT as a Simple Command

Assume that your analytic workspace has a hierarchical dimension named geography and a relation named g0.stanparent that is a self-relation of the geography values for the Standard hierarchy of geography.

DEFINE g0.newparent RELATION geography <geography>
LD Parent-child when hierarchy of geography is 1

Issuing a statement like REPORT g0.stanparent displays the values in g0.stanparent.

GEOGRAPHY          G0.STANPARENT
---------------- ----------------
World            NA
Americas         World
Canada           Americas
Toronto          Canada
Montreal         Canada
Ottawa           Canada
...              ...
USA              Americas
Boston           USA
LosAngeles       USA
...              ...
Mexico           Americas
Mexicocity       Mexico
Argentina        Americas
BuenosAires      Argentina
Brazil           Americas
Saopaulo         Brazil
Colombia         Americas
Bogota           Colombia
Australia        World
East.Aust        Australia
Sydney           East.Aust
Madrid           Spain
Budapest         Hungary
Athens           Greece
Vienna           Austria
Melbourne        East.Aust
Central.aust     Australia
...              ...
Perth            West.Aust
Bombay           India
Malaysia         Asia
Europe           World
France           Europe
Caen             France
Paris            France

Now you limit geography to the value Americas by issuing the following OLAP DML statement.

LIMIT geography TO 'Americas'

When you use the HIERHEIGHT function to find the node for Americas for the lowest-level of the hierarchy (level 1) by issuing the following OLAP DML statement.

REPORT HIERHEIGHT(g0.stanparent 1)

The following report is produced.

HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
NA

When you use the HIERHEIGHT function to find the node for Americas for the highest-level of the hierarchy (level 4) by issuing the following OLAP DML statement.

REPORT HIERHEIGHT(g0.stanparent 4)

The following report is produced.

HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
World

When you use the HIERHEIGHT function to find the node for Americas for the levels 2 and 3 of the hierarchy by issuing the following OLAP DML statements.

REPORT HIERHEIGHT(g0.stanparent 2)
REPORT HIERHEIGHT(g0.stanparent 3)

The following reports are produced.

HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
NA
 
HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
Americas

Notice that the output for each level corresponds in between the values that are created for a relation created using HIERHEIGHT command. For example, assume you created a relation named geog.stanhierrel for the standard hierarchy for geography and limit geography to 'Americas. A report of geog.stanhierrel would show the same geography values for each level.

LIMIT geography TO 'AMERICAS'
REPORT DOWN geography geog.stanhierrel

                 ---------------------------GEOG.STANHIERREL--------------------
                 ----------------------------GEOG.LVLDIM------------------------
GEOGRAPHY               1                2                3                4
---------------- ---------------- ---------------- ---------------- ------------
Americas         NA               NA               Americas         World

Example 6-110 Using HIERHEIGHT After a FOR Statement

Assume that your analytic workspace has the following program named findnodes that finds the nodes of all of the geography values in status.

DEFINE FINDNODES PROGRAM
PROGRAM
VARIABLE level INTEGER
FOR geography
DO
counter = 1
WHILE counter LE statlen(geog.lvldim)
DO
REPORT HIERHEIGHT(g0.stanparent level)
level = level + 1
DOEND
DOEND
END

Assume also that you limit geography to Americas and Asia and call the HIERHEIGHT function for each level of the Standard hierarchy by issuing the following OLAP statements.

LIMIT geography TO 'Americas', 'Asia'
CALL findnodes

The output of the findnodes program for the geography values Americas and Asia is follows. The program first reports on the value of each level for Americas is provided. Then it reports on the value of each level for Asia.

HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
NA
 
HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
NA
 
HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
Americas
 
HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
World
 
HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
NA
 
HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
NA
 
HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
Asia
 
HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
World

Notice that the output for each level corresponds in between the values that are created for a relation created using the HIERHEIGHT command

LIMIT geography TO 'Americas' 'Asia'
REPORT DOWN geography geog.stanhierrel

                 ---------------------------GEOG.STANHIERREL--------------------
                 ----------------------------GEOG.LVLDIM------------------------
GEOGRAPHY               1                2                3                4
---------------- ---------------- ---------------- ---------------- ------------
Americas         NA               NA               Americas         World
Asia             NA               NA               Asia             World

HIERSHAPE

The HIERSHAPE function identifies if a hierarchical dimension has a specified shape.

Return Value

BOOLEAN

Syntax

HIERSHAPE(parent-relation[(qdr)] {LEVEL | RAGGED | SKIPLEVEL | REGULAR} USING levelrel -

[INHIERARCHY inhvalueset] LEVELORDER levelvalueset)

Arguments

parent-relation

A text expression that is the name of the child-parent self-relation for the hierarchical dimension. (See "Parentrel Relation".)

qdr

A text expression that is the name of a QDR that qualifies parent-relation.

LEVEL

This option determines of all of the members are part of the same level as defined by the levelrel.

RAGGED

A hierarchy where leaf-nodes are located at different levels within the hierarchy.

SKIPLEVEL

A hierarchy where one or more leaf nodes link to a higher-level parent above its next most obvious level.

REGULAR

A traditional level-based hierarchy where each child has a parent at the next level up in the hierarchy.

levelrel

A text expression that is the name of the level relation for the hierarchical dimension. (See "Levelrel Relation".)

inhvalueset

A text expression that is the name of the inhier valueset for the hierarchical dimension. (See "Inhier Valueset or Variable".)

levelvalueset

A text expression that is the name of the hierlevels valueset for the hierarchical dimension. (See "Hierlevels Valueset".)

Notes

Star-consistent Hierarchies

A dimension is "star consistent" when the hierarchies it represents can be implemented as a star table (that is, when a single column defines each level so there is no partial membership in a level based on hierarchy). A dimension is "star inconsistent" when it cannot.For example, assume there is a dimension with members A, B, C, D. When some customer have a "mid" level consisting of A and B in hierarchy 1 and C and D in hierarchy 2. This dimension is star inconsistent because in a star table, you would only have a single column to represent the "mid" level and its inclusion means that both hierarchies would contain all the members of that level.


INFO

The INFO function obtains information that has been produced by a FORECAST, PARSE, or REGRESS statement or that has been produced for a model in your analytic workspace.

Because the syntax of the INFO function is different depending on the type of information being obtained, four separate entries are provided:


INFO (FORECAST)

The INFO (FORECAST) function obtains information produced by a FORECAST statement and stored internally by Oracle OLAP. Through the use of keywords, INFO lets you extract specific pieces of information about the forecast you have calculated.

Note:

Before using INFO, familiarize yourself with FORECAST.REPORT that is a standard report of its results, which may give you all the information you need. INFO is useful primarily for creating customized reports or for performing further analysis on the results.

When you try to extract information without having calculated a forecast, INFO produces an error. You can use the keyword AVAILABLE to determine whether any results are currently available.

Return Value

The return value depends on the keyword you use, as described in the tables in this entry. INFO returns NA when you use an index that is out of range or for any choice that does not apply to the forecasting method last used. For example, when your forecast formula has two coefficients and you request the twelfth one, INFO returns NA.

Syntax

INFO(FORECAST choice [index])

Arguments

FORECAST

Indicates that you want to obtain information produced by a FORECAST statement.

choice

The specific information you want. The choices available for FORECAST are listed in Table 6-12, "Choices for All Methods", Table 6-13, "Choices for TREND and EXPONENTIAL Forecasts", and Table 6-14, "Choices for WINTERS Forecasts". Choices marked as indexed require the index argument.

index

An INTEGER expression that specifies which result you want for a choice that can have several different results. For example, a trend equation might have several coefficients. You would use index to specify which coefficient you want information about. When you omit index for a choice that requires it, an error occurs.

Table 6-12 Choices for All Methods

Keyword Type Indexed? Meaning

AVAILABLE

BOOL

No

Is there a computed forecast for which to obtain information?

DEPENDENT

TEXT

No

The variable or expression being forecast.

METHOD

TEXT

No

The forecast method.

MAPE

DEC

No

The mean absolute percent error (a measure of goodness of fit).

LENGTH

INT

No

The number of forecast periods calculated.

TIME

TEXT

No

The dimension along which forecasting is performed.

FCNAME

TEXT

No

The name of the variable that contains the fitted and forecasted values (NA when no forecasts were saved).


Table 6-13 Choices for TREND and EXPONENTIAL Forecasts

Keyword Type Indexed? Meaning

FORMULA

TEXT

No

The text of the forecasting equation.

NUMCOEFS

INT

No

The number of coefficients.

COEFFICIENT

DEC

Yes

The specified coefficient in the forecasting equation; index specifies which one you want.


Table 6-14 Choices for WINTERS Forecasts

Keyword Type Indexed? Meaning

PERIODICITY

INT

No

The number of periods in a seasonal cycle.

ALPHA

DEC

No

The smoothing constant for the smoothed data series.

BETA

DEC

No

The smoothing constant for the seasonal index series.

GAMMA

DEC

No

The smoothing constant for the trend series.

STSMOOTHED

DEC

No

The starting value of the smoothed data series.

STSEASONAL

DEC

Yes

The starting values for the seasonal index series; index specifies which one you want.

STTREND

DEC

No

The starting value for the trend series.

FCSMOOTHED

TEXT

No

The variable that holds the smoothed data series.

FCSEASONAL

TEXT

No

The variable that holds the seasonal index series.

FCTREND

TEXT

No

The variable that holds the trend series.


Examples

Example 6-111 Getting Forecast Information

In this example, suppose you forecasted sales.

The following statements limit the dimensions of the sales variable, then obtain the formula for your forecast.

LIMIT product TO 'Sportswear'
LIMIT district TO 'Chicago'
LIMIT month TO 'Jan95' TO 'Dec96'
FORECAST LENGTH 12 METHOD EXPONENTIAL FCNAME fcst time -
month sales
SHOW INFO(FORECAST FORMULA)

These statements produce the following output.

87718.0009541865 * (1.005533834579 ** MONTH)

The next statement obtains the mean absolute percent error for your forecast.

SHOW INFO(FORECAST MAPE)

This statement produces the following output.

.17

INFO (MODEL)

The INFO (MODEL) function obtains information that is produced for the models in your analytic workspace and stored internally by Oracle OLAP. Through the use of keywords, INFO lets you extract specific pieces of information about the structure of a compiled model or the status of a model that you have run in your current session.

Note:

Before using INFO, familiarize yourself with the reports created by MODEL.COMPRPT, MODEL.DEPRT, and MODEL.XEQRPT that might give you all the information you need.

Use INFO with the keyword AVAILABLE to determine whether any model results are currently available. When you try to extract any other information without having considered or defined a model in your current session, INFO produces an error.

Return Value

The return value depends on the keyword you use, as described in the tables in this entry. INFO returns NA when you use an index that is out of range or when you request information that is not relevant. For example, if the model contains 5 statements and you request information about statement 6, INFO returns NA; or if you specify the DIMENSION REFERENCE choice when the assignment target is actually a variable, INFO returns NA.

Syntax

INFO(MODEL choice [index1 [index2 [index3]]])

where index is an argument specifies the result you want for a choice that can have several different results. Depending on the keyword choice, you can supply one or more of the following index arguments:

block-num
dimension-num
element-num
model-num
qualifier-num
source-num
stmnt-num

Arguments

MODEL

Indicates that you want to obtain information about a model in your analytic workspace. INFO returns information about the model that you have most recently defined or considered in the current session (see the DEFINE MODEL and CONSIDER commands).

choice

A keyword that specifies the information you want. The choices available for models are listed in the following tables that represent different informational categories:

Each table consists of four columns that provide the following information: keyword, data type of returned value; index argument associated with the keyword; and meaning.

Table 6-15 INFO (MODEL) Choices to Retrieve General Information About the Model

Keywords Data Type Index Arguments Meaning

AVAILABLE

BOOL

(No arguments)

Is there a model for which information is available?

NAME

TEXT

[MODEL model-num]

Without model-num (or with model-num equal to 0), the name of the current model. With model-num greater than 0, the name of the included model that is the specified model-num within the current model.

COUNT STATEMENTS

INT

(No arguments)

The number of statements in the current model. The count includes comments, equations, and DIMENSION and INCLUDE commands (if any), it but does not include the statements in an included model.

STATEMENT

TEXT

stmnt-num

The text of statement stmnt-num.

SIMULTANEOUS

BOOL

(No arguments)

Does the current model contain a simultaneous block? 


Table 6-16 INFO (MODEL) Choices to Retrieve Information about the Structure of the Model

Keyword(s) Data Type Index Argument(s) Meaning

COUNT ELEMENTS

INT

[BLOCK block-num]

Without block-num, the number of blocks in the current model. With block-num, the total number of statements and nested blocks within block block-num in the current model.

When you request further information about a particular element (for example, with the TYPE ELEMENT choice), you always specify the block number to which the element belongs and the number of the element within that block.

TYPE ELEMENT

TEXT

element-num BLOCK block-num

Returns BLOCK or STATEMENT, depending on whether element element-num of block block-num is a nested block or a statement.

NUMBER BLOCK

INT

element-num BLOCK block-num

The block number of the nested block that is element element-num of block block-num.

TYPE BLOCK

TEXT

block-num

Returns SIMPLE, STEP-FORWARD, STEP-BACKWARD, or SIMULTANEOUS, depending on the execution type of block block-num.

COUNT DIMS

INT

[BLOCK block-num]

Without block-num, the number of model dimensions of the current model. With block-num, the number of step-forward, step-backward, or simultaneous dimensions of block block-num within the current model.

DIMENSION

TEXT

dimension-num [BLOCK block-num]

Without block-num, the name of model dimension dimension-num of the current model. With block-num, the name of the specified step-forward, step-backward, or simultaneous dimension of block block-num.

NUMBER STATEMENT

INT

element-num BLOCK block-num

The statement number of the statement that is element element-num of block block-num.

The statement number refers to the position of the statement within its own model. To request further information about the statement (for example, with the HIDDEN choice), its model must be the model that you are currently considering.

HIDDEN

BOOL

stmnt-num

Has statement stmnt-num been masked by a subsequent statement?

NUMBER MODEL

INT

element-num BLOCK block-num

 

The number of the included model from which the statement that is element element-num of block block-num is taken.

 


Table 6-17 INFO (MODEL) Choices to Retrieve Information about Target, Sources, and Dependencies

Keyword(s) Data Type Index Argument Meaning

COUNT SOURCES

INT

STATEMENT stmnt-num

The number of data sources in statement stmnt-num within the current model.

TYPE REFERENCE

TEXT

STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the object type of the assignment target of statement stmnt-num. With source-num, the object type of data source source-num in statement stmnt-num. The object type is VARIABLE when the reference is to a variable. The type is DIMENSION when the reference is to the value of a dimension.

VARIABLE REFERENCE

TEXT

STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the name of the variable that is the assignment target of statement stmnt-num. With source-num, the name of the variable that is data source source-num in statement stmnt-num.

VALUE REFERENCE

TEXT

STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the dimension value that is the assignment target of statement stmnt-num. With source-num, the dimension value that is data source source-num in statement stmnt-num.

DIMENSION REFERENCE

TEXT

STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the model dimension of the target dimension value in statement stmnt-num. With source-num, the model dimension of source dimension value source-num in statement stmnt-num.

COUNT QUALIFIERS

INT

STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the number of qualifiers of the assignment target in statement stmnt-num. With source-num, the number of qualifiers of data source source-num in statement stmnt-num.

TYPE QUALIFIER

TEXT

qualifier-num STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the qualifier type of qualifier qualifier-num of the target of statement stmnt-num. With source-num, the qualifier type of qualifier qualifier-num of data source source-num in statement stmnt-num. The qualifier type can indicate dimensional dependence: LAG (previous dimension values only), LEAD (later values only), BOTH (both previous and later values), and VARIABLE (either previous or later values, depending on the value of a variable when the model is run). The qualifier type can also be QDR (qualified data reference).

DIMENSION QUALIFIER

TEXT

qualifier-num STATEMENT stmnt-num [SOURCE source-num]

qualifier-num STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the dimension of qualifier qualifier-num of the assignment target in statement stmnt-num. With source-num, the dimension of qualifier qualifier-num of data source source-num in statement stmnt-num.


Table 6-18 INFO (MODEL) Choices to Retrieve Information About Execution Status

Keyword(s) Data Type Index Argument Meaning

XEQSTATUS

TEXT

[BLOCK block-num]

Without block-num, the execution status of the model as a whole; when the model has not been run, the status is NOT EXECUTED. With block-num, the execution status of block block-num; when the model has not been run, an error is returned. When the model has been run, the status for the model as a whole or for a block can be SOLVED, DIVERGED, or FAILED TO CONVERGE. The status of an outer-level block can be EXECUTION INCOMPLETE when a nested block within it diverged or failed to converge.

COUNT ITERATIONS

INT

BLOCK block-num

The number of iterations that were performed for block block-num before it was solved or it diverged or failed to converge.

DAMP

DEC

(No arguments)

The value of the MODDAMP option when the model was run. (Relevant only when the solution method is GAUSS.)

DIVERGSTMT

INT

BLOCK block-num

The element number of the statement that diverged during the calculations for block block-num.

GAMMA

INT

(No arguments)

The value of the MODGAMMA option when the model was run.

MAXITERS

INT

(No arguments)

The value of the MODMAXITERS option when the model was run.

OVERFLOW

INT

(No arguments)

The value of the MODOVERFLOW option when the model was run.

SIMULTYPE

TEXT

(No arguments)

The value of the MODSIMULTYPE option when the model was run: AITKENS or GAUSS.

TOLERANCE

INT

(No arguments)

The value of the MODTOLERANCE option when the model was run. 


block-num

An INTEGER expression that specifies the block for which you want information. Block-num corresponds to the block numbers that are identified in the report produced by the MODEL.COMPRPT program.

dimension-num

An INTEGER expression that specifies the model dimension or block dimension for which you want information. For the model as a whole, the first dimension listed for the model is dimension-num 1, and so on. For example, assume that the MODEL.COMPRPT specifies the model dimensions as <line month>. In this case, line is dimension-num 1 and month is dimension-num 2. For a simultaneous block in the current model, the first dimension of the block is dimension-num 1, and so on. A step-forward or step-backward block has a single dimension, so the dimension of the block is always dimension-num 1. To see a list of the dimensions for the model as a whole and for each block of the model, you can run the MODEL.COMPRPT program.

element-num

An INTEGER expression that specifies the element for which you want information. When you request information about an element, you always specify the block number to which the element belongs. An element is either a statement in the specified block, or it is a nested block within the specified block. The element numbers correspond to the order of the statements and blocks in the compiled model. You can run the MODEL.COMPRPT program to see the list of elements in the compiled model.

For example, suppose the current model has the following compiled structure.

block 1
statement a
  block 2
  statement b
  statement c
  END block 2
statement d
END block 1

When you request information about block 1 in the preceding model, statement a is element-num 1; block 2 is element-num 2; and statement d is element-num 3. When you request information about block 2, statement b is element-num 1 and statement c is element-num 2.

model-num

For a hierarchy of included models, an INTEGER expression that specifies the model for which you want information. The model you are currently considering is model-num 0 (zero), the model it includes is model-num 1, and so on. The root model has the highest model number in the hierarchy.

qualifier-num

An INTEGER expression that specifies the qualifier for which you want information. Qualifiers change the dimensionality of a variable or dimension value reference. The reference can be qualified by a function, such as LAG, LEAD, or TOTAL or by a qualified data reference (QDR). To see the qualifiers for a statement, you can run the MODEL.DEPRT program for the model that contains the statement.

For each equation in the model, the MODEL.DEPRT report lists the assignment target and its qualifiers on one line, followed by the data sources. Each data source is listed on a separate line, together with its qualifiers. The MODEL.DEPRTreport also specifies the type of each qualifier: LAG, LEAD, BOTH, VARIABLE, or QDR (see the TYPE QUALIFIER choice in the third group of INFO keyword choices).

For the target and each source, qualifier-num corresponds to the order in which the qualifiers are listed in the MODEL.DEPRT report.

source-num

An INTEGER expression that specifies the data source for which you want information. In a calculation, each reference to a variable or a dimension value is counted as a source of data for the assignment target. A constant value is not counted as a source.

To see the data sources in a statement, you can run the MODEL.DEPRT program for the model that contains the statement. For each equation in the model, the MODEL.DEPRT report lists the assignment target on one line, followed by its data sources. Each data source is listed on a separate line.

stmnt-num

An INTEGER expression that specifies the statement for which you want information. Stmnt-num always refers to a statement from the model you are currently considering. It does not refer to a statement taken from an included model.

To see the statement numbers in the current model, you can run the MODEL.COMPRPT program. To the left of each statement, the report lists the model from which the statement is taken and the statement number within that model.

Examples

Example 6-112 Getting Qualifier Information

Assume that the following statement is statement 3 of a model called income.plan.

budget(line revenue) = LAG(actual(line revenue), 1, month) -
   + plan.factor

You can run the MODEL.DEPRPT program to see the qualifiers of the target and sources in this statement.

MODEL.DEPRPT income.plan

This statement produces the following output.

MODEL INCOME.PLAN
...
3    BUDGET(QDR <LINE>):
       ACTUAL(LAG <MONTH>)(QDR <LINE>)
       PLAN.FACTOR
...

This report shows that the assignment target, budget, has two data sources, actual and plan.factor.

Example 6-113 Checking Qualifier Information

The following statements make INCOME.PLAN the current model and check the number and type of the qualifiers of the assignment target of statement 3.

CONSIDER income.plan
SHOW INFO(MODEL COUNT QUALIFIERS STATEMENT 3)

These statements produce the following output.

1

The OLAP DML statement

SHOW INFO(MODEL TYPE QUALIFIER 1 STATEMENT 3)

produces the following output.

QDR

Example 6-114 Checking Different Data Sources

The following statements check the number and type of the qualifiers of the two data sources in statement 3.

The OLAP DML statement

SHOW INFO(MODEL COUNT QUALIFIERS STATEMENT 3 SOURCE 1)

produces the following output.

2

The OLAP DML statement

SHOW INFO(MODEL TYPE QUALIFIER 1 STATEMENT 3 SOURCE 1)

produces the following output.

LAG

The OLAP DML statement

SHOW INFO(MODEL TYPE QUALIFIER 2 STATEMENT 3 SOURCE 1)

produces the following output.

QDR

The OLAP DML statement

SHOW INFO(MODEL COUNT QUALIFIERS STATEMENT 3 SOURCE 2)

produces the following output.

0

INFO (PARSE)

The INFO (PARSE) function obtains information produced by a PARSE statement and stored internally by Oracle OLAP. Through the use of keywords, INFO lets you extract specific pieces of information about the expression that you have parsed.

Return Value

The return value depends on the keyword you use, as described in Table 6-19. When you try to extract unavailable information or use an index that is out of range, INFO returns NA. For example, if you parse a phrase that contains four expressions and then ask for the twelfth FORMULA, INFO returns NA.

Syntax

INFO(PARSE choice [index])

Arguments

PARSE

Indicates that you want to obtain information produced by a PARSE statement.

choice

The specific information you want. The choices available for PARSE are listed in Table 6-19, "INFO PARSE Keywords". Choices marked as indexed can take the optional index argument.

index

An INTEGER expression that specifies which result you want for a choice that can have several different results. For example, when you parse text that contains three expressions, each expression has its own formula and data type. You would use index to specify which expression you are interested in.

When you omit index, INFO returns all the information as a multiline value.

Table 6-19 INFO PARSE Keywords

Keyword Type Indexed? Meaning

PARSEABLE

BOOL

No

Was Oracle OLAP able to parse the text?

ERRORTEXT

TEXT

No

The text of an error message when the expressions were not parsed.

NUMFORMULAS

INT

No

The number of expressions (formulas) that were parsed.

NUMDIMS

INT

No

The number of dimensions in the union of all the expressions that were parsed.

FORMULA

TEXT

Yes

The text (formula) of the specified expression; index specifies which one you want.

DATA

TEXT

Yes

The data type of the specified expression.

TYPE

TEXT

Yes

The type of object of the specified expression; when the expression is the name of an object, it returns the type; when the expression is a qualified data reference, it returns QDR; when the expression is anything else, it returns EXP.

DIMENSION

TEXT

Yes

The name of the specified dimension in the union of all dimensions of the expressions. 


Examples

Example 6-115 Getting Parsed Information

In a simple report program, you want to allow the user to specify the data to be reported as an argument to the program. You want to allow the user to specify an expression and the name of a data variable. You cannot process expression arguments with an ARGS statement, so you use PARSE and INFO to parse the program arguments and produce the report.

The following statements create a simple report program.

DEFINE report1 PROGRAM
PROGRAM
PUSH month product district DECIMALS
DECIMALS = 0
LIMIT month TO FIRST 2
LIMIT product TO ALL
LIMIT district TO 'Chicago'
PARSE ARGS
REPORT ACROSS month: WIDTH 8 <&INFO(PARSE FORMULA 1) -
        WIDTH 13 &INFO(PARSE FORMULA 2)>
POP month product district DECIMALS
END

When users run the program, they can supply either the name of a variable (sales) or an expression (sales-expense) or both as arguments.

The following statement

REPORT1 sales sales-expense

produces the following output.

DISTRICT: CHICAGO
             --------------------MONTH--------------------
             --------Jan95--------- --------Feb95---------
PRODUCT       SALES   SALES-EXPENSE  SALES   SALES-EXPENSE
------------ -------- ------------- -------- -------------
Tents          29,099         1,595   29,010         1,505
Canoes         45,278           292   50,596           477
Racquets       54,270         1,400   58,158         1,863
Sportswear     72,123         7,719   80,072         9,333
Footwear       90,288         8,117   96,539        13,847

INFO (REGRESS)

The INFO (REGRESS) function obtains information produced by an REGRESS statement and stored internally by Oracle OLAP. Through the use of keywords, INFO lets you extract specific pieces of information about the regression you have calculated.

Note:

Before using INFO, familiarize yourself with REGRESS.REPORT that produces a standard report of its results, which might give you all the information you need. INFO is useful primarily for creating customized reports or for performing further analysis on the results

Return Value

The return value depends on the keyword you use, as described in Table 6-20, "INFO REGRESS Keywords".

Syntax

INFO(REGRESS choice [index])

Arguments

REGRESS

Indicates that you want to obtain information produced by an REGRESS statement.

choice

The specific information you want. The choices available for REGRESS are listed in Table 6-20, "INFO REGRESS Keywords". Choices marked as indexed require the index argument.

index

An INTEGER expression that specifies which result you want for a choice that can have several different results. For example, in a regression there may be multiple independent variables. You would use index to specify which independent variable you want information about. When you omit index for a choice that requires it, an error occurs.

Table 6-20 INFO REGRESS Keywords

Keyword Type Indexed? Meaning

AVAILABLE

BOOL

No

Is there a computed regression from which to extract information?

DEPENDENT

TEXT

No

The name of the dependent variable in the regression.

NOINTERCEPT

BOOL

No

Was the regression calculated with the intercept suppressed?

WEIGHTED

BOOL

No

Was the last regression weighted?

WEIGHT

TEXT

No

The expression used to weight the last regression.

NUMCOEFS

INT

No

The number of coefficients.

INDEPENDENT

TEXT

Yes

An independent variable; index specifies which one you want (Intercept to be first unless it was suppressed).

COEFFICIENT

DEC

Yes

An estimated coefficient; index specifies which one you want.

STDERROR

DEC

Yes

The standard error of an estimated coefficient; index specifies which one you want.

TRATIO

DEC

Yes

The t-ratio for an estimated coefficient; index specifies which one you want.

NUMOBS

INT

No

The number of observations that were used.

FRATIO

DEC

No

The F-ratio for the regression.

RBSQ

DEC

No

The corrected R-squared for the regression.

FORMULA

TEXT

No

The regression formula.

STDERROREST

DEC

No

The standard error of estimate for the regression

RESET

BOOL

 

Use when you want to reset the original state of AVAILABLE back to NO 


Notes

Determining Regression Results Availability

When you try to extract information without having performed a regression, INFO produces an error. You can use the keyword AVAILABLE to determine whether any results are currently available. Once a successful regression has run, AVAILABLE remains true even when one or more unsuccessful regressions follow, because the results of the previous successful regression are still available. AVAILABLE remains true until you use RESET to change the AVAILABLE state back to its original value of NO.

NA Results Due to Index

INFO returns NA when you use an index that is out of range. For example, when your regression has five independent variables and you request the coefficient of the twelfth one, INFO returns NA.

Examples

Example 6-116 Getting Regression Information

The following statement sends the third coefficient from your most recently calculated regression to the current outfile.

SHOW INFO(REGRESS COEFFICIENT 3)

This statement produces the following result.

7.55

INITCAP

The INITCAP function returns a specified text expression, with the first letter of each word in uppercase and all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.

Return Value

The same data type as the expression.

Syntax

INITCAP (text-exp)

Arguments

text-exp

A text expression.

Examples

Example 6-117 Capitalizing the First Character in Each World

The following example capitalizes each word in the string.

SHOW INITCAP('the soap')  
The Soap

INLIST

The INLIST function determines whether every line of a text value is a line in a second text value. Normally, INLIST is used to determine whether all the lines of a list (in the form of a multiline text value) can be found in a master list (in the form of a second multiline text value).

INLIST accepts TEXT values and NTEXT values as arguments. When only one argument is NTEXT, then INLIST automatically converts the other argument to NTEXT before performing the function operation.

Return Value

BOOLEAN

Syntax

INLIST(masterlist list)

Arguments

masterlist

A multiline text expression to which the lines of list are compared.

list

A multiline text expression whose lines are compared with the lines of masterlist. When every line of list can be found as a line of masterlist, INLIST returns the value YES. When one or more lines of list are not found in masterlist, INLIST returns the value NO.

Examples

Example 6-118 Comparing a List to a Master List

This example shows how to use INLIST to determine whether the lines of one list can be found in a master list. The master list in this case is a multiline text value in a variable called depts. The depts variable has the following values.

Marketing
Purchasing
Accounting
Engineering
Personnel

The first function call compares a list, which is specified as a text literal, with the master list. The return value is YES.

INLIST(depts, 'Accounting\nPersonnel')

The second function call compares a variable newlist that has the following values,

Development
Accounting

with the master list in depts. The return value is NO.

INLIST(depts, newlist)

INSBYTES

The INSBYTES function inserts one or more bytes into a text expression.

When you are using a single-byte character set, you can use INSCHARS.

Return Value

TEXT

Syntax

INSBYTES(text-expression bytes [after])

Arguments

text-expression

A TEXT expression into which the bytes are to be inserted. When text-expression is a multiline TEXT value, INSBYTES preserves the line breaks in the returned value.

bytes

One or more bytes that you insert into text-expression.

after

An INTEGER that represents the byte position after which the specified bytes are to be inserted. The position of the first byte in text-expression is 1. To insert bytes at the beginning of the text, specify 0 for after. When you omit this argument, INSBYTES inserts the bytes after the last byte in text-expression.

When you specify a value for after that is greater than the length of text-expression, INSBYTES adds blanks to the last line of text-expression. The number of inserted blanks is the difference between the value of after and the length of text-expression. For example, insbytes('abc' 'def' 4) inserts one blank space before adding def to abc, resulting in.

abc def

Examples

Example 6-119 Inserting Bytes in Text

This example shows how to insert the bytes there in the TEXT value hellojoe.

The function

INSBYTES('hellojoe', 'there', 5)

returns the following value.

hellotherejoe 

INSCHARS

The INSCHARS function inserts one or more characters into a text expression.

When you are using a multibyte character set, you can use the INSBYTES function instead of the INSCHARS function.

Return Value

TEXT or NTEXT

This function accepts TEXT values and NTEXT values as arguments. The data type of the return value depends on the data type of the values specified for the arguments:

Syntax

INSCHARS(text-expression characters [after])

Arguments

text-expression

The expression into which the characters are to be inserted. When text-expression is a multiline TEXT value, INSCHARS preserves the line breaks in the returned value.

characters

One or more characters that you insert into text-expression.

after

An INTEGER that represents the character position after which the specified characters are to be inserted. The position of the first character in text-expression is 1. To insert characters at the beginning of the text, specify 0 for after. When you omit this argument, INSCHARS inserts the characters after the last character in text-expression.

When you specify a value for after that is greater than the length of text-expression, INSCHARS adds blanks to the last line of text-expression. The number of inserted blanks is the difference between the value of after and the length of text-expression. For example, INSCHARS('abc' 'def' 4) inserts one blank before adding 'def' to 'abc', resulting in.

abc def

Examples

Example 6-120 Inserting Characters in Text

This example shows how to insert the characters there in the TEXT value hellojoe.

INSCHARS('hellojoe', 'there', 5)

hellotherejoe 

INSCOLS

The INSCOLS function inserts into the columns of a multiline TEXT value all the columns of another TEXT value. The inserted columns are placed after the column position you specify, and the original columns in each line are moved to the right. The function returns a multiline TEXT value composed of the resulting columns.

Return Value

TEXT or NTEXT

This function accepts TEXT values and NTEXT values as arguments. The data type of the return value depends on the data type of the values specified for the arguments:

The number of lines in the return value is always the same as the number of lines in text-expression. When the columns TEXT expression has fewer lines, INSCOLS repeats its last line in each subsequent line of the return value.

Syntax

INSCOLS(text-expression columns [after])

Arguments

text-expression

The expression into which you want to insert columns.

columns

The expression containing one or more columns in each line. All the columns of this expression is inserted into the corresponding lines of text-expression.

after

An INTEGER between 0 and 4,000 representing the column position after which columns should be inserted. The column position of the first character in each line is 1. When you do not specify after, insertion begins at the end of each line. The total length of a line cannot exceed 4,000 columns of single-byte characters or 2,000 columns of double-byte characters.

When you specify an after column that is to the right of the last character in a given line in text-expression, the corresponding line in the return value has spaces filling in the intervening columns.

Examples

Example 6-121 Inserting Text Columns

In the following example, a color code (stored in the multiline TEXT value itemcolor) is inserted into item identifiers that are stored in the itemid text value. The columns are inserted after Column 3.

itemcolor has the following value.

Blu
Red
Gre
Ora

itemid has the following value.

542-Fra
379-Eng
968-USA
369-Can

The INSCOLS function call

INSCOLS(itemid itemcolor 3)

returns the following.

542Blu-Fra
379Red-Eng
968Gre-USA
369Ora-Can 

INSLINES

The INSLINES function inserts one or more lines into a multiline text expression.

Return Value

TEXT or NTEXT

This function accepts TEXT values and NTEXT values as arguments. The data type of the return value depends on the data type of the values specified for the arguments:

Syntax

INSLINES(text-expression lines [after])

Arguments

text-expression

A multiline expression into whose values one or more lines are to be inserted.

lines

An expression that represents one or more lines of text that you insert into text-expression.

after

An INTEGER that represents the line number after which the specified lines are to be inserted. The position of the first line in text-expression is 1 (one). To insert lines at the very beginning, specify 0 (zero) for after. When you omit this argument, INSLINES inserts the new lines after the last line of text-expression.

Examples

Example 6-122 Inserting Text Lines

This example shows how to insert a new line into a multiline text value in a variable called mktglist with the following value.

Salespeople
Products
Services

The INSLINES function

INSLINES(mktglist, 'Advertising', 2)

returns the following.

Salespeople
Products
Advertising
Services 

INSTAT

The INSTAT function checks whether a dimension or dimension surrogate value is in the current status list or whether a dimension value is in a valueset.

Return Value

BOOLEAN

YES if the value is in the current status list or in a valueset and NO if it is not.

Syntax

INSTAT(dimensionvalue)

Arguments

dimension

The name of the dimension, dimension surrogate, or valueset.

value

The dimension or dimension surrogate value you want to test, either a text literal (enclosed in single quotes) or an expression that specifies the value. To specify the value of a conjoint dimension or a concat dimension, enclose the value in angle brackets. For a conjoint dimension, separate the base dimension values with a comma and space. For a concat dimension, separate the base dimension and its value with a colon and a space.

Notes

Checking an Invalid Value

When you specify a dimension name and value in an INSTAT statement, Oracle OLAP tells you whether that value is in the current status list for that dimension. Conversely, the ISVALUE function tells you whether an item is a value of a dimension, regardless of whether it is in the status. INSTAT produces an error when value is not a dimension value, but ISVALUE simply returns a value of FALSE.

Examples

Example 6-123 Using INSTAT With a Valueset

Assume that within your analytic workspace you have a geog dimension with the following definition and values.

DEFINE geog TEXT DIMENSION
GEOG
--------------
Austria
Belgium
Canada

Assume, also, that you define a mygeogs valueset and identify a value for that valueset using the following statements.

DEFINE mygeogs VALUESET geog
LIMIT mygeogs TO 'Belgium'
 

You cannot issue a REPORT on a valueset. If you attempt to, Oracle OLAP issues an error message.

REPORT mygeogs
ORA-34104: INSTATTEST!MYGEOGS is not a type of object that contains data values.
 

However, you can use the INSTAT function to display which values of a dimension are in a valueset.

REPORT INSTAT (mygeogs, geog)

GEOG            INSTAT (MYGEOGS, GEOG)
-------------- ------------------------
Austria                              no
Belgium                             yes
Canada                               no

Example 6-124 Checking Current Status

In the following example, a program accepts a value of the month dimension as an argument. The first lines of the program use INSTAT to check whether the dimension value that was passed as an argument is in the current status for month. When it is, the program calls a report program. When it is not, the program branches to its error-handling section.

ARGUMENT onemonth month
 
IF INSTAT(month onemonth)
   THEN sales_report
   ELSE GOTO error
...

Example 6-125 Using INSTAT When the Dimension is a Conjoint Dimension

When the dimension that you specify is a conjoint dimension, then the entire value must be enclosed in single quotes. For example, suppose the analytic workspace has a region dimension and a product dimension. The region dimension values include East, Central, and West. The product dimension values include Tents, Canoes, and Racquets.

The following statements define a conjoint dimension, and add values to it.

DEFINE reg.prod DIMENSION <geography product>
MAINTAIN reg.prod ADD <'East', 'Tents'> <'West', 'Canoes'>

To specify base positions, use a statement such as the following.

SHOW INSTAT(reg.prod '<1, 1>')
YES

To specify base text values, use a statement such as the following.

SHOW INSTAT(reg.prod '<\'East\', \'Tents\'>')
YES

Example 6-126 Using INSTAT When the Dimension is a Concat Dimension

When the dimension that you specify is a concat dimension, then you must enclose the entire <component dimension: dimension value> pair in single quotes. The following statement defines a concat dimension that has as its base dimensions region and product.

DEFINE reg.prod.ccdim DIMENSION CONCAT(region product)

A report of reg.prod.ccdim returns the following.

REG.PROD.CCDIM
----------------------
<region: East>
<region: Central>
<region: West>
<product: Tents>
<product: Canoes>
<product: Racquets>

To specify a base dimension position, use a statement such as the following.

SHOW INSTAT(reg.prod.ccdim '<product: 3>')
yes

To specify base dimension text values, use a statement such as the following.

SHOW INSTAT(reg.prod.ccdim '<product: Tents>')
YES

INSTR functions

The INSTR functions (INSTR, INSTRB, INSTRC, INSTR2, and INSTR4) searches a string for a substring using characters and returns the position in the string that is the first character of a specified occurrence of the substring. The functions vary in how they determine the position of the substring to return.

Return Value

A nonzero INTEGER when the search is successful or 0 (zero) when it is not.

Syntax

{INSTR | INSTRB | INSTRC | INSTR2 | INSTR4} (string , substring [, position [, occurrence]])

Arguments

string

The text expression to search.

substring

The string to search for.

position

A nonzero INTEGER indicating where in string the function begins the search. INSTR calculates position using characters as defined by the input character set. INSTRB calculates position using bytes. INSTRC calculates position using Unicode complete characters. INSTR2 calculates position using UCS2 code points. INSTR4 calculates position using UCS4 code points.

When position is negative, then INSTR counts and searches backward from the end of string. The default value of position is 1, which means that the function begins searching at the beginning of string.

occurrence

An INTEGER indicating which occurrence of string the function should search for. The value of occurrence must be positive. The default values of occurrence is 1, meaning the function searches for the first occurrence of substring.

Examples

Example 6-127 Using Character Position to Search Forward to Find the Position of a Substring

The following example searches the string "Corporate Floor", beginning with the third character, for the string "or". It returns the position in "Corporate Floor" at which the second occurrence of "or" begins.

SHOW INSTR('Corporate Floor','or', 3, 2)
14

Example 6-128 Using Character Position to Search Backward to Find the Position of a Substring

In this next example, the function counts backward from the last character to the third character from the end, which is the first "o" in "Floor". The function then searches backward for the second occurrence of "or", and finds that this second occurrence begins with the second character in the search string.

SHOW INSTR('Corporate Floor','or', -3, 2)
2

Example 6-129 Using a Double-Byte Character Set to Find the Position of a Substring

This example assumes a double-byte database character set.

SHOW INSTRB('Corporate Floor','or',5,2) 
27               

INTPART

The INTPART function calculates the integer part of a decimal number by truncating its decimal fraction.

Return Value

INTEGER

Syntax

INTPART(expression)

Arguments

expression

The decimal expression whose integer part is to be returned.

Notes

Large Values

When expression has a value larger than is allowed for an INTEGER (a value between -2,147,483,647 and 2,147,483,647), INTPART returns an NA value.

Examples

Example 6-130 Calculating the Integer Part of a Decimal Number

The following example shows the integer part of the number 3.14. The statement

show intpart(3.14)

produces the following result.

3

IRR

The IRR function computes the internal rate of return associated with a series of cash flow values. Each value of the result is calculated to be the discount rate for each period that makes the net present value of the corresponding cash flows equal to zero.

Return Value

DECIMAL (For example, n 8.25 percent internal rate of return produces a result value of .0825.)

The result returned by the IRR function is dimensioned by all the dimensions of cashflows except its time dimension. When cashflows is dimensioned only by the time dimension, IRR returns a single value.

Syntax

IRR(cashflows, [time-dimension])

Arguments

cashflows

A numeric expression dimensioned by time-dimension, that specifies the series of cash flow values.

Note:

All the cash flows used to compute a result value are assumed to occur at the same relative point within the period with which they are associated. Cash flows that corresponds to out-of-status dimension positions are ignored
time-dimension

A name that specifies the time dimension. When cashflows has a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, the time-dimension argument is optional since IRR automatically uses the DAY, WEEK, MONTH, QUARTER, or YEAR dimension of cashflows when you do not specify a value for time-dimension.

Notes

Multiple Discount Rates

Some series of cash flows have multiple discount rates, which make the net present value equal to zero. In such cases, IRR finds and returns only one of these discount rates as the internal rate of return. When there is only a single solution and it is between -99.9 percent and 10,000 percent, the IRR function finds it. When IRR cannot calculate an internal rate of return, the corresponding value in the result is NA.

Examples

Example 6-131 Calculating the Internal Rate of Return

The following statements create a dimension called project, add values to it, and create a variable called cflow, which is dimensioned by year and project.

DEFINE project DIMENSION TEXT
MAINTAIN project ADD 'a' 'b' 'c' 'd' 'e'
DEFINE cflow VARIABLE DECIMAL <project year>

Once you have assigned the following values to CFLOW,

------------------------CFLOW----------------------
               -----------------------PROJECT---------------------
YEAR               a          b          c          d          e
-------------- ---------- ---------- ---------- ---------- -------
Yr95            -200.00      -200.00    -300.00   -100.00  -200.00
Yr96             100.00       150.00     200.00     25.00    25.00
Yr97             100.00       400.00     200.00    100.00   200.00

then the following statement

REPORT IRR(cflow, year)

produces the following report of the internal rate of return.

IRR(CFLOW,
PROJECT          YEAR)
-------------- ----------
a                    0.00
b                    0.84
c                    0.22
d                    0.13
E                    0.06

ISDATE

The ISDATE program determines whether a text expression represents a valid date. ISDATE only tests a text expression to see if it can be converted to a DATE-only value; it does not actually make the conversion. You must use CONVERT to make the conversion.

Return Value

BOOLEAN

YES when the text expression represents a valid date; NO when it does not.

Syntax

ISDATE(test-date)

Arguments

test-date

A single-line ID or TEXT expression to be examined to see if it represents a valid date, as defined by the DATE-only data type. For a description of the valid styles for entering dates, see "Date-only Input Values".

Examples

Example 6-132 Testing a Text Expression

In the following statement, the ISDATE program tests a literal text expression to see if it is a valid date, and the output is sent to the current outfile.

SHOW ISDATE('3 5 1995')

This statement produces the following output.

YES

ISEMPTY

The ISEMPTY function identifies if a variable or one or more of its partitions has values.

Return Value

BOOLEAN

FALSE when the specified variable or partitions have values; TRUE when they are empty.

Syntax

ISEMPTY( variable [(partition ...)])

Arguments

variable

The name of the variable to check for values.

partition

The name of one or more partitions, separated by commas, to check for values.


ISINFINITE

The ISINFINITE function returns a value that indicates if a the value of a numeric expression is infinity.

See also:

This function provides information similar provided by the SQL IS [NOT] INFINITE floating point condition as described in Oracle Database SQL Language Reference.

Return Value

BOOLEAN

Returns TRUE when the expression is either +INF (or -INF when NOT is not specified); otherwise it returns FALSE.

Note:

The OLAP DML converts +INF or -INF values to NA when it performs calculation. Consequently, this function can only possibly return TRUE when executed against data that has been imported into an analytic workspace from a SQL-populated database but not yet used in an OLAP DML calculation.

Syntax

ISINFINITE(expression)

Arguments

expression

A decimal expression.


ISNAN

The ISNAN function returns a value that indicates if a the value of a numeric expression is the special NaN value.

See also:

This function provides information similar provided by the SQL IS [NOT] NAN floating point condition as described in Oracle Database SQL Language Reference.

Return Value

BOOLEAN

Returns TRUE when the expression is either +NaN (or -NaN when NOT is not specified); otherwise it returns FALSE.

Note:

The OLAP DML converts +NaN or -NaN values to NA when it performs calculation. Consequently, this function can only possibly return TRUE when executed against data that has been imported into an analytic workspace from a SQL-populated database but not yet used in an OLAP DML calculation.

Syntax

ISNAN(expression)

Arguments

expression

A decimal expression.


ISSESSION

The ISSESSION function determines whether the current member of a specified dimension is a temporary member (that is, a member added when a MAINTAIN ADD SESSION statement executes).

Return value

BOOLEAN

YES when the member is a temporary member; NO when it is not.

Syntax

ISSESSION( [RECURSIVE]dimension)

Arguments

RECURSIVE

Specifies that for a dimension with base dimensions, that Oracle OLAP tests the values of the base dimensions when making its determination.

dimension

The name of the dimension whose current member value is to be tested by Oracle OLAP.


ISVALUE

The ISVALUE function tests whether a dimension or a composite has a specified value.

Tip:

Use INSTAT to determine whether a value of a dimension is in the current status of the dimension.

Return Value

BOOLEAN

Syntax

ISVALUE(namevalue)

Arguments

name

The name of the dimension or the composite to be checked.

When the composite is unnamed, use the SPARSE keyword to refer to the composite (for example, SPARSE <market product>).

value

The value you want to test, either a text literal or text expression for an ID or TEXT dimension, an INTEGER for an INTEGER dimension, or a combination of values enclosed by angle brackets for composites and conjoint dimensions.

Examples

Example 6-133 Testing Valid Values

Suppose you want to find out if Packs is a value of the product dimension. The following statement produces the answer YES or NO.

SHOW ISVALUE(product, 'Packs')

Example 6-134 Testing Logical Position Numbers

You can test for the logical position numbers of base dimension values in a conjoint dimension. For example, suppose market and product are the base dimensions of the conjoint dimension markprod. The following statement tests whether or not there is a value assigned to the combination of the fourth market dimension value and the third product dimension value.

SHOW ISVALUE(markprod, '<4 3>')

JOINBYTES

The JOINBYTES function joins two or more text values as a single line.

JOINBYTES ignores any arguments that have a value of NA and removes line breaks from the text it joins. (To preserve the breaks in a multiline text expression, use the INSCHARS.) Also, when the length of the joined line exceeds 4,000 (The maximum length of a joined line), JOINBYTES automatically breaks the line and puts the remaining bytes on the next line. The line break could occur between the two bytes of a double-byte character. JOINBYTES would then end one line with the first byte of the double-byte character and start the next line with the second byte of the character.

Return Value

TEXT

Syntax

JOINBYTES(first-expressionnext-expression...)

Arguments

first-expression

An expression to which JOINBYTES joins next-expression. When the first-expression has a data type other than TEXT or NTEXT, JOINBYTES converts it to TEXT. Use the CONVERT or TO_CHAR function to convert a NTEXT expression to TEXT.

next-expression

One or more expressions to join with first-expression. When an expression you want to concatenate has a data type other than TEXT or NTEXT, JOINBYTES converts it to TEXT. Use the CONVERT or TO_CHAR function to convert a NTEXT expression to TEXT.

Examples

Example 6-135 Using JOINBYTES to Concatenate Values

This example shows how you can use JOINBYTES to combine text with the current values of the two variables name.product and price. The variable price has a data type of DECIMAL; however, JOINBYTES automatically converts its value to TEXT to join it with the other text values.

LIMIT product TO 'Canoes'
LIMIT month TO 'Dec96'

The JOINBYTES function

JOINBYTES('Current Price for ' name.product ' is:  $' price)

returns the following value.

Current Price for Aluminum Canoes is:  $200.03 

JOINCHARS

The JOINCHARS function joins two or more non-NA expressions as a single line text. JOINCHARS removes line breaks from the text it joins. (Use INSCHARS to preserve line breaks.)

When the length of the joined line exceeds 4,000 bytes, JOINCHARS automatically breaks the line and puts the remaining characters on the next line. When the line break would occur between the two bytes of a double-byte character, JOINCHARS does not split the double-byte character; instead, it puts both bytes of the double-byte character on the next line.

Tip:

When you are using a multibyte character set, you can use the JOINBYTES function instead of the JOINCHARS function.

Return Value

TEXT or NTEXT

The data type of the return value depends on the data type of the values specified for the arguments:

Syntax

JOINCHARS(first-expressionnext-expression...)

Arguments

first-expression

An expression to which JOINCHARS joins next-expression. When the first-expression has a data type other than TEXT or NTEXT, JOINCHARS converts it to TEXT.

next-expression...

One or more expressions to join with first-expression. When an expression you want to concatenate has a data type other than TEXT or NTEXT, JOINCHARS converts it to TEXT.

Examples

Example 6-136 Using JOINCHARS to Concatenate Values

This example shows how you can use JOINCHARS to combine text with the current values of the two variables name.product and price. The variable price has a data type of DECIMAL; however, JOINCHARS automatically converts its value to TEXT to join it with the other text values.

LIMIT product TO 'Canoes'
LIMIT month TO 'Dec96'

The JOINCHARS function

JOINCHARS('Current Price for ' name.product ' is:  $' price)

returns the following value.

Current Price for Aluminum Canoes is:  $200.03 

JOINCOLS

The JOINCOLS function joins the corresponding lines of two or more multiline text values. The function returns a multiline text value composed of the concatenated lines up to a length of 498 bytes (the maximum length of a single concatenated line).

The number of lines in the return value is always the same as that in the argument expression that has the most lines. When a given argument expression has fewer lines, JOINCOLS repeats its last line in each subsequent line of the return value. This repeating feature is useful when an argument expression is a single-line separator, such as a space or hyphen. See Example 6-137, "Joining the Columns of Two Text Expressions".

Return Value

TEXT or NTEXT

When all arguments are TEXT values, the return value is TEXT. When all arguments are NTEXT values, the return value is NTEXT. When the arguments include both TEXT and NTEXT values, the function converts all TEXT values to NTEXT before performing the function operation, and the return value is NTEXT.

Syntax

JOINCOLS(first-expressionnext-expression...)

Arguments

first-expression

An expression whose lines JOINCOLS joins with those of next-expression. When the expression has a data type other than TEXT or NTEXT, JOINCOLS converts it to TEXT. JOINCOLS ignores any arguments that have a value of NA

next-expression...

One or more expressions to join with first-expression. When an expression you want to concatenate has a data type other than TEXT or NTEXT, JOINCOLS converts it to TEXT. JOINCOLS ignores any arguments that have a value of NA

Examples

Example 6-137 Joining the Columns of Two Text Expressions

In the following example, each line in citylist is joined with a quoted text value, and the corresponding line from cityreps.

citylist has the following values.

Boston
Houston
Chicago
Denver

cityrep has the following values.

Brady
Lopez
Alfonso
Cody

The JOINCOLS function

JOINCOLS(citylist ' -- ' cityreps)

returns the following.

Boston -- Brady
Houston -- Lopez
Chicago -- Alfonso
Denver -- Cody 

JOINLINES

The JOINLINES function joins the values of two or more expressions into a single multiline textual value. When multiline text values are joined, all the lines of the first expression appear first, followed by all the lines of the second expression, and so forth. Normally the arguments for JOINLINES are text values, but they can have other data types.

Return Value

TEXT or NTEXT

When all arguments are TEXT values, the return value is TEXT. When all arguments are NTEXT values, the return value is NTEXT. When the arguments include both TEXT and NTEXT values, the function converts all TEXT values to NTEXT before performing the function operation, and the return value is NTEXT.

Syntax

JOINLINES(first-expression next-expression...)

Arguments

first-expression

An expression to which JOINLINES adds next-expression. When the expression has a data type other than TEXT or NTEXT, JOINLINES converts it to TEXT. JOINLINES ignores any arguments that have a value of NA.

next-expression...

One or more expressions to join with first-expression. When an expression you want to concatenate has a data type other than TEXT, JOINLINES converts it to TEXT. JOINLINES ignores any arguments that have a value of NA.

Examples

Example 6-138 Joining the Lines of Two Text Expressions

This example shows how to make a new list by adding the value Regions to the end of a variable called mktglist.

mktglist has the following initial values.

Salespeople
Products
Services

The statement

newlist = JOINLINES(mktglist 'Regions')

assigns the following to newlist.

Salespeople
Products
Services
Regions 

KEY

The KEY function returns the value of the specified base dimension for a value of a conjoint dimension or a composite.

Return Value

The return value depends on the data type of the specified base dimension.

Syntax

KEY(dimension-expbase-dimension-exp)

Arguments

dimension-exp

An expression that specifies a value of a conjoint dimension or a composite. When you specify the conjoint dimension itself, KEY uses the first value in status. When you specify the composite itself, KEY uses the first value in status for every base dimension in the composite.

base-dimension-exp

An expression that specifies the name of a base dimension of the previously specified conjoint dimension or composite for which you want to know the dimension value.

Examples

Example 6-139 Reporting with a Conjoint

Suppose you want to produce a report of data dimensioned by a conjoint dimension. You can label each row with the base values of each conjoint dimension value with the KEY function. Each base value occupies its own column and you have more control over the layout.

The following program excerpt loops over the conjoint dimension proddist, whose values are a combination of product and district. Assume also that there is a variable named dsales which is dimensioned by proddist.

DEFINE proddist DIMENSION <product district>
LD Conjoint dimension made up of combinations of product and district values
DEFINE dsales VARIABLE DECIMAL <month proddist>
LD Sparse sales data made dense by dimensioning by conjoint dimension proddist

The program excerpt shows dsales for three months. The base values of the conjoint dimension value each occupy their own column. For contrast, the second loop uses the conjoint dimension directly, without the KEY function. The conjoint dimension values are displayed in one column, with angle brackets.

LIMIT month TO FIRST 3
FOR proddist
  ROW KEY(proddist district) KEY( proddist product) ACROSS month: dsales
BLANK 2
FOR proddist
  ROW W 25 proddist ACROSS month: dsales

The program produces the following report.

Boston         Tents       32,153.52  32,536.30  43,062.75
Denver         Canoes      45,467.80  51,737.01  58,437.11
Atlanta        Sportswear 114,446.26 123,164.92 138,601.64
<Tents, Boston>            32,153.52  32,536.30  43,062.75
<Canoes, Denver>           45,467.80  51,737.01  58,437.11
<Sportswear, Atlanta>     114,446.26 123,164.92 138,601.64