PK
%Aoa, mimetypeapplication/epub+zipPK %A iTunesMetadata.plistM
LEAST
returns the smallest 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. Text expressions are compared character by character.
To retrieve the largest expression in a list of expressions, use GREATEST
.
Return Value
The data type of the first expression
Syntax
LEAST(expr [, expr ]...)
Arguments
expr
can be any expression.
Examples
LEAST('Harry','Harriot','Harold')
returns the value Harold
.
LEAST(19, 3, 7)
returns the value 3
.
UNISTR
converts a text string to the national character set.
Return Value
NVARCHAR2
Syntax
UNISTR ( string )
Arguments
string
can be any text expression. For portability, Oracle recommends using only ASCII characters and Unicode encoding values as text literals. A Unicode encoding value has the form \xxxx where xxxx is the hexadecimal value of a character. Supplementary characters are encoded as two code units, the first from the high-surrogates range (U+D800 to U+DBFF), and the second from the low-surrogates range (U+DC00 to U+DFFF). To include a literal backslash in the string, precede it with another backslash (\\).
Example
UNISTR('abc\00e5\00f1\00f6')
returns the value abcåñö
.
NUMTOYMINTERVAL
converts a number to an INTERVAL YEAR TO MONTH
data type.
Return Value
INTERVAL YEAR TO MONTH
Syntax
NUMTOYMINTERVAL(n, 'interval_unit')
Arguments
n
can be any numeric expression.
interval_unit
is a text expression that specifies the units. It must resolve to one of the following values:
YEAR
MONTH
These values are case insensitive.
Example
NUMTOYMINTERVAL(18, 'MONTH')
returns the value +01-06
.
ASCIISTR
converts a string in any character set to ASCII in the database character set. Non-ASCII characters are represented as \
xxxx, where xxxx is a UTF-16 code unit.
Return Value
VARCHAR2
Syntax
ASCIISTR(char)
Arguments
char
can be any character string.
Example
ASCIISTR('Skåne')
returns the value Sk\00E5ne
.
OLAP_DML_EXPRESSION
executes an expression in the OLAP DML language.
Return Value
The data type specified in the syntax
Syntax
OLAP_DML_EXPRESSION (expression, datatype)
Arguments
expression
An expression in the OLAP DML language, such as a call to a function or a program.
datatype
The data type of the return value from expression
Example
In this example, the OLAP_DML_EXPRESSION
function executes the OLAP DML RANDOM
function to generate a calculated measure with random numbers between 1.05 and 1.10.
OLAP_DML_EXPRESSION('RANDOM(1.05, 1.10)', NUMBER)
Time | Product | Random |
---|---|---|
2005 | Hardware | 1.07663806 |
2005 | Software/Other | 1.08295738 |
2006 | Hardware | 1.08707305 |
2006 | Software/Other | 1.09730881 |
LNNVL
evaluates a condition when one or both operands of the condition may be null. LNNVL
can be used anywhere a scalar expression can appear, even in contexts where the IS [NOT] NULL
, AND
, or OR
conditions are not valid but would otherwise be required to account for potential nulls.
NOTE: This function returns 1
(true) if the condition is false or unknown, and 0
(false) if the condition is true.
Return Value
NUMBER
Syntax
LNNVL(condition)
Arguments
condition
can be any expression containing scalar values.
Examples
LNNVL(1 > 4)
returns 1
(true).
TRIM
removes leading or trailing characters (or both) from a character string.
Return Value
VARCHAR2
Syntax
TRIM([ { { LEADING | TRAILING | BOTH }[ trim_character ] | trim_character } FROM ] trim_source )
Arguments
LEADING
removes matching characters from the beginning of the string.
TRAILING
removes matching characters from the end of the string.
BOTH
removes matching characters from both the beginning and the end of the string. (Default)
trim_character
is a single character to be removed. By default, it is a space.
trim_source
is the text expression to be trimmed.
Examples
TRIM('0' FROM '00026501.6703000')
returns the value 26501.6703
.
TRIM(LEADING '!' FROM '!!Help! Help!!')
returns the value Help! Help!!
.
CHARTOROWID
converts a value from a text data type to a ROWID
data type.
For more information about the ROWID
pseudocolumn, refer to the Oracle Database SQL Language Reference.
Return Value
ROWID
Syntax
CHARTOROWID(char)
Arguments
char
is a text expression that forms a valid rowid.
Example
chartorowid('AAAN6EAALAAAAAMAAB')
returns the text string AAAN6EAALAAAAAMAAB
as a rowid.
UPPER
converts all alphabetic characters in a text expression to uppercase. The data type of the return value is the same as the original text.
Return Value
CHAR | NCHAR | VARCHAR2 | NVARCHAR2
Syntax
UPPER(char)
Arguments
char
can be any text expression.
Example
UPPER('This is an emergency')
returns the string THIS IS AN EMERGENCY
.
REGEXP_INSTR
searches a string for a regular pattern. It can return an integer indicating either the beginning or the ending position of the matched substring. If no match is found, the function returns 0.
The function evaluates strings using characters as defined by the input character set.
Return Value
NUMBER
Syntax
REGEXP_INSTR (source_char, pattern
[, position
[, occurrence
[, return_option
[, match_parameter ]
]
]
]
)
Arguments
source_char
is the text expression to search.
pattern
is the string to search for. A period matches any character. For a list of operators, refer to the Oracle Database SQL Language Reference, Appendix D, Oracle Regular Expression Support.
position
is a nonzero integer indicating the character of source_char
where the function begins the search. When position
is negative, then the function 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 first character of source_char
.
occurrence
is an integer indicating which occurrence of pattern
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 pattern
.
return_option
is either 0 to return the position of the match (default), or 1 to return the position of the character following the match.
match_parameter
is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values:
c
: Case-sensitive matching.
i
: Case-insensitive matching.
m
: Treat the source string as multiple lines. The function interprets ^
and $
as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. By default, the function treats the source string as a single line.
n
: New-line character is among the characters matched by a period (the wildcard character). By default, it is not.
x
: Ignore whitespace characters.
Example
REGEXP_INSTR('Mississippi', 'i', 1, 3)
searches the string Mississippi
for the third instance of the letter i
, beginning the search at the first letter. It returns the value 8
.
TO_BINARY_DOUBLE
converts a text or numeric expression to a double-precision floating-point number.
Return Value
BINARY_DOUBLE
Syntax
TO_BINARY_DOUBLE (expr [, fmt [, 'nlsparam' ] ])
Arguments
n
can be any text or numeric expression.
fmt
is a text expression that identifies a number format model as described in the Oracle Database SQL Language Reference.
nlsparam
specifies the characters used by these number format elements:
Decimal character
Group separator
Local currency symbol
International currency symbol
This argument has the format shown here:
'NLS_NUMERIC_CHARACTERS = ''dg'' NLS_CURRENCY = ''text'' NLS_ISO_CURRENCY = territory '
The d
is the decimal character, and the g
is the group separator. They must be different single-byte characters. Within the quoted string, use two single quotation marks around the parameter values. Ten characters are available for the currency symbol.
If you omit this argument or any of the NLS parameters, then this function uses the default values for your session to interpret expr
.
Example
All of these examples return the value 1.235E+003
:
TO_BINARY_DOUBLE(1234.56) TO_BINARY_DOUBLE('$1,234.56', '$9,999.99') TO_BINARY_DOUBLE('1.234,56', '9G999D99', 'NLS_NUMERIC_CHARACTERS='',.''')
NUMTODSINTERVAL
converts a number to an INTERVAL DAY TO SECOND
data type.
Return Value
INTERVAL DAY TO SECOND
Syntax
NUMTODSINTERVAL(n, 'interval_unit')
Arguments
n
can be any numeric expression.
interval_unit
is a text expression that specifies the units. It must resolve to one of the following values:
DAY
HOUR
MINUTE
SECOND
These values are case insensitive.
Example
NUMTODSINTERVAL(100, 'MINUTE')
returns the value +00 01:40:00.000000.
SOUNDEX
returns a character string containing the phonetic representation of a text expression. This function lets you compare words that are spelled differently but sound alike.
The function is based on the algorithm described in Donald Knuth's The Art of Computer Programming. This algorithm was designed specifically for English. Its results for other languages other than English are unpredictable and typically unsatisfactory.
Return Value
VARCHAR2
Syntax
SOUNDEX (char)
Arguments
char
can be any text expression.
Example
All of these examples return the value D500
:
soundex('Donna') soundex('Diane') soundex('Dana')
LTRIM
scans a text expression from left to right and removes all the characters that match the characters in the trim expression, until it finds an unmatched character. The data type of the return value is the same as the original text.
Return Value
CHAR | NCHAR | VARCHAR2 | NVARCHAR2
Syntax
LTRIM(char [, set ])
Arguments
char
is the text expression to be trimmed.
set
is a text expression with the characters to remove. The default value of set
is a single blank.
Examples
LTRIM(' . . . .Last Word', ' .')
returns the value Last Word
.
COALESCE
returns the first non-null expression in a list of expressions, or null when all of the expressions evaluate to null.
Return Value
Data type of the first argument
Syntax
COALESCE(expr [, expr ]...)
Arguments
expr
can be any expression.
Examples
COALESCE(5, 8, 3)
returns the value 5
.
COALESCE(NULL, 8, 3)
returns the value 8
.
NEW_TIME
converts the date and time from one time zone to another. Before using this function, set the NLS_DATE_FORMAT
parameter to display 24-hour time.
Return Value
DATE
Syntax
NEW_TIME(date, timezone1, timezone2)
Arguments
date
is a datetime expression to be converted to a new time zone.
timezone1
is the time zone of date
.
timezone2
is the new time zone.
The time zone arguments are limited to the values inTable 3-2. For other time zones, use FROM_TZ
.
Table 3-2 Time Zones
Time Zone | Abbreviation |
---|---|
Alaska-Hawaii Daylight Time |
HDT |
Alaska-Hawaii Standard Time |
HST |
Atlantic Daylight Time |
ADT |
Atlantic Standard Time |
AST |
Bering Daylight Time |
BDT |
Bering Standard Time |
BST |
Central Daylight Time |
CDT |
Central Standard Time |
CST |
Eastern Daylight Time |
EDT |
Eastern Standard Time |
EST |
Greenwich Mean Time |
GMT |
Mountain Daylight Time |
MDT |
Mountain Standard Time |
MST |
Newfoundland Standard Time |
NST |
Pacific Daylight Time |
PDT |
Pacific Standard Time |
PST |
Yukon Daylight Time |
YDT |
Yukon Standard Time |
YST |
Example
NEW_TIME(SYSDATE, 'PST', 'EST')
returns a value such as 18-JAN-07 04:38:07
in Eastern Standard Time when SYSDATE
is 18-JAN-07 01:38:07
in Pacific Standard Time. For this example, NLS_DATE_FORMAT
is set to DD-MON-RR HH:MI:SS
.
HIER_DEPTH
returns a number representing the level depth of either all members of a hierarchy or a particular member, where 0 is the top level. The hierarchy can be either level-based or value-based.
Return Value
NUMBER
Syntax
HIER_DEPTH ( [member_expression] [WITHIN] {DIMENSION dimension_id | HIERARCHY hierarchy_id} )
Arguments
member_expression
Identifies a single dimension member within the hierarchy used for the calculation. If this optional argument is specified, then the result does not vary across dimension members.
dimension_id
The dimension over which the values are calculated using the default hierarchy.
hierarchy_id
The hierarchy over which the values are calculated. If dimension_id
is used instead, the default hierarchy is used.
Example
This example returns the depth of each member in the default hierarchy of the Time dimension.
HIER_DEPTH(DIMENSION "TIME")
Time | Depth |
---|---|
2006 | 1 |
Q1.06 | 2 |
Q2.06 | 2 |
Q3.06 | 2 |
Q4.06 | 2 |
JAN-06 | 3 |
FEB-06 | 3 |
MAR-06 | 3 |
APR-06 | 3 |
MAY-06 | 3 |
JUN-06 | 3 |
JUL-06 | 3 |
AUG-06 | 3 |
SEP-06 | 3 |
OCT-06 | 3 |
NOV-06 | 3 |
DEC-06 | 3 |
The next example returns 2
as the depth of Italy in the default Customer hierarchy.
HIER_DEPTH('ITA' WITHIN DIMENSION CUSTOMER)
LEAD
returns the value of an expression for a later time period.
Return Value
The same data type as the value expression
Syntax
LEAD (lead_args) OVER (lead_clause)
lead_args::=
expression, offset [, {default_expression | CLOSEST} ]
lead_clause::=
[ {DIMENSION dimension_id | HIERARCHY hierarchy_id} ] [ [BY] { LEVEL | PARENT | GREGORIAN {YEAR | QUARTER | MONTH | WEEK | DAY} | ANCESTOR AT { DIMENSION LEVEL dim_level_id | HIERARCHY LEVEL hier_level_id } } [POSITION FROM {BEGINNING | END}] ]
Arguments
expression
A dimensional expression whose values you want to calculate.
offset
A numeric expression for the number of periods to count forward from the current time period.
default_expression
The value returned when offset
does not identify a valid period. This clause is either an expression of any data type or the CLOSEST
keyword for the closest match. The closest match is the first member when counting forward.
dimension_id
The Time dimension over which the lead is calculated.
hierarchy_id
The hierarchy over which the lead is calculated. Otherwise, the default hierarchy for dimension_id
is used.
dim_level_id
The name of a level of dimension_id
.
hier_level_id
The name of a level of hierarchy_id
.
BY subclause
The BY subclause identifies the range of time periods used when counting the offset. Following are descriptions of the keywords:
Offset Unit | Description |
---|---|
LEVEL | The member at the same level offset periods after the current member. (Default) |
PARENT | The member at the same level with the same parent offset periods after the current member. |
GREGORIAN YEAR | The period at the same level with a start date exactly offset years after the start date of the current period. |
GREGORIAN QUARTER | The period at the same level with a start date exactly offset quarters after the start date of the current period. |
GREGORIAN MONTH | The period at the same level with a start date exactly offset months after the start date of the current period. |
GREGORIAN WEEK | The period at the same level with a start date exactly offset weeks after the start date of the current period. |
GREGORIAN DAY | The period at the same level with a start date exactly offset days after the start date of the current period. |
ANCESTOR | The period at the same level as the current period and whose ancestor is offset positions after the ancestor of the current period. |
Examples
This calculation returns the value of the next time period based on calendar quarter. The sample output from this calculation appears in the Next Qtr column.
LEAD (GLOBAL.UNITS_CUBE.UNITS, 1, CLOSEST) OVER (DIMENSION GLOBAL.TIME BY ANCESTOR AT DIMENSION LEVEL GLOBAL.TIME.CALENDAR_QUARTER)
Time | Parent | Units | Next Qtr |
---|---|---|---|
2005 | TOTAL | 565718 | -- |
Q1-05 | CY2005 | 143607 | 138096 |
Q2-05 | CY2005 | 138096 | 138953 |
Q3-05 | CY2005 | 138953 | 145062 |
Q4-05 | CY2005 | 145062 | 146819 |
Jan-05 | CY2005.Q1 | 50098 | 40223 |
Feb-05 | CY2005.Q1 | 43990 | 45477 |
Mar-05 | CY2005.Q1 | 49519 | 52396 |
Apr-05 | CY2005.Q2 | 40223 | 45595 |
May-05 | CY2005.Q2 | 45477 | 46882 |
Jun-05 | CY2005.Q2 | 52396 | 46476 |
Jul-05 | CY2005.Q3 | 45595 | 47476 |
Aug-05 | CY2005.Q3 | 46882 | 47496 |
Sep-05 | CY2005.Q3 | 46476 | 50090 |
Oct-05 | CY2005.Q4 | 47476 | 47776 |
Nov-05 | CY2005.Q4 | 47496 | 47695 |
Dec-05 | CY2005.Q4 | 50090 | 51348 |
Related Topics
DENSE_RANK
orders the members of a dimension based on the values of an expression. The function returns the sequence numbers of the dimension members.
DENSE_RANK
assigns the same minimum rank to identical values, and returns the results in a sequential list. The result may be fewer ranks than values in the series. For example, DENSE_RANK
may return 1, 2, 3, 3, 4 for a series of five dimension members.
Return Value
NUMBER
Syntax
DENSE_RANK ( ) OVER (rank_clause)
rank_clause::=
{ DIMENSION dimension_id | HIERARCHY hierarchy_id } ORDER BY order_by_clause [, order_by_clause]... [ WITHIN { LEVEL | PARENT | ANCESTOR AT { DIMENSION LEVEL dim_lvl_id | HIERARCHY LEVEL hier_level_id } } ]
order_by_clause::=
expression [ASC | DESC] [NULLS {FIRST | LAST}]
Arguments
dimension_id
The dimension over which the values are calculated using the default hierarchy.
hierarchy_id
The hierarchy over which the values are calculated. If dimension_id
is used instead, the default hierarchy is used.
ORDER BY
Provides the basis for the ranking. You can provide additional ORDER BY
clauses to break any ties in the order.
expression
Provides the values to use as the basis for the rankings.
ASC | DESC
Sorts the ranking from smallest to largest (ascending) or from largest to smallest (descending).
NULLS {FIRST | LAST}
Determines whether members with null values are listed first or last.
WITHIN
Selects a set of related dimension members to be ranked.
LEVEL
ranks all members at the same level.
PARENT
ranks members at the same level with the same parent.
ANCESTOR
ranks all members at the same level and with the same ancestor at a specified level.
dim_level_id
The name of a level of dimension_id
.
hier_level_id
The name of a level of hierarchy_id
.
Example
This example ranks time periods within a calendar year by Unit Cost, using the default Calendar hierarchy. Notice that although two months (JAN-02 and JUL-02) have the same value and the same rank (6), the ranking continues at 7 for JUN-02.
DENSE_RANK() OVER (DIMENSION "TIME" ORDER BY PRICE_CUBE.UNIT_COST DESC NULLS LAST WITHIN ANCESTOR AT DIMENSION LEVEL TIME.CALENDAR_YEAR)
Product | Time | Cost | Dense Rank |
---|---|---|---|
Deluxe Mouse | MAR-02 | 24.05 | 1 |
Deluxe Mouse | APR-02 | 23.95 | 2 |
Deluxe Mouse | FEB-02 | 23.94 | 3 |
Deluxe Mouse | AUG-02 | 23.88 | 4 |
Deluxe Mouse | MAY-02 | 23.84 | 5 |
Deluxe Mouse | JAN-02 | 23.73 | 6 |
Deluxe Mouse | JUL-02 | 23.73 | 6 |
Deluxe Mouse | JUN-02 | 23.72 | 7 |
Deluxe Mouse | SEP-02 | 23.71 | 8 |
Deluxe Mouse | NOV-02 | 23.65 | 9 |
Deluxe Mouse | DEC-02 | 23.62 | 10 |
Deluxe Mouse | OCT-02 | 23.37 | 11 |
Related Topics
LAG_VARIANCE
returns the difference between values for the current time period and an earlier period.
Return Value
The same data type as the value expression
Syntax
LAG_VARIANCE (lag_args) OVER (lag_clause)
lag_args::=
expression, offset [, {default_expression | CLOSEST} ]
lag_clause::=
[ {DIMENSION dimension_id | HIERARCHY hierarchy_id} ] [ [BY] { LEVEL | PARENT | GREGORIAN {YEAR | QUARTER | MONTH | WEEK | DAY} | ANCESTOR AT { DIMENSION LEVEL dim_level_id | HIERARCHY LEVEL hier_level_id } } [POSITION FROM {BEGINNING | END}] ]
Arguments
expression
A dimensional expression whose values you want to calculate.
offset
A numeric expression for the number of periods to count back from the current time period.
default_expression
The value returned when offset
does not identify a valid period. This clause is either an expression of any data type or the CLOSEST
keyword for the closest match. The closest match is the first member when counting back.
dimension_id
The Time dimension over which the lag is calculated.
hierarchy_id
The hierarchy over which the lag is calculated. Otherwise, the default hierarchy for dimension_id
is used.
dim_level_id
The name of a level of dimension_id
.
hier_level_id
The name of a level of hierarchy_id
.
BY subclause
The BY subclause identifies the range of time periods used when counting the offset. Following are descriptions of the keywords:
Offset Unit | Description |
---|---|
LEVEL | The member at the same level offset periods before the current member. (Default) |
PARENT | The member at the same level with the same parent offset periods before the current member. |
GREGORIAN YEAR | The period at the same level with a start date exactly offset years before the start date of the current period. |
GREGORIAN QUARTER | The period at the same level with a start date exactly offset quarters before the start date of the current period. |
GREGORIAN MONTH | The period at the same level with a start date exactly offset months before the start date of the current period. |
GREGORIAN WEEK | The period at the same level with a start date exactly offset weeks before the start date of the current period. |
GREGORIAN DAY | The period at the same level with a start date exactly offset days before the start date of the current period. |
ANCESTOR | The period at the same level as the current period and whose ancestor is offset positions before the ancestor of the current period. |
Examples
This example returns the difference in values between the current period and the equivalent period in the prior year.
LAG_VARIANCE (GLOBAL.UNITS_CUBE.UNITS, 1) OVER (HIERARCHY GLOBAL.TIME.CALENDAR ANCESTOR AT DIMENSION LEVEL GLOBAL.TIME.CALENDAR_YEAR)
Time | Units | Last Year | Difference |
---|---|---|---|
Q1.05 | 143607 | 146529 | -2922 |
Q2.05 | 138096 | 143070 | -4974 |
Q3.05 | 138953 | 148292 | -9339 |
Q4.05 | 145062 | 149528 | -4466 |
Q1.06 | 146819 | 143607 | 3212 |
Q2.06 | 145233 | 138096 | 7137 |
Q3.06 | 143572 | 138953 | 4619 |
Q4.06 | 149305 | 145062 | 4243 |
Related Topics
Expression Syntax Reference
Release 11.2
E23381-01
June 2011
Oracle OLAP Expression Syntax Reference, Release 11.2
E23381-01
Copyright © 2006, 2011, Oracle and/or its affiliates. All rights reserved.
Primary Author: Donna Carver
Contributor: David Greenfield, Anne Murphy, Martin Roth, Fuad Sheehab
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle America, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.
This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.
SYS_CONTEXT
returns the value of an attribute of a named context. The context, attribute, and value must already be defined in the database. If the context is not defined, SYS_CONTEXT
returns NULL
.
Return Value
VARCHAR2
Syntax
SYS_CONTEXT ('namespace', 'parameter')
Arguments
namespace
can be any named context in the database. USERENV
is a built-in context that describes the current session.
parameter
is a defined attribute of namespace
. Table 3-3 describes the predefined attributes of USERENV
that are most likely to have values. For a complete list, refer to the SYS_CONTEXT entry in the Oracle Database SQL Language Reference.
Table 3-3 USERENV Attributes
USERENV Attribute | Description |
---|---|
|
The identity used for authentication, such as database user name, schema name, or operating system login name. |
|
The method of authentication, such as |
|
The session edition identifier, such as |
|
The session edition name, such as |
|
The name of the currently active default schema, such as |
|
The numeric identifier of the currently active default schema, such as |
|
The name of the database user whose privileges are currently active, such as |
|
The numeric identifier of the database user whose privileges are currently active, such as |
|
Data Guard role of the database: |
|
The network domain of the database as specified by the |
|
The name of the database as specified by the |
|
The unique name of the database within the domain as specified by the |
|
The enterprise-wide identity of the user, or |
|
Job identifier of the current session if a client foreground process opened it; otherwise, |
|
The number used in the System Global Area by the globally accessed context. |
|
The global user identification from Oracle Internet Directory for Enterprise User Security logins; otherwise, |
|
The name of the client host computer. |
|
The way the user schema was created in the database: |
|
The identification number of the current instance, such as |
|
The name of the database instance. |
|
The IP address of the client, such as |
|
|
|
A short name for the session language, such as |
|
The language, territory, and database character set in the form |
|
The application name set through the |
|
The network protocol being used for communication, such as |
|
The session calendar, such as |
|
The session currency mark, such as $. |
|
The session date format, such as |
|
The session date language, such as |
|
|
|
The session territory, such as |
|
The operating system user name of the client process that initiated the database session. |
|
The host name of the computer where the database instance is running. |
|
The name of the service the session is connected to., such as |
|
The database user name or schema name that identified the user at login, such as |
|
The session identifier, such as |
|
The session number, such as |
Example
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')
returns a value such as DD-MON-RR
.
The LENGTH
functions return the length of a text expression.
LENGTH
counts the number of characters.
LENGTHB
uses bytes instead of characters.
LENGTHC
uses Unicode complete characters.
Return Value
NUMBER
Syntax
{ LENGTH | LENGTHB | LENGTHC }(char)
Arguments
char
is any text expression.
Example
LENGTH('CANDIDE')
returns the value 7
.
TZ_OFFSET
returns the time zone offset from Coordinated Universal Time (UTC).
Return Value
VARCHAR2
Syntax
TZ_OFFSET({ 'time_zone_name' | '{ + | - } hh:mi' | SESSIONTIMEZONE | DBTMEZONE } )
Arguments
time_zone_name
is the name of a time zone.
hh:mm
are hours and minutes. This argument simply returns itself.
SESSIONTIMEZONE
returns the session time zone.
DBTIMEZONE
returns the database time zone.
Examples
TZ_OFFSET('US/Eastern')
returns the offset -04:00
during Daylight Savings Time.
TZ_OFFSET('EST')
returns the offset -05:00
.
TZ_OFFSET(DBTIMEZONE)
returns the offset -07:00
for Mountain Standard Time.
EXTRACT
returns the value of a specified field from a datetime or interval expression.
Return Value
NUMBER
Syntax
EXTRACT( { { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | TIMEZONE_ABBR } } FROM { datetime_value_expression | interval_value_expression } )
Arguments
datetime_value_expression
is an expression with a datetime data type.
interval_value_expression
is an expression with an interval data type.
Example
EXTRACT(MONTH FROM CURRENT_TIMESTAMP)
returns the value 8
for August when the current timestamp is 08-AUG-06 01.10.55.330120 PM -07:00
.
EXTRACT(TIMEZONE_HOUR FROM CURRENT_TIMESTAMP)
returns the value -7
from the same example.
DBTIMEZONE
returns the value of the database time zone as either a time zone offset from Coordinated Universal Time (UTC) or a time zone region name.
To obtain other time zone offsets, use TZ_OFFSET
.
Return Value
VARCHAR2
Syntax
DBTIMEZONE
Example
DBTIMEZONE
returns the offset -08:00
for Mountain Standard Time.
NLS_CHARSET_NAME
returns the name corresponding to a specified character set number.
Return Value
VARCHAR2
Syntax
NLS_CHARSET_NAME (charset_id)
Arguments
charset_id
is a valid character set number or one of these keywords:
CHAR_CS
represents the database character set.
NCHAR_CS
represents the national character set. The national character set for the database can be either UTF-8 or AL16UTF16 (default). However, the national character set for analytic workspaces is always UTF-8.
If the number does not correspond to a character set, then the function returns NULL.
Example
NLS_CHARSET_NAME(2000)
returns the value AL16UTF16
.
NLS_CHARSET_ID
returns the identification number corresponding to a specified character set name.
Return Value
NUMBER
Syntax
NLS_CHARSET_ID ( charset_name )
Arguments
charset_name
is a VARCHAR2 expression that is a valid character set name.
Example
NLS_CHARSET_ID('AL32UTF8')
returns the value 873
.
HIER_LEVEL
returns the level of either all members of a hierarchy or a particular member. The hierarchy must be level-based.
Return Value
VARCHAR2
Syntax
HIER_LEVEL ( [member_expression] [WITHIN] {DIMENSION dimension_id | HIERARCHY hierarchy_id} )
Arguments
member_expression
Identifies a single dimension member within the hierarchy used for the calculation. If this optional argument is specified, then the result does not vary across dimension members.
dimension_id
The dimension over which the values are calculated using the default hierarchy.
hierarchy_id
The hierarchy over which the values are calculated. If dimension_id
is used instead, the default hierarchy is used.
Example
This example returns the level of each member of the default hierarchy of the Time dimension.
HIER_LEVEL(DIMENSION "TIME")
Time | Level |
---|---|
2006 | CALENDAR_YEAR |
Q1.06 | CALENDAR_QUARTER |
Q2.06 | CALENDAR_QUARTER |
Q3.06 | CALENDAR_QUARTER |
Q4.06 | CALENDAR_QUARTER |
JAN-06 | MONTH |
FEB-06 | MONTH |
MAR-06 | MONTH |
APR-06 | MONTH |
MAY-06 | MONTH |
JUN-06 | MONTH |
JUL-06 | MONTH |
AUG-06 | MONTH |
SEP-06 | MONTH |
OCT-06 | MONTH |
NOV-06 | MONTH |
DEC-06 | MONTH |
The next example returns ACCOUNT
as the level of Business World in the Market hierarchy of the Customer dimension.
HIER_LEVEL('BUSN WRLD' WITHIN HIERARCHY CUSTOMER.MARKET)
LOWER
converts all alphabetic characters in a text expression to lowercase. The data type of the return value is the same as the original text.
Return Value
CHAR | NCHAR | VARCHAR2 | NVARCHAR2
Syntax
LOWER(char)
Arguments
char
can be any text expression.
Example
LOWER('STOP SHOUTING')
returns the string stop shouting
.
RTRIM
scans a text expression from right to left and removes all the characters that match the characters in the trim expression, until it finds an unmatched character. The data type of the return value is the same as the original text.
Return Value
CHAR | NCHAR | VARCHAR2 | NVARCHAR2
Syntax
RTRIM(char [, set ])
Arguments
char
is the text expression to be trimmed.
set
is a text expression with the characters to remove. The default value of set
is a single blank.
Examples
RTRIM('You did that!?!?!?!', '?!')
returns the value You did that
.
RTRIM('3.14848', '84')
returns the text value 3.1
.
MAX
returns the largest of a selection of data values calculated over a Time dimension.
The GREGORIAN
relations superimpose the Gregorian calendar on the Time dimension. These relations can be useful for calculations on fiscal and nonstandard hierarchies.
Return Value
NUMBER
Syntax
MAX (value_expr) OVER (window_clause)
window_clause::=
[ { DIMENSION dimension_id | HIERARCHY hierarchy_id } ] BETWEEN preceding_boundary | following_boundary [WITHIN { PARENT | LEVEL | GREGORIAN {YEAR | QUARTER | MONTH | WEEK} | ANCESTOR AT { DIMENSION LEVEL dim_level_id | HIERARCHY LEVEL hier_level_id } } ]
preceding_boundary::=
{UNBOUNDED PRECEDING | expression PRECEDING} AND { CURRENT MEMBER | expression {PRECEDING | FOLLOWING} | UNBOUNDED FOLLOWING }
following_boundary::=
{CURRENT MEMBER | expression FOLLOWING} AND { expression FOLLOWING | UNBOUNDED FOLLOWING }
Arguments
value_expr
A dimensional expression whose values you want to calculate.
dimension_id
The Time dimension over which the values are calculated using the default hierarchy.
hierarchy_id
The hierarchy over which the values are calculated. If dimension_id
is used instead, the default hierarchy is used.
dim_level_id
The name of a level of dimension_id
.
hier_level_id
The name of a level of hierarchy_id
.
boundaries
The preceding_boundary and following_boundary identify a range of time periods within the group identified by the dimension or hierarchy.
UNBOUNDED
starts with the first period or ends with the last period of the group.
CURRENT MEMBER
starts or ends the calculation at the current time period.
expression
A numeric value identifying a period at a particular distance from the current time period that starts or ends the range.
WITHIN subclause
Identifies the range of time periods used in the calculation. Following are descriptions of the keywords.
Range | Description |
---|---|
LEVEL | Calculates all time periods at the same level. (Default) |
PARENT | Calculates time periods at the same level with the same parent. |
GREGORIAN YEAR | Calculates time periods within the same Gregorian year. |
GREGORIAN QUARTER | Calculates time periods within the same Gregorian quarter. |
GREGORIAN MONTH | Calculates time periods within the same Gregorian month. |
GREGORIAN WEEK | Calculates time periods within the same Gregorian week. |
ANCESTOR | Includes time periods at the same level and with the same ancestor at a specified level. |
Example
This example calculates a moving maximum within the calendar year.
MAX(GLOBAL.UNITS_CUBE.UNITS) OVER (DIMENSION GLOBAL.TIME BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT DIMENSION LEVEL GLOBAL.TIME.CALENDAR_YEAR)
Time | Units | Maximum |
---|---|---|
JAN-06 | 47776 | 47776 |
FEB-06 | 47695 | 47776 |
MAR-06 | 51348 | 51348 |
APR-06 | 47005 | 51348 |
MAY-06 | 52809 | 52809 |
JUN-06 | 45419 | 52809 |
JUL-06 | 48388 | 52809 |
AUG-06 | 48830 | 52809 |
SEP-06 | 46354 | 52809 |
OCT-06 | 47411 | 52809 |
NOV-06 | 46842 | 52809 |
DEC-06 | 55052 | 55052 |
Related Topics
GREATEST
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. Text expressions are compared character by character.
To retrieve the smallest expression in a list of expressions, use LEAST
.
Return Value
The data type of the first expression
Syntax
GREATEST(expr [, expr ]...)
Arguments
expr
can be any expression.
Examples
GREATEST('Harry','Harriot','Harold')
returns the value Harry
.
GREATEST(7, 19, 3)
returns the value 19
.
NULLIF
compares one expression with another. It returns NULL
when the expressions are equal, or the first expression when they are not.
Return Value
Data type of the first argument
Syntax
NULLIF(expr1, expr2)
Arguments
expr1
is the base expression. It cannot be a literal null.
expr2
is the expression to compare with the base expression.
Example
NULLIF('red', 'Red')
returns the value red
.
The OLAP row functions extend the syntax of the SQL row functions for use with dimensional objects. If you use the SQL row functions, then this syntax is familiar. You can use these functions on relational data when loading it into cubes and cube dimensions, and with the OLAP functions when creating calculated measures.
This chapter describes the row functions of the OLAP expression syntax. It contains these topics:
A B C D E F G H I L M N O P R S T U V W
A
B
C
D
E
F
G
H
I
L
M
N
O
P
R
S
T
U
V
W
The row functions are grouped into the following categories:
These functions accept numeric input and return numeric values:
These functions accept character input and return character values:
These functions return information about a character set:
These functions accept character input and return numeric values:
These functions operate on date, timestamp, or interval values:
These functions determine the greatest or least value in a set of values:
These functions change a value from one data type to another:
These functions return a numeric value for each input value:
These functions facilitate null handling:
These functions provide information about the instance and the session:
SHARE
calculates the ratio of an expression's value for the current dimension member to the value for a related member of the same dimension. Arguments to this function identify which related member is used in the ratio.
Return Value
NUMBER
Syntax
share_expression::=
SHARE (expression share_clause [share_clause]... )
share_clause::=
OF { DIMENSION dimension_id | HIERARCHY hierarchy_id } { PARENT | TOP | MEMBER 'member_name' | DIMENSION LEVEL dim_level_id | HIERARCHY LEVEL hier_level_id }
Arguments
expression
A dimensional expression whose values you want to calculate.
dimension_id
A dimension of expression
. The default hierarchy is used in the calculation. If you want to use a different hierarchy, use the HIERARCHY
argument instead.
hierarchy_id
A level hierarchy of expression
member_name
A member of the specified dimension or hierarchy.
dim_level_id
The name of a level of dimension_id
.
hier_level_id
The name of a level of hierarchy_id
.
Share is calculated with these formulas:
Keyword | Formula |
---|---|
PARENT | current member/parent |
TOP | current member/root ancestor |
MEMBER | current member/specified member |
DIMENSION LEVEL | current member/ancestor at specified level or null if the current member is above the specified level. |
Example
This example calculates the percent share of the parent member for each product. The results appear in the Share of Parent column.
(SHARE(UNITS_CUBE.SALES OF HIERARCHY PRODUCT.PRIMARY PARENT))*100
The next example calculates the percent share of Total Product for each product. The results appear in the Share of Top column.
(SHARE(UNITS_CUBE.SALES OF HIERARCHY PRODUCT.PRIMARY TOP))*100
Product | Parent | Sales | Share of Parent | Share of Top |
---|---|---|---|---|
Desktop PCs | HRD | 74556528 | 60 | 54 |
Portable PCs | HRD | 18338225 | 15 | 13 |
CD/DVD | HRD | 16129497 | 13 | 12 |
Memory | HRD | 5619219 | 5 | 4 |
Modems/Fax | HRD | 5575726 | 4 | 4 |
Monitors | HRD | 3972142 | 3 | 3 |
Accessories | SFT | 6213535 | 49 | 5 |
Operating Systems | SFT | 4766857 | 37 | 3 |
Documentation | SFT | 1814844 | 14 | 1 |
Hardware | TOTAL | 124191336 | 91 | 91 |
Software/Other | TOTAL | 12795236 | 9 | 9 |
LOCALTIMESTAMP
returns the current date and time in the session time zone.
Return Value
TIMESTAMP
Syntax
LOCALTIMESTAMP [ (timestamp_precision) ]
Arguments
timestamp_precision
specifies the fractional second precision of the time value returned.
Examples
LOCALTIMESTAMP
returns a value such as 09-AUG-06 08.11.37.045186 AM
.
LOCALTIMESTAMP(2)
returns a value such as 09-AUG-06 08.11.37.040000 AM
.
NANVL
checks if a value is a number. If it is, then NANVL
returns that value. If not, it returns an alternate value. This function is typically used to convert a binary double or binary float NaN (Not a Number) value to zero or null.
Return Value
datatype
Syntax
NANVL (expression, alternate)
Arguments
expression
can be any value.
alternate is the numeric value returned if expression is not a number.
CURRENT_TIMESTAMP
returns the current date and time in the session time zone. The time zone offset identifies the current local time of the session.
Return Value
TIMESTAMP WITH TIME ZONE
Syntax
CURRENT_TIMESTAMP [ (precision) ]
Arguments
precision
specifies the fractional second precision of the returned time value. The default value is 6.
Examples
CURRENT_TIMESTAMP
returns a value such as 08-AUG-06 11.18.31.082257 AM -08:00
.
CURRENT_TIMESTAMP(2)
returns a value such as 08-AUG-06 11.18.31.08 AM -08:00
.
TO_CHAR(number)
converts a numeric expression to a text value in the database character set.
Return Value
VARCHAR2
Syntax
TO_CHAR(n [, fmt [, 'nlsparam' ] ])
Arguments
n
is a numeric expression to be converted.
fmt
is a text expression that identifies a number format model as described in the Oracle Database SQL Language Reference.
nlsparam
specifies the characters that are returned by these number format elements:
Decimal character
Group separator
Local currency symbol
International currency symbol
This argument has the format shown here:
'NLS_NUMERIC_CHARACTERS = ''dg'' NLS_CURRENCY = ''text'' NLS_ISO_CURRENCY = territory '
The characters d
and g
represent the decimal character and group separator, respectively. They must be different single-byte characters. Within the quoted string, use two single quotation marks around the parameter values. Ten characters are available for the currency symbol.
If you omit this argument or any of the NLS parameters, then this function uses the default values for your session.
Examples
TO_CHAR(1234567, 'C9G999G999D99')
returns a text string such as USD1,234,567.00
.
TO_CHAR(1234567, 'C9G999G999D99', 'NLS_ISO_CURRENCY = SPAIN')
returns the text string EUR1,234,567.00
.
RAWTOHEX
converts raw data to a character value containing its hexadecimal representation.
Return Value
VARCHAR2
Syntax
RAWTOHEX(raw)
Arguments
raw
can be any scalar data type other than LONG
, LONG RAW
, CLOB
, BLOB
, or BFILE
.
Example
RAWTOHEX(NLSSORT('Rumpelstiltskin'))
converts the raw value returned by NLSSORT
to the hexadecimal value 52756D70656C7374696C74736B696E00
.
The OLAP expression syntax includes analytic functions, arithmetic operators, and single-row functions. The OLAP syntax is an extension of the SQL syntax. If you have used SQL analytic functions or single-row functions, then this syntax is familiar to you.
This preface contains these topics:
This document is intended for anyone who wants to create calculated measures or transform the data stored in relational tables for use in dimensional database objects such as cubes, cube dimensions, and measures.
For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc
.
Access to Oracle Support
Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info
or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs
if you are hearing impaired.
For more information, see these documents in the Oracle Database 11.2 documentation set:
Explains how SQL applications can extend their analytic processing capabilities and manage summary data by using the OLAP option of Oracle Database.
Oracle OLAP Customizing Analytic Workspace Manager
Describes how to customize Analytic Workspace Manager with XML documents and Java plug-ins.
Contains a complete description of the OLAP Data Manipulation Language (OLAP DML), which is used to define and manipulate analytic workspace objects.
Contains full descriptions of the data dictionary views for cubes, cube dimensions, and other dimensional objects.
Oracle Database PL/SQL Packages and Types Reference
Contains full descriptions of several PL/SQL packages for managing cubes.
Oracle OLAP Java API Developer's Guide
Introduces the Oracle OLAP API, a Java application programming interface for Oracle OLAP, which is used for defining, building, and querying dimensional objects in the database.
Oracle OLAP Java API Reference
Describes the classes and methods in the Oracle OLAP Java API for defining, building, and querying dimensional objects in the database.
These text conventions are used in this document:
Convention | Meaning |
---|---|
boldface | Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary. |
italic | Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values. |
monospace | Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter. |
The syntax in this reference is presented in a simple variation of Backus-Naur Form (BNF) that uses the following symbols and conventions:
Symbol or Convention | Description |
---|---|
[ ] | Brackets enclose optional items. |
{ } | Braces enclose a choice of items, only one of which is required. |
| | A vertical bar separates alternatives within brackets or braces. |
... | Ellipses indicate that the preceding syntactic element can be repeated. |
delimiters | Delimiters other than brackets, braces, and vertical bars must be entered as shown. |
boldface | Words appearing in boldface are keywords. They must be typed as shown. (Keywords are case-sensitive in some, but not all, operating systems.) Words that are not in boldface are placeholders for which you must substitute a name or value. |
CHR
converts an integer to the character with its binary equivalent in either the database character set or the national character set.
For single-byte character sets, if n
> 256, then CHR
converts the binary equivalent of mod(n, 256)
.
For the Unicode national character sets and all multibyte character sets, n
must resolve to one entire code point. Code points are not validated, and the result of specifying invalid code points is indeterminate.
Return Value
VARCHAR2 | NVARCHAR2
Syntax
CHR(n [ USING NCHAR_CS ])
Arguments
n
is a numeric expression.
USING NCHAR_CS
returns a character in the national character set. Otherwise, the return value is in the database character set. The OLAP engine uses the UTF8 national character set, so the return value may be different from the SQL CHR
function, which uses the database UTF16 national character set.
Example
CHR(67)
, CHR(67 USING NCHAR_CS)
, and CHR(323)
all return the letter C
on an ASCII-based system with the WE8DEC database character set and the UTF8 national character set. CHR(323)
is evaluated as CHR(MOD(323, 256))
.
ADD_MONTHS
returns a date that is a specified number of months after a specified date.
When the starting date is the last day of the month or when the returned month has fewer days, then ADD_MONTHS
returns the last day of the month. Otherwise, the returned day is the starting day.
Return Value
DATE
Syntax
ADD_MONTHS(date, integer)
Arguments
date
is the starting date.
integer
is the number of months to be added to the starting date.
Example
ADD_MONTHS('17-JUN-06', 1)
returns the value 17-JUL-06
.
ROW_NUMBER
orders the members of a dimension based on the values of an expression. The function returns the sequence numbers of the dimension members.
ROW_NUMBER
assigns a unique rank to each dimension member; for identical values, the rank is arbitrary. For example, ROW_NUMBER
always returns 1, 2, 3, 4, 5 for a series of five dimension members, even when they have the same value.
Return Value
NUMBER
Syntax
ROW_NUMBER ( ) OVER (rank_clause)
rank_clause::=
{ DIMENSION dimension_id | HIERARCHY hierarchy_id } ORDER BY order_by_clause [, order_by_clause]... [ WITHIN { PARENT | LEVEL | ANCESTOR AT { DIMENSION LEVEL dim_lvl_id | HIERARCHY LEVEL hier_level_id } } ]
order_by_clause::=
expression [ASC | DESC] [NULLS {FIRST | LAST}]
Arguments
dimension_id
The dimension over which the values are calculated using the default hierarchy.
hierarchy_id
The hierarchy over which the values are calculated. If dimension_id
is used instead, the default hierarchy is used.
ORDER BY
Provides the basis for the ranking. You can provide additional ORDER BY
clauses to break any ties in the order.
expression
Provides the values to use as the basis for the rankings.
ASC | DESC
Sorts the ranking from smallest to largest (ascending) or from largest to smallest (descending).
NULLS {FIRST | LAST}
Determines whether members with null values are listed first or last.
WITHIN
Selects a set of related dimension members to be ranked.
PARENT
ranks members at the same level with the same parent.
LEVEL
ranks all members at the same level.
ANCESTOR
ranks all members at the same level and with the same ancestor at a specified level.
dim_level_id
The name of a level of dimension_id
.
hier_level_id
The name of a level of hierarchy_id
.
Example
This example ranks time periods within a calendar year by Unit Cost. Notice even though two months (JAN-02 and JUL-02) have the same value, they are assigned sequential numbers (6 and 7).
ROW_NUMBER() OVER (HIERARCHY TIME.CALENDAR ORDER BY PRICE_CUBE.UNIT_COST DESC NULLS LAST WITHIN ANCESTOR AT DIMENSION LEVEL TIME.CALENDAR_YEAR)
Product | Time | Cost | Row Number |
---|---|---|---|
Deluxe Mouse | MAR-02 | 24.05 | 1 |
Deluxe Mouse | APR-02 | 23.95 | 2 |
Deluxe Mouse | FEB-02 | 23.94 | 3 |
Deluxe Mouse | AUG-02 | 23.88 | 4 |
Deluxe Mouse | MAY-02 | 23.84 | 5 |
Deluxe Mouse | JAN-02 | 23.73 | 6 |
Deluxe Mouse | JUL-02 | 23.73 | 7 |
Deluxe Mouse | JUN-02 | 23.72 | 8 |
Deluxe Mouse | SEP-02 | 23.71 | 9 |
Deluxe Mouse | NOV-02 | 23.65 | 10 |
Deluxe Mouse | DEC-02 | 23.62 | 11 |
Deluxe Mouse | OCT-02 | 23.37 | 12 |
Related Topics
TO_DATE
converts a text expression to a DATE
data type.
Return Value
DATE
Syntax
TO_DATE(char [, fmt [, 'nlsparam' ] ])
Arguments
char
is a text expression that represents a date.
fmt
is a datetime model format specifying the format of char
. The default date format is determined implicitly by the NLS_TERRITORY
initialization parameter or can be set explicitly by the NLS_DATE_FORMAT
parameter. For data type formats, refer to the Oracle Database SQL Language Reference.
nlsparam
specifies the language of char
. This argument can have this form:
'NLS_DATE_LANGUAGE = language'
By default, char
is in the session date language.
Examples
TO_DATE('October 13, 2008', 'MONTH DD, YYYY')
returns the value 13-OCT-08
.
TO_DATE('13 Octubre 2008', 'dd month yyyy', 'NLS_DATE_LANGUAGE=SPANISH')
also returns the value 13-OCT-08
NVL
replaces a null with a string. NVL
returns the replacement string when the base expression is null, and the value of the base expression when it is not null.
To replace an expression with one value if it is null and a different value if it is not, use NVL2
.
Return Value
Data type of the first argument
Syntax
NVL(expr1, expr2)
Arguments
expr1
is the base expression that is evaluated.
expr2
is the replacement string that is returned when expr1
is null.
Examples
NVL('First String', 'Second String')
returns the value First String
.
NVL(null, 'Second String')
returns the value Second String
.
LEAD_VARIANCE
returns the difference between values for the current time period and the offset period.
Return Value
The same data type as the value expression
Syntax
LEAD_VARIANCE (lead_args) OVER (lead_clause)
lead_args::=
expression, offset [, {default_expression | CLOSEST} ]
lead_clause::=
[ {DIMENSION dimension_id | HIERARCHY hierarchy_id} ] [ [BY] { LEVEL | PARENT | GREGORIAN {YEAR | QUARTER | MONTH | WEEK | DAY} | ANCESTOR AT { DIMENSION LEVEL dim_level_id | HIERARCHY LEVEL hier_level_id } } [POSITION FROM {BEGINNING | END}] ]
Arguments
expression
A dimensional expression whose values you want to calculate.
offset
A numeric expression for the number of periods to count forward from the current time period.
default_expression
The value returned when offset
does not identify a valid period. This clause is either an expression of any data type or the CLOSEST
keyword for the closest match. The closest match is the first member when counting forward.
dimension_id
The Time dimension over which the lead is calculated.
hierarchy_id
The hierarchy over which the lead is calculated. Otherwise, the default hierarchy for dimension_id
is used.
dim_level_id
The name of a level of dimension_id
.
hier_level_id
The name of a level of hierarchy_id
.
BY subclause
The BY subclause identifies the range of time periods used when counting the offset. Following are descriptions of the keywords:
Offset Unit | Description |
---|---|
LEVEL | The member at the same level offset periods after the current member. (Default) |
PARENT | The member at the same level with the same parent offset periods after the current member. |
GREGORIAN YEAR | The period at the same level with a start date exactly offset years after the start date of the current period. |
GREGORIAN QUARTER | The period at the same level with a start date exactly offset quarters after the start date of the current period. |
GREGORIAN MONTH | The period at the same level with a start date exactly offset months after the start date of the current period. |
GREGORIAN WEEK | The period at the same level with a start date exactly offset weeks after the start date of the current period. |
GREGORIAN DAY | The period at the same level with a start date exactly offset days after the start date of the current period. |
ANCESTOR | The period at the same level as the current period and whose ancestor is offset positions after the ancestor of the current period. |
Examples
This calculation returns the difference between the current value and the value of the next time period based on calendar quarter. The sample output from this calculation appears in the Difference column.
LEAD_VARIANCE (GLOBAL.UNITS_CUBE.UNITS, 1, CLOSEST) OVER (DIMENSION GLOBAL.TIME BY ANCESTOR AT DIMENSION LEVEL GLOBAL.TIME.CALENDAR_QUARTER)
This calculation generates the data in the Percent column.
Time | Parent | Units | Next Qtr | Difference |
---|---|---|---|---|
2005 | TOTAL | 565718 | -- | -- |
Q1-05 | CY2005 | 143607 | 138096 | 5511 |
Q2-05 | CY2005 | 138096 | 138953 | -857 |
Q3-05 | CY2005 | 138953 | 145062 | -6109 |
Q4-05 | CY2005 | 145062 | 146819 | -1757 |
Jan-05 | CY2005.Q1 | 50098 | 40223 | 9875 |
Feb-05 | CY2005.Q1 | 43990 | 45477 | -1487 |
Mar-05 | CY2005.Q1 | 49519 | 52396 | -2877 |
Apr-05 | CY2005.Q2 | 40223 | 45595 | -5372 |
May-05 | CY2005.Q2 | 45477 | 46882 | -1405 |
Jun-05 | CY2005.Q2 | 52396 | 46476 | 5920 |
Jul-05 | CY2005.Q3 | 45595 | 47476 | -1881 |
Aug-05 | CY2005.Q3 | 46882 | 47496 | -614 |
Sep-05 | CY2005.Q3 | 46476 | 50090 | -3614 |
Oct-05 | CY2005.Q4 | 47476 | 47776 | -300 |
Nov-05 | CY2005.Q4 | 47496 | 47695 | -199 |
Dec-05 | CY2005.Q4 | 50090 | 51348 | -1258 |
Related Topics
TO_CHAR(datetime)
converts a datetime or interval expression to a text string in a specified format.
Return Value
VARCHAR2
Syntax
TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])
Arguments
datetime
is a datetime expression to be converted to text.
interval
is an interval expression to be converted to text.
fmt
is a datetime model format specifying the format of char
. The default date format is determined implicitly by the NLS_TERRITORY
initialization parameter or can be set explicitly by the NLS_DATE_FORMAT
parameter. For data type formats, refer to the Oracle Database SQL Language Reference.
nlsparam
specifies the language in which month and day names and abbreviations are returned. This argument can have this form:
'NLS_DATE_LANGUAGE = language'
By default, the return value is in the session date language.
Examples
TO_CHAR(SYSDATE)
returns a value such as 11-APR-08
.
TO_CHAR(SYSDATE, 'Day: MONTH DD, YYYY')
returns a value such as Friday : APRIL 11, 2008
.
TO_CHAR(SYSDATE, 'Day: MONTH DD, YYYY', 'NLS_DATE_LANGUAGE = Spanish')
returns a value such as Viernes : ABRIL 11, 2008
.
SESSIONTIMEZONE
returns the time zone of the current session. The return type is a time zone offset from Coordinated Universal Time (UTC) or a time zone region name.
Return Value
VARCHAR2
Syntax
SESSIONTIMEZONE
Example
SESSIONTIMEZONE
returns the value -05:00
for Eastern Standard Time.
ROUND
returns a number rounded to a specified number of places.
Return Value
NUMBER
Syntax
ROUND(n [, integer ])
Arguments
n
is the number to round.
integer
is the number of decimal places of the rounded number. A negative value rounds to the left of the decimal point. The default value is 0.
Examples
ROUND(15.193)
returns the value 15
.
ROUND(15.193,1)
returns the value 15.2
.
ROUND(15.193,-1)
returns the value 20
.
MIN
returns the smallest of a selection of data values calculated over a Time dimension.
The GREGORIAN
relations superimpose the Gregorian calendar on the Time dimension. These relations can be useful for calculations on fiscal and nonstandard hierarchies.
Return Value
NUMBER
Syntax
MIN (value_expr) OVER (window_clause)
window_clause::=
[ { DIMENSION dimension_id | HIERARCHY hierarchy_id } ] BETWEEN preceding_boundary | following_boundary [WITHIN { LEVEL | PARENT | GREGORIAN {YEAR | QUARTER | MONTH | WEEK} | ANCESTOR AT { DIMENSION LEVEL dim_level_id | HIERARCHY LEVEL hier_level_id } } ]
preceding_boundary::=
{UNBOUNDED PRECEDING | expression PRECEDING} AND { CURRENT MEMBER | expression {PRECEDING | FOLLOWING} | UNBOUNDED FOLLOWING }
following_boundary::=
{CURRENT MEMBER | expression FOLLOWING} AND { expression FOLLOWING | UNBOUNDED FOLLOWING }
Arguments
value_expr
A dimensional expression whose values you want to calculate.
dimension_id
The Time dimension over which the values are calculated using the default hierarchy.
hierarchy_id
The hierarchy over which the values are calculated. If dimension_id
is used instead, the default hierarchy is used.
dim_level_id
The name of a level of dimension_id
.
hier_level_id
The name of a level of hierarchy_id
.
boundaries
The preceding_boundary and following_boundary identify a range of time periods within the group identified by the dimension or hierarchy.
UNBOUNDED
starts with the first period or ends with the last period of the group.
CURRENT MEMBER
starts or ends the calculation at the current time period.
expression
A numeric value identifying a period at a particular distance from the current time period that starts or ends the range.
WITHIN subclause
Identifies the range of time periods used in the calculation. Following are descriptions of the keywords.
Range | Description |
---|---|
LEVEL | Calculates all time periods at the same level. (Default) |
PARENT | Calculates time periods at the same level with the same parent. |
GREGORIAN YEAR | Calculates time periods within the same Gregorian year. |
GREGORIAN QUARTER | Calculates time periods within the same Gregorian quarter. |
GREGORIAN MONTH | Calculates time periods within the same Gregorian month. |
GREGORIAN WEEK | Calculates time periods within the same Gregorian week. |
ANCESTOR | Includes time periods at the same level and with the same ancestor at a specified level. |
Example
This example calculates the minimum value between the current member and all subsequent members in the same calendar year. The selection of the data displays the minimum values for the months in 2006.
MIN(GLOBAL.UNITS_CUBE.UNITS) OVER (DIMENSION GLOBAL.TIME BETWEEN CURRENT MEMBER AND UNBOUNDED FOLLOWING WITHIN ANCESTOR AT DIMENSION LEVEL GLOBAL.TIME.CALENDAR_YEAR)
Time | Units | Minimum |
---|---|---|
JAN-06 | 47776 | 45419 |
FEB-06 | 47695 | 45419 |
MAR-06 | 51348 | 45419 |
APR-06 | 47005 | 45419 |
MAY-06 | 52809 | 45419 |
JUN-06 | 45419 | 45419 |
JUL-06 | 48388 | 46354 |
AUG-06 | 48830 | 46354 |
SEP-06 | 46354 | 46354 |
OCT-06 | 47411 | 46842 |
NOV-06 | 46842 | 46842 |
DEC-06 | 55052 | 55052 |
Related Topics
NLSSORT
returns the string of bytes used to sort a text string. You can use this function to specify sorting and comparison operations based on a linguistic sort sequence rather than on the binary value of a string.
Note: NLSSORT
returns a RAW
value, which you may pass to another function. To create a measure or a calculated measure for the values returned by NLSSORT
, use the RAWTOHEX
function.
For more information about linguistic sorting, refer to the Oracle Database Globalization Support Guide.
Return Value
RAW
Syntax
NLSSORT(char [, 'nlsparam' ])
Arguments
char
is a text expression.
nlsparam
is a linguistic sort sequence in the form NLS_SORT =
sort
[_ai |_ci]
, where sort
is an NLS language. You can add a suffix to the language to modify the sort: _ai
for an accent-insensitive sort, or _ci
for a case-insensitive sort.
Example
NLSSORT('Rumpelstiltskin')
returns the value 52756D70656C7374696C74736B696E00
for a default linguistic sort, which in this case is American.
NLS_INITCAP
returns a string in which each word begins with a capital followed by lower-case letters. White space and nonalphanumeric characters delimit the words.
Return Value
VARCHAR2
Syntax
NLS_INITCAP (char [, 'nlsparam' ])
Arguments
char
can be any text string.
nlsparam
can have the form 'NLS_SORT =
sort
'
where sort
is either a linguistic sort sequence or BINARY
. The linguistic sort sequence handles special linguistic requirements for case conversions. These requirements can result in a return value of a different length than char
. If you omit nlsparam
, then this function uses the default sort sequence for your session.
Example
NLS_INITCAP('WALKING&THROUGH*A*winter wonderland')
returns the value Walking#Through*A*Winter Wonderland
.
NLS_INITCAP('ijsland')
returns the value Ijsland
, but NLS_INITCAP(NLS_INITCAP('ijsland', 'NLS_SORT = XDutch')
returns IJsland
.
REGEXP_REPLACE
searches a string for a regular pattern and replaces it with another string. By default, the function returns source_char
with every occurrence of the regular expression pattern replaced with replace_string
.
Return Value
CHAR | NCHAR | VARCHAR2 | NVARCHAR2
Syntax
REGEXP_REPLACE(source_char, pattern
[, replace_string
[, position
[, occurrence
[, match_parameter ]
]
]
]
)
Arguments
source_char
is the text expression that is searched.
pattern
is the text expression to search for. It is usually a text literal and can contain up to 512 bytes. For a list of operators, refer to the Oracle Database SQL Language Reference, Appendix D, "Oracle Regular Expression Support."
replace_string
is the text that replaces pattern
in source_char
.
position
is a nonzero integer indicating the character of source_char
where the function begins the search. When position
is negative, then the function 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 first character of source_char
.
occurrence
is an integer indicating which occurrence of pattern
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 pattern
.
match_parameter
is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values:
c
: Case-sensitive matching.
i
: Case-insensitive matching.
m
: Treat the source string as multiple lines. The function interprets ^
and $
as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. By default, the function treats the source string as a single line.
n
: New-line character is among the characters matched by a period (the wildcard character). By default, it is not.
x
: Ignore whitespace characters.
Example
REGEXP_REPLACE('500 Oracle Parkway, Redwood Shores, CA', '( ){2,}', ' ')
eliminates extra spaces and returns the string
500 Oracle Parkway, Redwood Shores, CA
REMAINDER
returns a rounded remainder when one number is divided by another using this equation:
n2 - (n1 * N)
where N
is the integer nearest n2/n1
.
Return Value
NUMBER
Syntax
REMAINDER(n2, n1)
Arguments
n1
is a numeric expression for the divisor.
n2
is a numeric expression for the dividend.
Example
REMAINDER(18,7)
returns the value -3
.
HIER_ORDER
sorts the members of a dimension with children immediately after their parents, and returns a sequential number for each member.
Return Value
NUMBER
Syntax
HIER_ORDER ( [member_expression] [WITHIN] {DIMENSION dimension_id | HIERARCHY hierarchy_id} )
Arguments
member_expression
Identifies a single dimension member within the hierarchy used for the calculation. If this optional argument is specified, then the result does not vary across dimension members.
dimension_id
The dimension over which the values are calculated using the default hierarchy.
hierarchy_id
The hierarchy over which the values are calculated. If dimension_id
is used instead, the default hierarchy is used.
Example
This example orders the values of the Time dimension:
HIER_ORDER(DIMENSION "TIME")
Time | Order |
---|---|
2006 | 138 |
Q1.06 | 139 |
JAN-06 | 140 |
FEB-06 | 141 |
MAR-06 | 142 |
Q2.06 | 143 |
APR-06 | 144 |
MAY-06 | 145 |
JUN-06 | 146 |
Q3.06 | 147 |
JUL-06 | 148 |
AUG-06 | 149 |
SEP-06 | 150 |
Q4.06 | 151 |
OCT-06 | 152 |
NOV-06 | 153 |
DEC-06 | 154 |
The next example returns 78
as the order number of Business World in the Market hierarchy of the Customer dimension.
HIER_ORDER('BUSN WRLD' WITHIN HIERARCHY CUSTOMER.MARKET)
LOG
computes the logarithm of an expression.
Return Value
NUMBER
Syntax
LOG(n2, n1)
Arguments
n2
is the base by which to compute the logarithm.
n1
is the value whose logarithm is calculated. It can be any numeric expression that is greater than zero. When the value is equal to or less than zero, LOG
returns a null value.
Example
LOG(10,100)
returns the value 2
.
ORA_HASH
generates hash values for an expression. You can use it to randomly assign a set of values into several buckets for analysis, or to generate a set of random numbers.
Return Value
NUMBER
Syntax
ORA_HASH (expr [, max_bucket [, seed_value ] ])
Arguments
expr
can be any expression that provides the data for generating the hash values.
max_bucket
is the maximum bucket number. For example, when max_bucket
is set to 5, ORA_HASH returns values of 0 to 5, creating six buckets. Set this value from 0 to 4294967295 or 2^32-1 (default).
seed_value
is a value used by ORA_HASH
to generate the hash values. Enter a different seed_value
for different results. Set this value from 0 (default) to 4294967295 or 2^32-1.
Example
ORA_HASH(PRODUCT_CUBE.PRICES, 5)
returns a value in the range of 0 to 5 for each value of the Prices measure, as shown in the Hash 5 column. The rows are also sorted on the Hash 5 column.
ORA_HASH(PRODUCT_CUBE.PRICES, 5, 13)
also returns values in the range of 0 to 5, but uses a different seed.
Product | Prices | Hash 5 | Seed 13 |
---|---|---|---|
ENVY STD | 200539.83 | 0 | 4 |
ENVY EXE | 255029.31 | 0 | 5 |
1GB USB DRV | 44645.65 | 1 | 2 |
DLX MOUSE | 1379.49 | 2 | 2 |
144MB DISK | 3011.43 | 2 | 5 |
512 USB DRV | 22139.99 | 2 | 2 |
19 SVGA | 34837.16 | 3 | 0 |
56KPS MODEM | 12478 | 3 | 2 |
ENVY EXT KBD | 4312.22 | 3 | 5 |
17 SVGA | 22605.55 | 4 | 1 |
EXT CD ROM | 17990.14 | 4 | 0 |
ENVY ABM | 205462.25 | 5 | 1 |
TRANSLATE
converts a text string between the database character set and the national character set.
Note: The TRANSLATE USING
function is supported primarily for ANSI compatibility. Oracle recommends that you use the TO_CHAR
and TO_NCHAR
functions for converting data to the database or national character sets. TO_CHAR
and TO_NCHAR
can take as arguments a greater variety of data types than TRANSLATE USING
, which accepts only character data.
Return Value
VARCHAR2 | NVARCHAR2
Syntax
TRANSLATE (char USING { CHAR_CS | NCHAR_CS } )
Arguments
char
is a text expression to be converted to the database character set (USING CHAR_CS
) or the national character set (USING NCHAR_CS
).
Example
TRANSLATE('north by northwest' USING NCHAR_CS)
returns the value north by northwest
in the national character set.
TO_NCHAR(character)
converts a character string to the national character set.
Return Value
NVARCHAR2
Syntax
TO_NCHAR(exp)
Arguments
exp
is a text expression. If it contains characters that are not represented in the national character set, then the conversion results in a loss of data.
Example
TO_NCHAR('David Ortiz')
returns the value David Ortiz
in the national character set.
This appendix lists the reserved words for the OLAP expression syntax.
The following are reserved words for the OLAP Expression Syntax. You should not use them or Oracle Database reserved words as object names. However, if you do, be sure to enclose them in double quotes in the expression syntax.
Refer to the Oracle Database SQL Language Reference for a list of Oracle Database reserved words.
Table A-1 lists symbols that are used in the OLAP expression syntax. To enter them as literal characters, enclose them in quotes the same as any other literal text.
Table A-1 OLAP Expression Syntax Symbols
Symbol | Description |
---|---|
|
Comma |
|
Concatenate |
|
Divide |
|
Equals |
|
Greater than |
|
Greater than or equal to |
|
Left bracket |
|
Left parenthesis |
|
Less than |
|
Less than or equal to |
|
Minus |
|
Not equal to |
|
Not equal to |
|
Not equal to |
|
Plus |
|
Right bracket |
|
Right parenthesis |
|
Star |
TO_BINARY_FLOAT
converts a text or numeric expression to a single-precision floating-point number.
Return Value
BINARY_FLOAT
Syntax
TO_BINARY_FLOAT (expr [, fmt [, 'nlsparam' ] ])
Arguments
n
can be any text or numeric expression.
fmt
is a text expression that identifies a number format model as described in the Oracle Database SQL Language Reference.
nlsparam
specifies the characters used by these number format elements:
Decimal character
Group separator
Local currency symbol
International currency symbol
This argument has the format shown here:
'NLS_NUMERIC_CHARACTERS = ''dg'' NLS_CURRENCY = ''text'' NLS_ISO_CURRENCY = territory '
The d
is the decimal character, and the g
is the group separator. They must be different single-byte characters. Within the quoted string, use two single quotation marks around the parameter values. Ten characters are available for the currency symbol.
If you omit this argument or any of the NLS parameters, then this function uses the default values for your session to interpret expr
.
Examples
All of these examples return the value 1.235E+003
:
TO_BINARY_FLOAT(1234.56) TO_BINARY_FLOAT('$1,234.56', '$9,999.99') TO_BINARY_FLOAT('1.234,56', '9G999D99', 'NLS_NUMERIC_CHARACTERS='',.''')
NEXT_DAY
returns the date of the first instance of a particular day of the week that follows the specified date.
Return Value
DATE
Syntax
NEXT_DAY(date, char)
Arguments
date
is a datetime expression.
char
is a text expression that identifies a day of the week (for example, Monday) in the language of your session.
Example
NEXT_DAY('11-SEP-01', 'Monday')
returns the value 17-SEP-01
.
NLS_UPPER
converts all alphabetic characters in a text expression to uppercase. The data type of the return value is the same as the original text.
Return Value
CHAR | NCHAR | VARCHAR2 | NVARCHAR2
Syntax
NLS_UPPER (char [, 'nlsparam' ])
Arguments
char
can be any text expression.
nlsparam
is a linguistic sort sequence in the form NLS_SORT =
sort
[_ai |_ci]
, where sort
is an NLS language. You can add a suffix to the language to modify the sort: _ai
for an accent-insensitive sort, or _ci
for a case-insensitive sort.
Example
NLS_UPPER('This is an emergency')
returns the string THIS IS AN EMERGENCY
.
LAG
returns the value from an earlier time period.
Return Value
The same data type as the value expression
Syntax
LAG (lag_args) OVER (lag_clause)
lag_args::=
expression, offset [, {default_expression | CLOSEST} ]
lag_clause::=
[ {DIMENSION dimension_id | HIERARCHY hierarchy_id} ] [ [BY] { LEVEL | PARENT | GREGORIAN {YEAR | QUARTER | MONTH | WEEK | DAY} | ANCESTOR AT { DIMENSION LEVEL dim_level_id | HIERARCHY LEVEL hier_level_id } } [POSITION FROM {BEGINNING | END}] ]
Arguments
expression
A dimensional expression whose values you want to calculate.
offset
A numeric expression for the number of periods to count back from the current time period.
default_expression
The value returned when offset
does not identify a valid period. This clause is either an expression of any data type or the CLOSEST
keyword for the closest match. The closest match is the first member when counting back.
dimension_id
The Time dimension over which the lag is calculated.
hierarchy_id
The hierarchy over which the lag is calculated. Otherwise, the default hierarchy for dimension_id
is used.
dim_level_id
The name of a level of dimension_id
.
hier_level_id
The name of a level of hierarchy_id
.
BY subclause
The BY subclause identifies the range of time periods used when counting the offset. Following are descriptions of the keywords:
Offset Unit | Description |
---|---|
LEVEL | The member at the same level offset periods before the current member. (Default) |
PARENT | The member at the same level with the same parent offset periods before the current member. |
GREGORIAN YEAR | The period at the same level with a start date exactly offset years before the start date of the current period. |
GREGORIAN QUARTER | The period at the same level with a start date exactly offset quarters before the start date of the current period. |
GREGORIAN MONTH | The period at the same level with a start date exactly offset months before the start date of the current period. |
GREGORIAN WEEK | The period at the same level with a start date exactly offset weeks before the start date of the current period. |
GREGORIAN DAY | The period at the same level with a start date exactly offset days before the start date of the current period. |
ANCESTOR | The period at the same level as the current period and whose ancestor is offset positions before the ancestor of the current period. |
Example
This example returns the value from the prior year for each period.
LAG(UNITS_CUBE.UNITS, 1) OVER (HIERARCHY "TIME".CALENDAR ANCESTOR AT DIMENSION LEVEL "TIME".CALENDAR_YEAR)
Time | Units | Last Year |
---|---|---|
Q1.05 | 143607 | 146529 |
Q2.05 | 138096 | 143070 |
Q3.05 | 138953 | 148292 |
Q4.05 | 145062 | 149528 |
Q1.06 | 146819 | 143607 |
Q2.06 | 145233 | 138096 |
Q3.06 | 143572 | 138953 |
Q4.06 | 149305 | 145062 |
Related Topics
NVL2
returns one value when the value of a specified expression is not null, or another value when the value of the specified expression is null.
To replace a null value with a string, use NVL
.
Return Value
Data type of the first argument
Syntax
NVL2(expr1, expr2, expr3)
Arguments
expr1
is the base expression whose value this function evaluates.
expr2
is an expression whose value is returned when expr1
is not null.
expr3
is an expression whose value is returned when expr1
is null.
Example
NVL2('Which string?', 'First String', 'Second String')
returns the value First String
.
LAG_VARIANCE_PERCENT
returns the percent difference between values for the current time period and an earlier period.
Return Value
The same data type as the value expression
Syntax
LAG_VARIANCE_PERCENT (lag_args) OVER (lag_clause)
lag_args::=
expression, offset [, {default_expression | CLOSEST} ]
lag_clause::=
[ {DIMENSION dimension_id | HIERARCHY hierarchy_id} ] [ [BY] { LEVEL | PARENT | GREGORIAN {YEAR | QUARTER | MONTH | WEEK | DAY} | ANCESTOR AT { DIMENSION LEVEL dim_level_id | HIERARCHY LEVEL hier_level_id } } [POSITION FROM {BEGINNING | END}] ]
Arguments
expression
A dimensional expression whose values you want to calculate.
offset
A numeric expression for the number of periods to count back from the current time period.
default_expression
The value returned when offset
does not identify a valid period. This clause is either an expression of any data type or the CLOSEST
keyword for the closest match. The closest match is the first member when counting back.
dimension_id
The Time dimension over which the lag is calculated.
hierarchy_id
The hierarchy over which the lag is calculated. Otherwise, the default hierarchy for dimension_id
is used.
dim_level_id
The name of a level of dimension_id
.
hier_level_id
The name of a level of hierarchy_id
.
BY subclause
The BY subclause identifies the range of time periods used when counting the offset. Following are descriptions of the keywords:
Offset Unit | Description |
---|---|
LEVEL | The member at the same level offset periods before the current member. (Default) |
PARENT | The member at the same level with the same parent offset periods before the current member. |
GREGORIAN YEAR | The period at the same level with a start date exactly offset years before the start date of the current period. |
GREGORIAN QUARTER | The period at the same level with a start date exactly offset quarters before the start date of the current period. |
GREGORIAN MONTH | The period at the same level with a start date exactly offset months before the start date of the current period. |
GREGORIAN WEEK | The period at the same level with a start date exactly offset weeks before the start date of the current period. |
GREGORIAN DAY | The period at the same level with a start date exactly offset days before the start date of the current period. |
ANCESTOR | The period at the same level as the current period and whose ancestor is offset positions before the ancestor of the current period. |
Examples
This example returns the percent difference in value between the current period and the equivalent period in the prior year.
LAG_VARIANCE_PERCENT (GLOBAL.UNITS_CUBE.UNITS, 1) OVER (HIERARCHY GLOBAL.TIME.CALENDAR ANCESTOR AT DIMENSION LEVEL GLOBAL.TIME.CALENDAR_YEAR)
Time | Units | Last Year | Difference | Percent |
---|---|---|---|---|
Q1.05 | 143607 | 146529 | -2922 | -.02 |
Q2.05 | 138096 | 143070 | -4974 | -.03 |
Q3.05 | 138953 | 148292 | -9339 | -.06 |
Q4.05 | 145062 | 149528 | -4466 | -.03 |
Q1.06 | 146819 | 143607 | 3212 | .02 |
Q2.06 | 145233 | 138096 | 7137 | .05 |
Q3.06 | 143572 | 138953 | 4619 | .03 |
Q4.06 | 149305 | 145062 | 4243 | .03 |
Related Topics
INITCAP
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 non-alphanumeric characters. The data type of the return value is the same as the original text.
Return Value
CHAR | NCHAR | VARCHAR2 | NVARCHAR2
Syntax
INITCAP(char)
Arguments
char
can be any text expression.
Example
INITCAP('top ten tunes')
and INITCAP('TOP TEN TUNES')
both return the string Top Ten Tunes
.
RANK
orders the members of a dimension based on the values of an expression. The function returns the sequence numbers of the dimension members.
RANK
assigns the same rank to identical values. For example, RANK
may return 1, 2, 3, 3, 5 for a series of five dimension members.
Return Value
NUMBER
Syntax
RANK ( ) OVER (rank_clause)
rank_clause::=
{ DIMENSION dimension_id | HIERARCHY hierarchy_id } ORDER BY order_by_clause [, order_by_clause]... [ WITHIN { PARENT | LEVEL | ANCESTOR AT { DIMENSION LEVEL dim_lvl_id | HIERARCHY LEVEL hier_level_id } } ]
order_by_clause::=
expression [ASC | DESC] [NULLS {FIRST | LAST}]
Arguments
dimension_id
The dimension over which the values are calculated using the default hierarchy.
hierarchy_id
The hierarchy over which the values are calculated. If dimension_id
is used instead, the default hierarchy is used.
ORDER BY
Provides the basis for the ranking. You can provide additional ORDER BY
clauses to break any ties in the order.
expression
Provides the values to use as the basis for the rankings.
ASC | DESC
Sorts the ranking from smallest to largest (ascending) or from largest to smallest (descending).
NULLS {FIRST | LAST}
Determines whether members with null values are listed first or last.
WITHIN
Selects a set of related dimension members to be ranked.
PARENT
ranks members at the same level with the same parent.
LEVEL
ranks all members at the same level.
ANCESTOR
ranks all members at the same level and with the same ancestor at a specified level.
dim_level_id
The name of a level of dimension_id
.
hier_level_id
The name of a level of hierarchy_id
.
Example
This example ranks time periods within a calendar year by Unit Cost. Notice that no month is ranked 7, because two months (JAN-02 and JUL-02) have the same value and the same rank (6).
RANK() OVER (DIMENSION TIME.CALENDAR ORDER BY PRICE_CUBE.UNIT_COST DESC NULLS LAST WITHIN ANCESTOR AT DIMENSION LEVEL TIME.CALENDAR_YEAR)
Product | Time | Cost | Rank |
---|---|---|---|
Deluxe Mouse | MAR-02 | 24.05 | 1 |
Deluxe Mouse | APR-02 | 23.95 | 2 |
Deluxe Mouse | FEB-02 | 23.94 | 3 |
Deluxe Mouse | AUG-02 | 23.88 | 4 |
Deluxe Mouse | MAY-02 | 23.84 | 5 |
Deluxe Mouse | JAN-02 | 23.73 | 6 |
Deluxe Mouse | JUL-02 | 23.73 | 6 |
Deluxe Mouse | JUN-02 | 23.72 | 8 |
Deluxe Mouse | SEP-02 | 23.71 | 9 |
Deluxe Mouse | NOV-02 | 23.65 | 10 |
Deluxe Mouse | DEC-02 | 23.62 | 11 |
Deluxe Mouse | OCT-02 | 23.37 | 12 |
Related Topics
DECODE
compares an expression to one or more search strings one by one.
If expr
is search
, then DECODE
returns the corresponding result
. If there is no match, then DECODE
returns default
. If you omit default
, then DECODE
returns null.
Return Value
Data type of the first result
argument
Syntax
DECODE(expr, search, result
[, search, result ]...
[, default ]
)
Arguments
expr
is an expression that is compared to one or more search strings.
search
is a string that is searched for a match to expr
.
result
is the return value when expr
matches the corresponding search string.
default
is the return value when expr
does not match any of the search strings. If default
is omitted, then DECODE
returns null.
The arguments can be any numeric or character type. Two nulls are equivalent. If expr
is null, then DECODE
returns the result
of the first search
that is also null.
The maximum number of components, including expr
, searches
, results
, and default
, is 255.
Example
DECODE(sysdate, '21-JUN-06', 'Summer Solstice', '21-DEC-06', 'Winter Solstice', 'Have a nice day!')
returns these values:
Summer Solstice
on June 21, 2006
Winter Solstice
on December 21, 2006
Have a nice day!
on all other days
TRANSLATE
enables you to make several single-character, one-to-one substitutions in one operation. This expression returns an expression with all occurrences of each character in one string replaced by its corresponding character in a second string.
Return Value
CHAR | NCHAR | VARCHAR2 | NVARCHAR2
Syntax
TRANSLATE(expr, from_string, to_string)
Arguments
expr
is a text expression to be modified.
from_string
consists of one or more characters to be replaced in expr
.
to_string
consists of one or more characters that replace the corresponding characters in from_string
. This string can be shorter than from_string
, so that a null replaces the extra characters. However, to_string
cannot be empty.
Example
TRANSLATE('disk', 'dk', 'Dc')
returns the value Disc
.
This chapter describes the basic building blocks of the OLAP expression syntax. It contains these topics:
The naming conventions for dimensional objects follow standard Oracle naming rules. All names are case-insensitive.
owner.{ cube | dimension | table }.{ measure | column | attribute }
Table 1-1 Naming Conventions for Dimensional Objects
Convention | Quoted ID | Unquoted ID |
---|---|---|
Initial Character |
Any character. |
Alphabetic character from the database character set. |
Other Characters |
All characters, punctuation marks, and spaces are permitted. Double quotation marks and nulls (\0) are not permitted. |
Alphanumeric characters from the database character set and underscore (_) are permitted. The dollar sign ($) and pound sign (#) are permitted but not recommended. Double quotation marks and nulls (\0) are not permitted. |
Reserved Words |
Permitted but not recommended. |
Not permitted. |
GLOBAL.UNITS_CUBE.SALES
identifies the SALES
measure in the Units Cube.
TIME.DIM_KEY
and TIME.LEVEL_NAME
identify columns in the Time views.
TIME.CALENDAR
identifies the CALENDAR
hierarchy in the Time dimension.
TIME.CALENDAR.CALENDAR_YEAR
identifies the CALENDAR_YEAR
level of the CALENDAR
hierarchy in the Time dimension.
GLOBAL.UNITS_FACT.MONTH_ID
identifies a foreign key column in the UNITS_FACT
table.
TIME_DIM.CALENDAR_YEAR_DSC
identifies a column in the TIME_DIM
table.
Table 1-2 describes the data types that can be used for cubes and measures.
Table 1-2 Dimensional Data Types
Data Type | Description |
---|---|
|
A 64-bit floating number. A |
|
A 32-bit floating number. A |
|
A fixed length character string with a length of size characters or bytes. The size can range from 1 to 2000. |
|
A valid date in the range from January 1, 4712 BC to December 31, 9999 CE. It contains the datetime fields |
|
A decimal number with precision p and scale s represented as a |
|
A subtype of |
|
A whole number represented as a |
|
A period of time in days, hours, minutes, and seconds. The day precision is the maximum number of digits in the |
|
A period of time in years and months. The precision is the number of digits in the |
|
A fixed length character string with a length of size characters. The size can range from 1 character to 2000 bytes. The maximum number of characters depends on the national character set, which can require up to four bytes per character. |
|
A decimal number with precision p and scale s. The precision can range from 1 to 38. The scale can range from -84 to 127. A |
|
A variable length Unicode character string with a maximum length of size characters. The size can range from 1 character to 4000 bytes. The maximum number of characters depends on the national character set, which can require up to four bytes per character. |
|
A valid date that contains the datetime fields |
|
A valid date with the same description as
|
|
A valid date that contains the datetime fields |
|
A variable length character string with a maximum length of size characters or bytes. The size can range from 1 to 4000. |
An operator manipulates data items and returns a result. Operators manipulate individual data items called operands or arguments. They are represented by special characters or by keywords. Syntactically, an operator appears before an operand, after an operand, or between two operands.
The OLAP Expression Syntax has these types of operators:
For conditional operators, go to "Conditions".
A binary operator operates on two operands.
Here are two simple examples using numeric literals for the operands.
7 * 2
is 14
.
(8/2) + 1
is 5
.
This example multiplies the values of the Sales measure by a numeric literal to create a calculated measure named Sales Budget.
UNITS_CUBE.SALES * 1.06
Table 1-5 Multiplication Operator Example
Product | Level | Sales | Sales Budget |
---|---|---|---|
Hardware |
CLASS |
124191336 |
131642816 |
Desktop PCs |
FAMILY |
74556528 |
79029919 |
Monitors |
FAMILY |
3972142 |
4210470 |
Memory |
FAMILY |
5619219 |
5956372 |
Modems/Fax |
FAMILY |
5575726 |
5910269 |
CD/DVD |
FAMILY |
16129497 |
17097267 |
Portable PCs |
FAMILY |
18338225 |
19438518 |
The next example creates a calculated measure named Profit by subtracting Cost from Sales.
UNITS_CUBE.SALES - UNITS_CUBE.COST
Product | Level | Sales | Cost | Profit |
---|---|---|---|---|
Hardware | CLASS | 124191336 | 116058248 | 8133088 |
Desktop PCs | FAMILY | 74556528 | 71937312 | 2619215 |
Monitors | FAMILY | 3972142 | 3546195 | 425947 |
Memory | FAMILY | 5619219 | 4962527 | 656692 |
Modems/Fax | FAMILY | 5575726 | 5162879 | 412847 |
CD/DVD | FAMILY | 16129497 | 12510832 | 3618664 |
Portable PCs | FAMILY | 18338225 | 17938502 | 399723 |
The concatenation operator (||
) combines text expressions.
'The date today is: ' || sysdate
generates a text string such as The date today is: 23-AUG-06
.
The next example concatenates the level name and dimension keys of the Product dimension to create an identifier.
PRODUCT.LEVEL_NAME || ' ' || PRODUCT.DIM_KEY
Level | Dim Key | Identifier |
---|---|---|
CLASS | HRD | CLASS HRD |
FAMILY | DTPC | FAMILY DTPC |
FAMILY | MON | FAMILY MON |
FAMILY | MEM | FAMILY MEM |
FAMILY | MOD | FAMILY MOD |
FAMILY | DISK | FAMILY DISK |
FAMILY | LTPC | FAMILY LTPC |