7 OLAP DML Functions: A - K
This chapter contains the following topics:
-
One topic for each of the OLAP DML functions that begins with the letters A - K, beginning with ABS.
Reference topics for the remaining OLAP DML functions appear in alphabetical order in OLAP DML Functions: L - Z.
For other OLAP DML reference topics, see OLAP DML Properties, OLAP DML Options, OLAP DML Commands: A-G, and OLAP DML Commands: H-Z.
7.1 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.
7.3 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
Conversion functions
System and Database information functions
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
Financial functions
File management functions
NA functions
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
- 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
Object value retrieval functions
Program argument and context functions
Reporting functions
Statistical and forecasting functions
Status manipulation functions
Text functions
- ASCII
- ASCIISTR
- 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
- REPLACE
- REPLBYTES
- REPLCHARS
- REPLCOLS
- REPLLINES
- RPAD
- RTRIM
- SORT function
- SORTLINES
- SOUNDEX
- SUBSTR functions
- TEXTFILL
- TRANSLATE
- TRIM
- UNIQUELINES
- UPPER
- UPPER
- VSIZE
Time-series functions
7.4 ABS
The ABS function calculates the absolute value of an expression. Because the absolute value of a real number is its numeric value without regard to its sign, this function always returns a positive value. For example, 3 is the absolute value of both 3 and -3.
Return Value
DECIMAL.
The dimensionality of the result is the same as the specified expression.
Syntax
ABS(expression)
Examples
Example 7-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
7.5 ADD_MONTHS
The ADD_MONTHS function returns the date that is n months after the specified date.
Return Value
DATETIME
Syntax
ADD_MONTHS(start_datetime, n)
Parameters
- start_datetime
-
A
DATETIMEexpression 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 7-2. - n
-
An
INTEGERthat identifies the number of months to be added to start_datetime.
Examples
Example 7-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)
Because February 29 was the last day of February 2000, ADD_MONTHS returns February 29, 2000.
29-Feb-00
7.6 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 executes a DEFINE VARIABLE statement that includes a USING AGGOUNT phrase.
See Also:
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)
Examples
Example 7-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
Because 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 127.7 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 the following table for more information.
Syntax
AGGMAPINFO (name {choice | {choice-at-position rel-pos} })
Parameters
- name
-
The name of the aggmap object.
- choice
-
Specifies the type of information returned. See the following table for details.
Table 7-1 Keywords for the choice Parameter of the AGGMAPINFO function
| Keyword | Data Type | Description |
|---|---|---|
|
ADDED_MODELS |
|
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 |
|
Indicates the setting for the AGGINDEX statement in the aggmap. A |
|
CHILDREN member-name |
|
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 |
|
The members added using MAINTAIN ADD SESSION statements. The names of the members are returned as a multi-line text string. |
|
DIMENSION |
|
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 |
|
Indicates whether Oracle OLAP has a cache for the AGGREGATE function. (Applies to AGGMAP type aggmaps only.) |
|
MAPTYPE |
|
The type of the aggmap.
|
|
MODELS |
|
The models in the aggmap. The names of the models are returned as a multi-line text string. |
|
NUMRELS |
|
The total number of RELATION statements in an aggmap specification. |
|
RELATIONS |
|
The name of relation that is specified by a RELATION statement in the aggmap specification. Each statement is displayed on a separate line. |
|
STORE |
|
Indicates whether the CACHE statement in the aggmap is set to STORE. A |
|
VARIABLES |
|
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 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
NAwhen 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
INTEGERthat specifies a RELATION statement in the aggmap. TheINTEGERindicates 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 anINTEGERwith a value of1as the rel-pos argument. To get information about the fourth RELATION statement in an aggmap, use theINTEGER4, and so on. You may use anyINTEGERbetween1and 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 7-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
7.8 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.
See Also:
Note:
When the variable you want to aggregate has an $AGGMAP property, you do not have to use the AGGREGATE function to aggregate the data that has not been precomputed.
Return Value
The same data type as the aggregated variable.
Syntax
AGGREGATE (var ... [USING aggmap] - [FROM fromspec|FROMVAR textvar] [FORCECALC FORCEORDER] [COUNTVAR countvar])
Parameters
- 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
NAto 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 is 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-
NAcounts 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
INTEGERvariable 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 anINTEGERvariable for each one to record the results.
Usage Notes
Steps for Supporting Run-Time Calculations
Follow these steps when combining pre-aggregation with run-time aggregation:
-
Create an aggmap that limits the amount of data to be precalculated.
-
Execute the AGGREGATE command with the FUNCDATA argument.
-
When you have made any changes after executing the AGGREGATE command (see "Compiling the Aggmap"), recompile the aggmap with a COMPILE statement.
-
Add an $AGGREGATE_FROM property to the data variables (see "Using NA Values to Trigger Run-Time Calculations").
-
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:
-
Use an ALLOCATE statement to distribute the data in a new aggregate to the contributing values lower in the hierarchy.
-
Do not precalculate the data that is subject to run-time changes because the stored aggregates cannot be altered to reflect changes made at run time to the contributing values.
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:
-
Change the PRECOMPUTE phrase of the RELATION statement in the aggmap so that new data is aggregated.
-
Execute the AGGREGATE command with the FUNCDATA keyword.
-
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 7-5 Using the AGGREGATE Function as the Formula of an Expression
Example 9-32 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 9-32, 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 because 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 NAExample 7-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 7-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 7-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
...7.9 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)
Parameters
- 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 7-9 Using the AGGREGATION Function to Create a Custom Aggregate
The following lines of code from a program perform these steps:
-
Add the new dimension value
my_timeto thetimedimension.MAINTAIN time ADD 'My_Time'
-
Define the model
mytime_custaggand 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.)
-
Define the
sales_aggmapaggmap.DEFINE sales_aggmap AGGMAP <time cpc <customer product channel> > AGGMAP RELATION prntrel.time RELATION prntrel.chan RELATION prntrel.prod RELATION prntrel.cust END
-
Add the model
mytime_custaggtosales_aggmap.AGGMAP ADD mytime_custagg TO sales_aggmap
-
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
7.10 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 7-10 Displaying a List of the Aggregation Operators
When you issue an 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
7.11 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 7-11 Displaying a List of the Allocation Operators
When you issue an 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
7.12 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)
Examples
Example 7-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
7.13 ANTILOG10
The ANTILOG10 function calculates the value of 10 raised to a specified power.
Return Value
DECIMAL
Syntax
ANTILOG10(n)
Examples
Example 7-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
7.14 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 ...] )
Parameters
- 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
YESorNOvalue. When you indicate one or more dimensions for the result, ANY tests forTRUEvalues 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.
Usage 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 7-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 7-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. Because 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
7.15 ARCCOS
The ARCCOS function calculates the angle value (in radians) of a specified cosine.
Return Value
NUMBER
Syntax
ARCCOS(expression)
Usage 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 7-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
7.16 ARCSIN
The ARCSIN function calculates the angle value (in radians) of a specified sine.
Return Value
NUMBER
Syntax
ARCSIN(expression)
Usage 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 7-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
7.17 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)
Examples
Example 7-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
7.18 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)
Parameters
Examples
Example 7-19 Finding the Arc Tanget
The following example returns the arc tangent of.3 and.2.
SHOW ARCTAN2(.3/.2) .982793723
7.19 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 Argruments that Are Passed Into a Program" .
Return Value
TEXT
Syntax
ARG(n)
Parameters
- 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 7-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 because 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 ========== ========== ==========
7.20 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 7-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 ...
7.21 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)
Parameters
- 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 is0(zero) or negative.
Examples
Example 7-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 ========== ========== ==========
7.22 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 7-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 ========== ========== ==
7.23 ASCII
The ASCII function returns the decimal representation of the first character of an expression.
Return Value
INTEGER
Syntax
ASCII (text-exp)
Usage 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 7-24 Finding the ASCII Decimal Equivalent of a Character
The following example returns the ASCII decimal equivalent of the letter "Q".
SHOW ASCII('Q')
817.24 ASCIISTR
The ASCIISTR function takes a string in any character set and returns an ASCII version of that string.
Returns
NTEXT
Syntax
ASCIISTR(text-exp)
Usage 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:
Implementing a Unicode Solution in the Database for information on Unicode character sets and character semantics.
7.25 AVERAGE
The AVERAGE function calculates the average of the values of an expression.
Return Value
DECIMAL
Syntax
AVERAGE(expression [CACHE] [dimension ...] )
Parameters
- 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.
Usage 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 7-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. Because the region dimension is related to the district dimension, you can specify region instead of district as a dimension for the results of AVERAGE.
7.26 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 the following table.
Syntax
AW(keyword [workspace])
Parameters
- keyword
-
Indicates the specific information you want. The keywords that you can use with the AW function are listed in the following table with the data type of the value they return and the meaning of the information.
Table 7-2 Keywords for AW Function
- 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.
Usage 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 7-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
7.27 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 7-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
7.28 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])
Parameters
- 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 7-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 7-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
7.29 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:
-
The data type of the return value is NTEXT when any of the component dimensions of concatdim is of type NTEXT, or when any component dimension is a conjoint that uses a simple dimension of type NTEXT.
-
The data type of the return value is the data type of the component dimensions when all of the component dimensions have the same data type and when none of the component dimensions is a conjoint.
-
The data type of the return value is TEXT in all other cases.
Syntax
BASEVAL(concatdim)
Parameters
Examples
Example 7-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 7-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
7.30 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)
Parameters
Examples
Example 7-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
7.31 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 ]... )
Parameters
Examples
Example 7-33 Converting Bit Vectors to a Number
SHOW BIN_TO_NUM(1,0,1,0) 10.00
7.32 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)
Examples
See Example 7-65.
7.33 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])
Parameters
- 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 7-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)
7.34 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:
-
FUNCTIONwhen the program was invoked as a function that returns a value. -
COMMANDwhen the program was invoked as a command. -
CALLwhen the program was invoked using a CALL statement. -
TRIGGERwhen the program is a trigger program (that is, when a TRIGGER command associated the program with an object event) was invoked in response to an OLAP DML statement.
Syntax
CALLTYPE
Examples
Example 7-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
7.35 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-value] top-value
Parameters
- 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 7-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 7-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 "Example 7-36" .
7.36 CEIL
The CEIL function returns the smallest whole number greater than or equal to a specified number.
Return Value
NUMBER
Syntax
CEIL(n)
Examples
Example 7-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
7.37 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])
Parameters
- text-expression
-
A
TEXTexpression in which bytes are to be changed. When text-expression is a multilineTEXTexpression, CHANGEBYTES preserves the line breaks in the returned value. - oldtext
-
A
TEXTexpression that contains one or more bytes that to be changed. - newtext
-
A
TEXTexpression that contains one or more bytes that to replace oldtext. - number
-
An
INTEGERthat 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 7-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
7.38 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])
Parameters
- text-expression
-
The
TEXTorNTEXTexpression 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
TEXTorNTEXTexpression that contains one or more characters to be changed. - newtext
-
A
TEXTorNTEXTexpression that contains one or more characters to replace oldtext. - number
-
An
INTEGERthat 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 7-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
7.39 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 partition [,PARTITION partition]...) ] aggmap] )
Parameters
- variable
-
The name of the variable whose aggmap object you want to check for changes.
- partition
-
The name of one or more partitions of variable, 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.
7.40 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 partition [,PARTITION partition]...)] [returntype] )
Parameters
- variable
-
The name of the variable to check for changed values.
- partition
-
The name of one or more partitions of variable, 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.
7.41 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])
Parameters
- expression
-
The expression to be transformed into a multiline text value. When the expression has a data type other than
TEXTorNTEXT, CHARLIST automatically converts the expression toTEXT. - 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 7-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 7-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)
7.42 CHARTOROWID
The CHARTOROWID function converts a value from a text data type to ROWID data type.
Return Value
ROWID
Syntax
CHARTOROWID(char)
Examples
Example 7-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/7.43 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
Parameters
- 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 a 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 LIMIT(product TO FIRST 2)) 82 "Total just over product SHOW TOTAL(CHGDIMS(sales TO product)) 83
7.44 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 ])
Parameters
Examples
Example 7-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
7.45 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]...)
Examples
Example 7-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.807.46 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)
Parameters
- n
-
The number of the column in the current row whose value you want; n can be any
INTEGERexpression.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 7-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
7.47 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]])
Parameters
- 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 7-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 9-72.
SHOW CONTEXT
The statement produces the following output.
democontext1
Example 7-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
7.48 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(expression, type [argument...])
Parameters
- 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 the following table:
Table 7-3 Keywords for the type Parameter 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
TEXTdata types, the result is the value itself and is, therefore, of variable length. -
For
IDandDECIMALdata types, the result is 8 bytes long;IDvalues is blank filled, when necessary. -
For
BOOLEANorINTEGER, 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
BOOLEANdata type.BYTE
Converts a single character into an ASCII
INTEGERvalue in the range 0 to 255. Or BYTE converts anINTEGERwithin 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
DATEdata type.DATETIME
Conversion to Oracle OLAP
DATETIMEdata type.DECIMAL
Conversion to Oracle OLAP
DECIMALdata type.DSINTERVAL
Conversion to Oracle OLAP DML
DSINTERVALdata type.ID
Conversion to Oracle OLAP
IDdata 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 haveID,TEXT,DATE, or RELATION value values. In the case ofTEXTvalues with no alphanumeric equivalent, INFILE converts them to the correct escape sequences.INTEGER
Conversion to Oracle OLAP
INTEGERdata type.LONGINTEGER
Conversion to Oracle OLAP
LONGINTEGERdata type.NTEXT
Conversion to standard Oracle OLAP data types. Corresponds to the
NCHARandNVARCHAR2SQL data types. AnNTEXTcharacter is encoded inUTF8Unicode. This encoding might be different from theNCHARcharacter set of the database, which can beUTF16. A conversion fromNTEXTtoTEXTcan result in data loss when theNTEXTvalue cannot be represented in the database character set.NUMBER [(
p,[s])]Conversion to Oracle OLAP
NUMBERdata 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
ROWIDvalue.SHORTDECIMAL
Conversion to Oracle OLAP
SHORTDECIMALdata type.SHORTINTEGER
Conversion to Oracle OLAP
SHORTINTEGERdata type.TEXT
Conversion to standard Oracle OLAP data types. Corresponds to
CHARandVARCHAR2data types in SQL. ATEXTcharacter is encoded in the database character set.TIMESTAMP
Conversion to Oracle OLAP DML
TIMESTAMPdata type.TIMESTAMP_LTZ
Conversion to Oracle OLAP DML TIMESTAMP_LTZ data type.
TIMESTAMP_TZ
Conversion to Oracle OLAP DML
TIMESTAMP_TZdata type.UROWID
Converts a text value to a
UROWIDvalue.YMINTERVAL
Conversion to Oracle OLAP DML
YMINTERVALdata 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 the following table:
Table 7-4 Syntax for Specifying Conversion to TEXT, NTEXT, ID, DATE, and INFILE
Keyword for type argument When Converting From Syntax for All Parameters 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
BOOLEANorINTEGERBINARY [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 isYES). - 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 isNO, a minus sign precedes negative values. When this argument is omitted, CONVERT uses the current value of the PARENS option (the default isNO). - 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:Mstands for month,Dstands for day, andYstands 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 the following table:
Table 7-5 Values for escape-int Parameter 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
INTEGERexpression that controls the width of the converted result. It can evaluate to1,2, or4bytes. The default width is2forBOOLEAN, or4forINTEGER. When anINTEGERvalue is too large to fit in the specified width, the result isNA. 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.
Usage Notes
INFILE Conversion
The maximum number of characters in a line is 4,000. An error occurs when you try an INFILE conversion that produces a line with more than 4,000 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 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:
-
You can omit punctuation included in the format string from the datetime string if all the digits of the numeric format elements, including leading zeros, are specified. In other words, specify 02 and not 2 for two-digit format elements such as MM, DD, and YY.
-
You can omit time fields found at the end of a format string from the datetime string.
-
When a match fails between a datetime format element and the corresponding characters in the date string, then Oracle attempts alternative format elements, as shown in the following table:
Table 7-6 Oracle Format Matching
| Original Format Element | Additional Format Elements to Try instead Of the Original |
|---|---|
'MM' |
|
|
|
|
|
|
|
|
|
|
|
|
|
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 7-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 7-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'
7.49 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])
Parameters
- 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.
Usage 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 7-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
7.50 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)
Parameters
Examples
Example 7-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 7-53 Calculating the Cosine of an Angle in Degrees
This example calculates the cosine of an angle of 60 degrees. Because 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
7.51 COSH
The COSH function calculates the hyperbolic cosine of an angle expression.
Return Value
NUMBER
Syntax
COSH(expression)
Parameters
Examples
Example 7-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
7.52 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...])
Parameters
- boolean-expression
-
The Boolean expression whose
TRUEvalues 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
YESorNOvalue. When you indicate one or more dimensions for the result, COUNT tests forTRUEvalues 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.
Usage 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 7-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
7.53 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])
Parameters
- 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".
Usage 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
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 7-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
Example 7-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
7.54 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 7-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
7.55 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) ]
Parameters
Examples
Example 7-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
7.56 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)
Parameters
- 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 7-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 7-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
7.57 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 7-62 Retrieving the Database Time Zone
SHOW DBTIMEZONE -08:00
7.58 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)
Parameters
- 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 7-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
7.59 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])
Parameters
- 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.
Usage 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 7-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.86Example 7-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
7.60 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] ] ])
Parameters
- 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
INTEGERexpression 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.
Usage 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 7-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 7-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
7.61 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:
-
The first period for which straight-line depreciation over the remaining periods exceeds the declining balance depreciation for those periods (the default)
-
The period specified by the switch-period argument
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]]]])
Parameters
- 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
INTEGERexpression 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
INTEGERexpression 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
NAor 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
Usage 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 7-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 7-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 7-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.
7.62 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]])
Parameters
- 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
INTEGERexpression 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.
Usage 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 7-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 7-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.
7.63 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]])
Parameters
- 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
INTEGERexpression 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.
Usage 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 7-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 7-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.
7.64 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)
Parameters
Examples
Example 7-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
7.65 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)
Parameters
Examples
Example 7-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
7.66 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...])
Parameters
- 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
YESorNOvalue. When you indicate one or more dimensions for the result, EVERY tests forTRUEvalues 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.
Usage 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 7-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 7-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. Because 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
7.67 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)
Usage 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 7-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
7.69 EVERSION
The EVERSION function returns a text value that specifies the internal Oracle OLAP build number.
Return Value
TEXT
Syntax
EVERSION
Usage 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 7-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
7.70 EXTBYTES
The EXTBYTES function extracts a portion of a text expression.
Return Value
TEXT
Syntax
EXTBYTES(text-expression [start [length]])
Parameters
- text-expression
-
A
TEXTexpression from which a portion is to be extracted. When text-expression is a multilineTEXTvalue, EXTBYTES preserves the line breaks in the returned value. - start
-
An
INTEGERthat 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
INTEGERthat 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 7-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
7.71 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]])
Parameters
- text-expression
-
A
TEXTorNTEXTexpression 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
INTEGERthat 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
INTEGERthat 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 7-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
7.72 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]])
Parameters
- text-expression
-
The
TEXTorNTEXTexpression 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, between1and32767, that represents the column position at which to begin extracting. The column position of the first character in each line of text-expression is1. 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
INTEGERthat 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 7-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
7.73 EXTLINES
The EXTLINES function extracts lines from a multiline text expression.
Return Value
TEXT or NTEXT
Syntax
EXTLINES(text-expression [start [numlines]])
Parameters
- text-expression
-
A multiline
TEXTorNTEXTexpression from whose values one or more lines are to be extracted. - start
-
An
INTEGERthat represents the line number at which to begin extracting. The position of the first line in text-expression is1. When you omit this argument, EXTLINES begins with line 1. - numlines
-
An
INTEGERrepresenting 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 7-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
7.74 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:
-
When extracting from a datetime with a time zone value, the function returns a value in UTC.
-
When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the function returns a text string that is the appropriate time zone name or abbreviation.
-
When you extract any of the other values, the function returns a value in the Gregorian calendar.
-
When the values you specify results in an ambiguity, the function returns NA.
Syntax
EXTRACT(time |timezone_hour_or_nimute |timezone_regn_or_abbr FROM datetime_exp| interval_exp )
Parameters
- 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_TZexpression. - 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, orTIMESTAMP_LTZexpression. See "Datetime Expressions" for information on how to specify these expressions. - interval_exp
-
A
DSINTERVALorYMINTERVALexpression. See "Interval Expressions" for information on how to specify these expressions.
Usage 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 7-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
7.75 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])
Parameters
- 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 9-119.
7.76 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])
Parameters
- 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 the following table.
Table 7-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
NAwhen no name was specified at that time.TRIALSRUN
INTEGER
The number of trials for which data is available; or
NAwhen 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 the following table.
Table 7-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
NAvalues 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.
YESindicates that Oracle OLAP smoothed the data;NOindicates 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 7-8). 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.
Usage Notes
Using Options
You can retrieve information about the options specified for the entire forecast or information about a specific trial.
-
When you want information about the options specified for the entire forecast, do not use the TRIAL keyword. In this case, option can be HANDLEID, TRIALSRUN, or any of the options that you can specify using the FCSET command.
-
When you want information about a specific trial, use the TRIAL trial-num phrase. In this case, option can be ALPHA, BETA, CYCDECAY, GAMMA, MAD, MAPE, METHOD, MPTDECAY, RMSE, SMOOTHING, TRANSFORM, or TRENDHOLD.
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:
-
In a FOR loop, FCQUERY returns data for the current values of the FOR dimensions
-
In a QUAL function, FCQUERY returns data for the specified values of the qualified dimensions.
-
In all other cases, FCQUERY returns data for the first value in status of each of its dimensions.
Examples
Example 7-87 Querying a Forecast
The autofcst program illustrated in Example 9-119 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
7.77 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)
Parameters
- TYPE
-
Returns a text expression that specifies the type of error that has occurred. The types of errors and their meanings are listed in the following table:
Table 7-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 thatNAis 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
INTEGERorDECIMAL(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
NAwhenNAwas 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.
Usage 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 7-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
7.78 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])
Parameters
- 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.
Usage 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 7-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
7.79 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)
Parameters
Usage 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 7-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
7.80 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:
Return Value
INTEGER
Syntax
FILEOPEN(file-name {READ|WRITE|APPEND} [BINARY]) [NLS_CHARSET charset-exp]
Parameters
- 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.
Usage 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 7-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 7-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 7.81 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 7-10 for more information.
Syntax
FILEQUERY(file-id attrib-arg)
Parameters
- 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 7-10 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. The following table 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 7-10 File Attributes Returned by FILEQUERY
Keyword Return Values Return Data Type file-id Parameter APPEND
TRUEwhen the file is open for writing at the end (that is,TRUEfor APPEND and WRITE);FALSEwhen it is not.BOOLEANFileunit number
BMARGIN
The number of blank lines that form the bottom margin.
INTEGERFileunit number
CHANGED
TRUEwhen the file's archive bit is set;FALSEwhen it is not.BOOLEANFileunit number or file name
EOF
TRUEwhen end-of-file has been reached;FALSEwhen it is not.BOOLEANFileunit number
EXISTS
TRUEwhen the file exists;FALSEwhen it is not.BOOLEANFileunit number or file name
FILENAME
The file name associated with the fileunit.
TEXTFileunit 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.
INTEGERFileunit number
LINESLEFT
The number of lines left on the page.
INTEGERFileunit 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.
INTEGERFileunit number
NLS_CHARSET
The character set being used for this fileunit. See the FILEOPEN function for more information.
TEXTFileunit number
NUMBYTES
The size of the file in bytes.
INTEGERFileunit 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.
TEXTFileunit number
PAGENUM
The current page number. See "Paging Attributes".
INTEGERFileunit number
PAGEPRG
The Oracle OLAP program or statement that produces headings when output is paged. See "Paging Attributes".
TEXTFileunit number
PAGESIZE
The number of lines on each page. See "Paging Attributes".
INTEGERFileunit number
PAGING
TRUEwhen the output is formatted in pages;FALSEwhen it is not. See "Paging Attributes".BOOLEANFileunit number
PAUSEATPAGEEND
TRUEwhen Oracle OLAP pauses after each page;FALSEwhen it does not. See "Paging Attributes".BOOLEANFileunit number
R[EAD]
TRUEwhen the file is open for reading;FALSEwhen it is not.BOOLEANFileunit number
RO
TRUEwhen the file's read-only attribute is set;FALSEwhen it is not.BOOLEANFileunit number or file name
TABEXPAND
TRUEwhen the tab characters are expanded when the file is read by FILEGET or FILEREAD;FALSEwhen they are not. See "Tab Treatment".BOOLEANFileunit number or file name
TMARGIN
The number of blank lines that form the top margin.
INTEGERFileunit number
UNIT
The file unit for the specified file name.
INTEGERFile name
W[RITE]
TRUEwhen the file is open for writing;FALSEwhen it is not.BOOLEANFileunit number
Usage 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 7-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
7.82 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:
-
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
FILTERLINES(source-expression filter-expression)
Parameters
- 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.
Usage 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 7-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
7.83 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-expression, bytes [starting-pos [LINENUM]])
Parameters
- text-expression
-
The
TEXTexpression 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 7-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
7.84 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-expression, characters [starting-pos [LINENUM]])
Parameters
- 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 7-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
7.85 FINDLINES
The FINDLINES function determines the position of one or more lines in a multiline text expression.
Return Value
INTEGER
Syntax
FINDLINES(text-expression, lines)
Parameters
- 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 returnsNA. - 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 7-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
7.86 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(loans, rates, n, [time-dimension] [STATUS])
Parameters
- 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.
Usage 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. The following table illustrates how NASKIP affects the results when a loan or rate value is NA for a given time period:
Table 7-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- |
|
Error |
Error |
|
|
Non- |
Interest values ( |
|
|
|
|
|
|
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 7-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. Because 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.
7.87 FLOOR
The FLOOR function returns the largest whole number equal to or less than a specified number.
Return Value
NUMBER
Syntax
FLOOR(n)
Examples
Example 7-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 FLOORstatement produces the result that follows it.SHOW FLOOR(15.7) 15
-
The following
SHOW FLOORstatement produces the result that follows it.SHOW FLOOR(4) 4
-
The following
SHOW FLOORstatement produces the result that follows it.SHOW FLOOR(-6.457) -7
7.88 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(loans, rates, n, [time-dimension] [STATUS])
Parameters
- 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.
Usage 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. See the Usage Notes of the FINTSCHED function for information on 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 7-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). Because 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 7-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
7.89 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)
Parameters
- 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 7-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
7.90 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
Parameters
- 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
NAwhen 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.
Usage 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 "Example 7-103".
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 7-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
7.91 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]...)
Examples
Example 7-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')
HarryExample 7-105 Finding the Largest Numerical Expression
The following statement selects the number with the greatest value.
SHOW GREATEST (5, 3, 18) 18
7.92 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...)
Parameters
- gidrel
-
A grouping id relation for the hierarchical dimension that you previously created using the GROUPINGID command.
Examples
Example 7-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 9-145. 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
7.93 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])
Parameters
- 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 7-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
7.94 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)
7.95 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]
Parameters
- 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 HIERCHECK checks whether there are multiple paths from any child to its parent.
- CONSISTENT
-
Specifies that HIERCHECK 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 HIERCHECK 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.
-
Usage 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 HIERCHECK 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 7-108.
Examples
Example 7-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'.
7.96 HIERHEIGHT
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)
Parameters
- parentrel
-
A child-parent self-relation for the hierarchical dimension. See "Parentrel Relation" for more information.
- level
-
An
INTEGERvalue that represents a level of the hierarchical dimension. The value1(one) represents the lowest-level of the hierarchical dimension.
Usage 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 7-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 7-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
7.97 HIERSHAPE
The HIERSHAPE function identifies whether 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)
Parameters
- 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 whether 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".)
Usage Notes
Star-consistent Hierarchies
A dimension is "star consistent" if all of the level hierarchies of the dimension can be represented as a single table with one column per dimension level and one row per leaf member. A dimension is "star inconsistent" if it cannot be represented in this way.
For an example of a dimension that is not "star consistent", suppose that a time dimension has three levels, YEAR, QUARTER, and MONTH, and that it has two hierarchies, FISCAL and CALENDAR. Both hierarchies have the levels, in descending order, YEAR, QUARTER, and MONTH. Suppose that the hierarchies have the following members.
CALENDAR hierarchy:
CY2012
CYQ1_2012
Jan_2012
Feb_2012
Mar_2012
CYQ2_2012
Apr_2012
May_2012
Jun_2012
...
FISCAL hierarchy:
FY2012
FYQ1_2012
Apr_2012
May_2012
Jun_2012
FYQ2_2012
Jul_2012
Aug_2012
Sep_2012
...
This is a valid and consistent dimension, but it is not star consistent because you cannot represent it using only three columns in a table. For example, the row in which MONTH is "Apr_2012" would need to have two different values for QUARTER, "CYQ2_2012" and "FYQ1_2012", which is not possible.
To make this dimension star consistent, you would need to replace the MONTH level with two distinct levels, FISCAL_MONTH and CALENDAR_MONTH, and similarly replace the year YEAR level with FISCAL_YEAR and CALENDAR_YEAR.
7.98 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:
7.98.1 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])
Parameters
- 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 7-12, Table 7-13, and Table 7-14. 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 7-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 ( |
Table 7-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 7-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 7-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
7.98.2 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
Parameters
- 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:
-
Table 7-17. These choices provide information about statements that are equations. Equations have the form assignment target = expression. The expression can refer to one or more data sources. Assignment targets and data sources can be either variables or dimension values, and they can have qualifiers that affect their dimensionality.
-
Table 7-18. All of these choices (except XEQSTATUS) are relevant only after running a model with a simultaneous block. When the current model has not been compiled, Oracle OLAP returns an error when you use any choice except AVAILABLE or NAME.
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 7-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 7-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 7-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 7-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,lineis dimension-num1andmonthis dimension-num2. For a simultaneous block in the current model, the first dimension of the block is dimension-num1, and so on. A step-forward or step-backward block has a single dimension, so the dimension of the block is always dimension-num1. 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
block1in the preceding model,statementais element-num1;block2is element-num2; andstatementdis element-num3. When you request information aboutblock2,statementbis element-num1andstatementcis element-num2. - 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-num1, 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 7-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 7-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 7-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
7.98.3 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 7-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])
Parameters
- 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 7-19. 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 7-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 7-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
7.98.4 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 7-20.
Syntax
INFO(REGRESS choice [index])
Parameters
- 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 7-20. 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 7-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
Usage 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 7-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
7.99 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)
Examples
Example 7-117 Capitalizing the First Character in Each World
The following example capitalizes each word in the string.
SHOW INITCAP('the soap')
The Soap7.100 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)
Parameters
- 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 valueNO.
Examples
Example 7-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)
7.101 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])
Parameters
- text-expression
-
A
TEXTexpression 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
INTEGERthat 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 addingdeftoabc, resulting in.abc def
Examples
Example 7-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
7.102 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:
-
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
INSCHARS(text-expression characters [after])
Parameters
- 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
INTEGERthat 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 7-120 Inserting Characters in Text
This example shows how to insert the characters there in the TEXT value hellojoe.
INSCHARS('hellojoe', 'there', 5)
hellotherejoe 7.103 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:
-
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.
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])
Parameters
- 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
INTEGERbetween 0 and 32,767 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 32,767 columns of single-byte characters or fewer columns for multi-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 7-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
7.104 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:
-
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
INSLINES(text-expression lines [after])
Parameters
- 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
INTEGERthat represents the line number after which the specified lines are to be inserted. The position of the first line in text-expression is1(one). To insert lines at the very beginning, specify0(zero) for after. When you omit this argument, INSLINES inserts the new lines after the last line of text-expression.
Examples
Example 7-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
7.105 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(dimension, value)
Parameters
- 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.
Usage 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 7-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 7-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 7-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 7-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
7.106 INSTR functions
The INSTR functions (INSTR, INSTRB, and INSTRC) search a string for a substring using characters and return 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.
-
INSTR calculates lengths using characters as defined by the input character set.
-
INSTRB calculates lengths using bytes.
-
INSTRC calculates lengths using Unicode complete characters.
Return Value
A nonzero INTEGER when the search is successful or 0 (zero) when it is not.
Syntax
{INSTR | INSTRB | INSTRC} (string , substring [, position [, occurrence]])
Parameters
- 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.
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 7-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)
14Example 7-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)
2Example 7-129 Using a Multibyte Character Set to Find the Position of a Substring
This example assumes a multibyte database character set.
SHOW INSTRB('Corporate Floor','or',5,2)
27 7.107 INTPART
The INTPART function calculates the integer part of a decimal number by truncating its decimal fraction.
Return Value
INTEGER
Syntax
INTPART(expression)
Usage 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 7-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
7.108 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])
Parameters
- 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 because IRR automatically uses the DAY, WEEK, MONTH, QUARTER, or YEAR dimension of cashflows when you do not specify a value for time-dimension.
Usage 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 7-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
7.109 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)
Parameters
- 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 7-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
7.110 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 to that 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)
7.111 ISEMPTY
7.112 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)
7.113 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)
7.114 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(name, value)
Parameters
- 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
INTEGERfor an INTEGER dimension, or a combination of values enclosed by angle brackets for composites and conjoint dimensions.
Examples
Example 7-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 7-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>')
7.115 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 function.) Also, when the length of the joined line exceeds 32,767 (that is, 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 bytes of a multibyte character. JOINBYTES would then end one line after one of the bytes and start the next line with the next byte of the character.
Return Value
TEXT
Syntax
JOINBYTES(first-expression, next-expression...)
Parameters
- 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 7-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
7.116 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 32,767 bytes, JOINCHARS automatically breaks the line and puts the remaining characters on the next line. When the line break would occur between the bytes of a multibyte character, JOINCHARS does not split the multibyte character; instead, it puts all of the bytes of the multibyte 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:
-
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
JOINCHARS(first-expression, next-expression...)
Parameters
- 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 7-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
7.117 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 32,767 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 7-137.
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-expression, next-expression...)
Parameters
- 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 7-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
7.118 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...)
Parameters
- 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 7-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
7.119 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-exp, base-dimension-exp)
Parameters
- 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 7-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