operators 
summaries
member summaries
constants
constructs
business date/time functions
block functions
macro functions
common functions
DB2
Informix
MS Access
Oracle
Red Brick
SQL Server
Teradata
SAP BW
Sybase
report functions
To build an expression, choose from the following:
operators
summaries
member summaries
constants
constructs
business date/time functions
block functions
macro functions
common functions
DB2
Informix
MS Access
Oracle
Red Brick
SQL Server
Teradata
SAP BW
Sybase
report functions
Operators specify what happens to the values on either side of the operator. Operators are similar to functions, in that they manipulate data items and return a result.
Inserts an open parenthesis in your expression.
(exp)
Inserts a close parenthesis in your expression.
(exp)
Multiplies two numeric values.
value1 * value2
Separates expression components.
exp(param1, param2)
Divides two numeric values.
value1 / value2
Concatenates strings.
string1 || string2
Adds two values.
value1 + value2
Subtracts two numeric values or negates a numeric value.
value1 - value2 or - value
Compares values against a defined value and retrieves the values that are less than the defined value.
value1 < value2
Compares values against a defined value and retrieves the values that are less than or equal to the defined value.
value1 <= value2
Compares values against a defined value and retrieves the values that are not equal to the defined value.
value1 <> value2
Compares values against a defined value and retrieves the values that are equal to the defined value.
value1 = value2
Compares values against a defined value and retrieves the values that are greater than the defined value.
value1 > value2
Separates the components in a literal member expression.
[namespace].[dimension].[hierarchy].[level]->[L1]
Compares values against a defined value and retrieves the values that are greater than or equal to the defined value.
value1 >= value2
Returns TRUE if the conditions on both sides of the expression are true.
arg1 AND arg2
Works with summary expressions to define the scope to be adjusted based on the grouping columns in the query. The scope is context dependent.
aggregate_function ( expression AUTO )
Determines if a value falls in a given range.
name BETWEEN value1 and value2
Works with When, Then, Else, and End.
CASE expression { WHEN expression THEN expression } [
ELSE expression ] END Determines if a string contains another string.
string1 CONTAINS string2
Keyword that can be used as the first argument of member summary functions.
aggregate_function( currentMeasure within set expression )
Works with the LOOKUP construct.
lookup(....) in (....) DEFAULT(....)
A keyword used in an aggregate expression, to include only distinct occurrences of values. See also the function unique.
DISTINCT dataItem
count ( DISTINCT [OrderDetailQuantity] )
Result: 1704
Works with the If or Case constructs.
IF (condition) THEN .... ELSE (expression) , or CASE .... ELSE expression END
Works with the Case or When constructs.
CASE .... END
Determines if a string ends with a given string.
string1 ENDS WITH string2
Works with summary expressions to define the scope of the aggregation in the query.
aggregate_function ( expression FOR expression { , expression
} ) Works with summary expressions to define the scope to be all the specified grouping columns in the query. See also FOR clause.
aggregate_function ( expression FOR ALL expression {
, expression } ) Works with summary expressions to define the scope to be adjusted based on a subset of the grouping columns in the query. Equivalent to FOR clause.
aggregate_function ( expression FOR ANY expression {
, expression } ) Works with summary expressions to set the scope to be the whole query. See also FOR clause.
aggregate_function ( expression FOR REPORT )
Works with the Then and Else constructs.
IF (condition is true) THEN (action) ELSE (alternate action)
Determines if a value exists in a given list of values.
exp1 IN (exp_list)
Determines if an item exists in a given list of constant values or ranges.
expression IN_RANGE { constant : constant [ , constant
: constant ] } [code] IN_RANGE { 5 } Result: This is equivalent to [code] = 5
[code] IN_RANGE { 5: } Result: This is equivalent to [code] >= 5
[code] IN_RANGE { :5 } Result: This is equivalent to [code] <= 5
[code] IN_RANGE { 5:10 } Result: This is equivalent to ( [code] >= 5 and [code] <= 10 )
[code] IN_RANGE { :5,10,20: } Result: This is equivalent to ( [code] <= 5 or [code] = 10 or [code] >= 20 )
Determines if a value is undefined in the data.
value IS MISSING
Determines if a value is undefined in the data.
value IS NULL
Determines if a value is defined in the data.
value IS NOT MISSING
Determines if a value is defined in the data.
value IS NOT NULL
Determines if a string matches the pattern of another string.
string1 LIKE string2
Finds and replaces data with a value you specify. It is preferable to use the CASE construct.
LOOKUP (name) in (value1 --> value2) default (expression)
lookup([Country]) in ('Canada'-->([List Price] * 0.60),
'Australia'-->([List Price] * 0.80)) default([List Price]) Returns TRUE if the condition is false, otherwise returns FALSE.
NOT arg
Returns TRUE if either of the two conditions on both sides of the expression are true.
arg1 OR arg2
Performs a summary calculation before applying the summary filter.
summary ([expression] PREFILTER)
Counts the number of rows output by the query. Use with Count().
count(ROWS)
Determines if a string starts with a given string.
string1 STARTS WITH string2
Works with the If or Case constructs.
IF (condition) THEN ...., or CASE expression WHEN expression THEN .... END
Works with the Case construct.
CASE [expression] WHEN .... END
This list contains predefined functions that return either a single summary value for a group of related values or a different summary value for each instance of a group of related values.
Returns a calculated value using the appropriate aggregation function, based on the aggregation type of the expression.
aggregate ( expr [ auto ] )
aggregate ( expr for [ all | any ] expr { , expr } )
aggregate ( expr for report ) Returns the average value of selected data items. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.
average ( [ distinct ] expr [ auto ] )
average ( [ distinct ] expr for [ all | any ] expr { , expr } )
average ( [ distinct ] expr for report ) average ( Sales )
Result: The average of all Sales values.
Returns the number of selected data items excluding NULL values. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.
count ( [ distinct ] expr [ auto ] )
count ( [ distinct ] expr for [ all | any ] expr { , expr } )
count ( [ distinct ] expr for report ) count ( Sales )
Result: The total number of entries under Sales.
Returns the maximum value of selected data items. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.
maximum ( [ distinct ] expr [ auto ] )
maximum ( [ distinct ] expr for [ all | any ] expr { , expr } )
maximum ( [ distinct ] expr for report ) maximum ( Sales )
Result: The maximum value of all Sales values.
Returns the median value of selected data items.
median ( expr [ auto ] )
median ( expr for [ all | any ] expr { , expr } )
median ( expr for report ) Returns the minimum value of selected data items. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.
minimum ( [ distinct ] expr [ auto ] )
minimum ( [ distinct ] expr for [ all | any ] expr { , expr } )
minimum ( [ distinct ] expr for report ) minimum ( Sales )
Result: The minimum value of all Sales values.
Returns a moving average by row for a specified set of values of over a specified number of rows. The "<for-option>" defines the scope of the function. The "at" option defines the level of aggregation and can only be used in the context of relational datasources. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.
moving-average ( numeric_expr , numeric_expr [ at exp
{, expr } ] [ <for-option> ] [ prefilter ] )
moving-average ( [ distinct ] numeric_expr , numeric_expr [ <for-option>
] [ prefilter ] )
<for-option> ::= for expr {, expr } | for report | auto moving-average ( Qty, 3 )
Result: For each row, this displays the quantity and a moving average of the current row and the preceding two rows.
Qty ------ 200 700 400 200 200 500 | Moving-Average (Qty, 3) ------------------------------------ 200 450 433.3333 433.3333 266.6667 300.0000 |
Returns a moving total by row for a specified set of values over a specified number of rows. The "<for-option>" defines the scope of the function. The "at" option defines the level of aggregation and can only be used in the context of relational datasources. The keyword "distinct" is available for backward compatibility of expressions used in previous versions of the product.
moving-total ( numeric_expr , numeric_expr [ at exp
{, expr } ] [ <for-option> ] [ prefilter ] )
moving-total ( [ distinct ] numeric_expr , numeric_expr [ <for-option>
] [ prefilter ] )
<for-option> ::= for expr {, expr } | for report | auto moving-total ( Quantity, 3 )
Result: For each row, this displays the quantity and a moving total of the current row and the preceding two rows.
Qty ------ 200 700 400 200 200 500 | Moving-Total (Qty, 3) -------------------------------- 200 900 1300 1300 800 900 |
Returns the percent of the total value for selected data items. The "<for-option>" defines the scope of the function. The "at" option defines the level of aggregation and can only be used in the context of relational datasources. The keyword "distinct" is available for backward compatibility of expressions used in previous versions of the product.
percentage ( numeric_expr [ at exp {, expr } ] [ <for-option>
] [ prefilter ] )
percentage ( [ distinct ] numeric_expr [ <for-option> ] [ prefilter
] )
<for-option> ::= for expr {, expr } | for report | auto percentage ( sales 98 )
Result: Shows the percentage of the total sales for 1998 that is attributed to each sales representative.
Sales Rep ----------------- Bill Gibbons Bjorn Flertjan Chris Cornel | Sales 98 ------------ 60646 62523 22396 | Percentage -------------- 7.11% 7.35% 2.63% |
Returns a value, on a scale of one hundred, that indicates the percent of a distribution that is equal to or below the selected data items. The "<for-option>" defines the scope of the function. The "at" option defines the level of aggregation and can only be used in the context of relational datasources. The keyword "distinct" is available for backward compatibility of expressions used in previous versions of the product.
percentile ( numeric_expr [ at exp {, expr } ] [ <for-option>
] [ prefilter ] )
percentile ( [ distinct ] numeric_expr [ <for-option> ] [ prefilter
] )
<for-option> ::= for expr {, expr } | for report | auto percentile ( Sales 98 )
Result: For each row, the percentage of rows that are equal to or less than the quantity value of that row are displayed.
Qty ------ 800 700 600 500 400 400 200 200 | Percentile (Qty) ----------------- 1 0.875 0.75 0.625 0.5 0.5 0.25 0.25 |
Returns the rank of a value for a range that you specify. It returns integers to represent any range of ranks, such as 1 (highest) to 100 (lowest). The "<for-option>" defines the scope of the function. The "at" option defines the level of aggregation and can only be used in the context of relational datasources. The keyword "distinct" is available for backward compatibility of expressions used in previous versions of the product.
quantile ( numeric_expr, numeric_expr [ at exp {, expr
} ] [ <for-option> ] [ prefilter ] )
quantile ( [ distinct ] numeric_expr, numeric_expr [ <for-option>
] [ prefilter ] )
<for-option> ::= for expr {, expr } | for report | auto quantile ( Qty, 4 )
Result: The quantity, the rank of the quantity value, and the quantity values broken down into 4 quantile groups (quartiles) are displayed.
Qty ------ 800 700 600 500 400 400 200 200 | Rank (Qty) ----------------- 1 2 3 4 5 5 7 7 | Quantile (Qty, 4) ----------------------- 1 1 2 2 3 3 4 4 |
Returns the rank of a value, represented as integers from 1 (highest) to 4 (lowest), relative to a group of values. The "<for-option>" defines the scope of the function. The "at" option defines the level of aggregation and can only be used in the context of relational datasources. The keyword "distinct" is available for backward compatibility of expressions used in previous versions of the product.
quartile ( numeric_expr [ at exp {, expr } ] [ <for-option>
] [ prefilter ] )
quartile ( [ distinct ] numeric_expr [ <for-option> ] [ prefilter
] )
<for-option> ::= for expr {, expr } | for report | auto quartile ( Qty )
Result: The quantity and the quartile of the quantity value represented as integers from 1 (highest) to 4 (lowest) are displayed.
Qty ------ 450 400 350 300 250 200 150 100 | Quartile (Qty) --------------------- 1 1 2 2 3 3 4 4 |
Returns the rank value of selected data items. The sort order is optional; descending order (DESC) is assumed by default. If two or more rows tie, then there is a gap in the sequence of ranked values (also known as Olympic ranking). The "<for-option>" defines the scope of the function. The "at" option defines the level of aggregation and can only be used in the context of relational datasources. The keyword "distinct' is available for backward compatibility of expressions used in previous versions of the product. Null values are ranked last.
rank ( expr [ ASC | DESC ] {, expr [ ASC | DESC ] }
[ at exp {, expr } ] [ <for-option> ] [ prefilter ] )
rank ( [ distinct ] expr [ ASC | DESC ] {, expr [ ASC | DESC ] }
[ <for-option>] [ prefilter ] )
<for-option> ::= for expr {, expr } | for report | auto rank ( Sales 98 )
Result: For each row, the rank value of sales for 1998 that is attributed to each sales representative is displayed. Some numbers are skipped when a tie between rows occurs.
Sales Rep ---------------- Bill Gibbons Bjorn Flertjan Chris Cornel John Smith | Sales 98 ----------- 60000 50000 50000 48000 | Rank ------------ 1 2 2 4 |
Returns the running average by row (including the current row) for a set of values. The "<for-option>" defines the scope of the function. The "at" option defines the level of aggregation and can only be used in the context of relational datasources. The keyword "distinct" is available for backward compatibility of expressions used in previous versions of the product.
running-average ( numeric_expr [ at exp {, expr } ]
[ <for-option> ] [ prefilter ] )
running-average ( [ distinct ] numeric_expr [ <for-option> ]
[ prefilter ] )
<for-option> ::= for expr {, expr } | for report | auto running-average ( Qty )
Result: For each row, the quantity and a running average of the current and the previous rows are displayed.
Name ------- Smith Smith Smith Smith Wong Wong | Qty ------ 7 3 6 4 3 5 | Avg ------ 5 5 5 5 4 4 | Running-Average for name --------------------------- 7 5 5.33 5 3 4 |
Returns the running count by row (including the current row) for a set of values. The "<for-option>" defines the scope of the function. The "at" option defines the level of aggregation and can only be used in the context of relational datasources. The keyword "distinct" is available for backward compatibility of expressions used in previous versions of the product.
running-count ( numeric_expr [ at exp {, expr } ] [
<for-option> ] [ prefilter ] )
running-count ( [ distinct ] numeric_expr [ <for-option> ] [
prefilter ] )
<for-option> ::= for expr {, expr } | for report | auto running-count ( Qty )
Result: For each row, the quantity and a running count of the position of the current row are displayed.
Name ------- Smith Smith Smith Smith Wong Wong | Qty ------ 7 3 6 4 3 5 | Count --------- 4 4 4 4 3 3 | Running-Count for name -------------------------- 1 2 3 4 1 2 |
Returns a running difference by row, calculated as the difference between the value for the current row and the preceding row, (including the current row) for a set of values. The "<for-option>" defines the scope of the function. The "at" option defines the level of aggregation and can only be used in the context of relational datasources. The keyword "distinct" is available for backward compatibility of expressions used in previous versions of the product.
running-difference ( numeric_expr [ at exp {, expr }
] [ <for-option> ] [ prefilter ] )
running-difference ( [ distinct ] numeric_expr [ <for-option>
] [ prefilter ] )
<for-option> ::= for expr {, expr } | for report | auto running-difference ( Qty )
Result: For each row, the quantity and a running difference between the value for the current row and the preceding row are displayed.
Name ------- Smith Smith Smith Smith Wong Wong | Qty ------ 7 3 6 4 3 5 | Running-Difference for name -------------------------------------- NULL -4 3 -2 -1 2 |
Returns the running maximum by row (including the current row) for a set of values. The "<for-option>" defines the scope of the function. The "at" option defines the level of aggregation and can only be used in the context of relational datasources. The keyword "distinct" is available for backward compatibility of expressions used in previous versions of the product.
running-maximum ( numeric_expr [ at exp {, expr } ]
[ <for-option> ] [ prefilter ] )
running-maximum ( [ distinct ] numeric_expr [ <for-option> ]
[ prefilter ] )
<for-option> ::= for expr {, expr } | for report | auto running-maximum ( Qty )
Result: For each row, the quantity and a running maximum of the current and previous rows are displayed.
Name ------- Smith Smith Smith Smith Wong Wong | Qty ------ 2 3 6 7 3 5 | Max ------ 7 7 7 7 5 5 | Running-Maximum (Qty) for name --------------------------------- 2 3 6 7 3 5 |
Returns the running minimum by row (including the current row) for a set of values. The "<for-option>" defines the scope of the function. The "at" option defines the level of aggregation and can only be used in the context of relational datasources. The keyword "distinct" is available for backward compatibility of expressions used in previous versions of the product.
running-minimum ( numeric_expr [ at exp {, expr } ]
[ <for-option> ] [ prefilter ] )
running-minimum ( [ distinct ] numeric_expr [ <for-option> ]
[ prefilter ] )
<for-option> ::= for expr {, expr } | for report | auto running-minimum ( Qty )
Result: For each row, the quantity and a running minimum of the current and previous rows are displayed.
Name ------- Smith Smith Smith Smith Wong Wong | Qty ----- 7 3 6 2 4 5 | Min ------ 2 2 2 2 3 3 | Running-Minimum (Qty) for name ---------------------------------- 7 3 3 2 4 4 |
Returns a running total by row (including the current row) for a set of values. The "<for-option>" defines the scope of the function. The "at" option defines the level of aggregation and can only be used in the context of relational datasources. The keyword "distinct" is available for backward compatibility of expressions used in previous versions of the product.
running-total ( numeric_expr [ at exp {, expr } ] [
<for-option> ] [ prefilter ] )
running-total ( [ distinct ] numeric_expr [ <for-option> ] [
prefilter ] )
<for-option> ::= for expr {, expr } | for report | auto running-total ( Qty )
Result: For each row, the quantity and a running total of the current and previous rows are displayed.
Name ------- Smith Smith Smith Smith Wong Wong | Qty ------ 2 3 6 7 3 5 | Total -------- 18 18 18 18 12 12 | Running-Total (Qty) for name --------------------------------- 2 5 11 18 3 8 |
Returns the standard deviation of selected data items. The keyword "distinct" is available for backward compatibility of expressions used in previous versions of the product.
standard-deviation ( [ distinct ] expr [ auto ] )
standard-deviation ( [ distinct ] expr for [ all | any ] expr {
, expr } )
standard-deviation ( [ distinct ] expr for report ) standard-deviation ( ProductCost )
Result: A value indicating the deviation between product costs and the average product cost.
Computes the population standard deviation and returns the square root of the population variance. The keyword "distinct" is available for backward compatibility of expressions used in previous versions of the product.
standard-deviation-pop ( [ distinct ] expr [ auto ] )
standard-deviation-pop ( [ distinct ] expr for [ all | any ] expr
{ , expr } )
standard-deviation-pop ( [ distinct ] expr for report ) standard-deviation-pop ( ProductCost )
Result: A value of the square root of the population variance.
Returns the total value of selected data items. The keyword "distinct" is available for backward compatibility of expressions used in previous versions of the product.
total ( [ distinct ] expr [ auto ] )
total ( [ distinct ] expr for [ all | any ] expr { , expr } )
total ( [ distinct ] expr for report ) total ( Sales )
Result: The total value of all Sales values.
Returns the variance of selected data items. The keyword "distinct" is available for backward compatibility of expressions used in previous versions of the product.
variance ( [ distinct ] expr [ auto ] )
variance ( [ distinct ] expr for [ all | any ] expr { , expr } )
variance ( [ distinct ] expr for report ) variance ( Product Cost )
Result: A value indicating how widely product costs vary from the average product cost.
Returns the population variance of a set of numbers after discarding the nulls in this set. The keyword "distinct" is available for backward compatibility of expressions used in previous versions of the product
variance-pop ( [ distinct ] expr [ auto ] )
variance-pop ( [ distinct ] expr for [ all | any ] expr { , expr
} )
variance-pop ( [ distinct ] expr for report ) variance-pop ( Qty)
Result: For each row, this displays the population variance of a set of numbers after discarding the nulls in this set.
This list contains predefined functions that return either a single summary value for a set of members or a different summary value for each member of a set of members.
Returns a calculated value using the appropriate aggregation function, based on the aggregation type of the expression.
aggregate ( < currentMeasure | numeric_expr > within set set_expr ) aggregate ( < currentMeasure | numeric_expr > within < detail | aggregate > expr )
Returns the average value of the selected data items.
average ( < currentMeasure | numeric_expr > within set set_expr ) average ( < currentMeasure | numeric_expr > within < detail | aggregate > expr )
Returns the number of selected data items excluding NULL values.
count ( < currentMeasure | numeric_expr > within set set_expr ) count ( < currentMeasure | numeric_expr > within < detail | aggregate > expr )
Returns the maximum value of selected data items.
maximum ( < currentMeasure | numeric_expr > within set set_expr ) maximum ( < currentMeasure | numeric_expr > within < detail | aggregate > expr )
Returns the median value of selected data items.
median ( < currentMeasure | numeric_expr > within set set_expr ) median ( < currentMeasure | numeric_expr > within < detail | aggregate > expr )
Returns the minimum value of selected data items.
minimum ( < currentMeasure | numeric_expr > within set set_expr ) minimum ( < currentMeasure | numeric_expr > within < detail | aggregate > expr )
Returns the percent of the total value for the selected data items.
percentage ( numeric_expr [ tuple member_expr {, member_expr
} ] within set set_expr ) percentage ( [gosales].[sales measures].[quantity] tuple [gosales].[Staff].[].[department]->[West] within set children([gosales].[Staff].[].[Staff] )
Returns a value, on a scale from 0 to 100, that indicates the percent of a distribution that is equal to or below the selected data items.
percentile ( numeric_expr [ tuple member_expr {, member_expr
} ] within set set_expr ) Returns the rank of a value for the specified range. It returns integers to represent any range of ranks, such as 1 (highest) to 100 (lowest).
quantile ( numeric_expr, numeric_expr [ tuple member_expr
{, member_expr } ] within set set_expr ) Returns the rank of a value, represented as integers from 1 (highest) to 4 (lowest), relative to a group of values.
quartile ( numeric_expr [ tuple member_expr {, member_expr
} ] within set set_expr ) Returns the rank value of the selected data items. The type of ranking returned (Olympic, dense, or serial) is data source dependent. The sort order is optional; DESC is assumed by default.
rank ( numeric_expr [ ASC | DESC ] [ tuple member_expr
{, member_expr } ] within set set_expr ) rank ( [gosales].[sales measures].[quantity] tuple [gosales].[Staff].[].[department]->[West] within set children([gosales].[Staff].[].[Staff] )
Returns the standard deviation of the selected data items.
standard-deviation ( < currentMeasure | numeric_expr > within set set_expr ) standard-deviation ( < currentMeasure | numeric_expr > within < detail | aggregate > expr )
Returns the standard deviation population of the selected data items.
standard-deviation-pop ( < currentMeasure | numeric_expr > within set set_expr ) standard-deviation-pop ( < currentMeasure | numeric_expr > within < detail | aggregate > expr )
Returns the total value of the selected data items.
total ( < currentMeasure | numeric_expr > within set set_expr ) total ( < currentMeasure | numeric_expr > within < detail | aggregate > expr )
Returns the variance of the selected data items.
variance ( < currentMeasure | numeric_expr > within set set_expr ) variance ( < currentMeasure | numeric_expr > within < detail | aggregate > expr )
Returns the variance population of the selected data items.
variance-pop ( < currentMeasure | numeric_expr > within set set_expr ) variance-pop ( < currentMeasure | numeric_expr > within < detail | aggregate > expr )
A constant is a fixed value that you can use in an expression.
Inserts the current system date.
Inserts the current system date and time.
Inserts a zero time with time zone.
Inserts an example of a timestamp with time zone.
Inserts a zero interval.
Inserts a zero year interval.
Inserts a zero month interval.
Inserts a zero year to month interval.
Inserts a zero day interval.
Inserts a zero hour interval.
Inserts a zero minute interval.
Inserts a zero second interval.
Inserts a zero day to hour interval.
Inserts a zero day to minute interval.
Inserts a zero day to second interval.
Inserts a zero hour to minute interval.
Inserts a zero hour to second interval.
Inserts a zero minute to second interval.
Inserts a NULL value if the expression conditions are not met.
Inserts the number 0, which can be replaced with a new numeric value.
Inserts an empty string.
Inserts the current system time.
This list contains constructs and templates that can be used to create an expression.
This is the template for using an "IF...THEN...ELSE" statement in the expression.
IF ([Country] = 'Canada')
THEN ([List Price] * 0.60)
ELSE ([List Price]) This is the template for using "IN_RANGE" in the expression.
[code] IN_RANGE { :30 , 40, 50, 999: } [code] IN_RANGE { 5 } Result: This is equivalent to [code] = 5
[code] IN_RANGE { 5: } Result: This is equivalent to [code] >= 5
[code] IN_RANGE { :5 } Result: This is equivalent to [code] <= 5
[code] IN_RANGE { 5:10 } Result: This is equivalent to ( [code] >= 5 and [code] <= 10 )
[code] IN_RANGE { :5,10,20: } Result: This is equivalent to ( [code] <= 5 or [code] = 10 or [code] >= 20 )
This is the template for using a "search case" in the expression.
CASE
WHEN [Country] = 'Canada' THEN ([List Price] * 0.60)
WHEN [CountryCode] > 100 THEN [List Price] * 0.80
ELSE [List Price]
END This is the template for using a "simple case" in the expression.
CASE [Country]
WHEN 'Canada' THEN ([List Price] * 0.60)
WHEN 'Australia' THEN [List Price] * 0.80
ELSE [List Price]
END This list contains business functions for performing date and time calculations.
Returns the date or datetime, depending on the first argument, that results from adding "integer_exp" days to "date_exp".
_add_days ( date_exp, integer_exp )
_add_days ( 2002-04-30 , 1 )
Result: 2002-05-01
_add_days ( 2002-04-30 12:10:10.000, 1 )
Result: 2002-05-01 12:10:10.000
_add_days ( 2002-04-30 00:00:00.000, 1/24 ) Note that the second argument is not a whole number. This is supported by some database technologies and increments the time portion.
Result: 2002-04-30 01:00:00.000
Returns the date or datetime, depending on the first argument, that results from the addition of "integer_exp" months to "date_exp".
_add_months ( date_exp, integer_exp )
_add_months ( 2002-04-30 , 1 )
Result: 2002-05-30
_add_months ( 2002-04-30 12:10:10.000, 1 )
Result: 2002-05-30 12:10:10.000
Returns the date or datetime, depending on the first argument, that results from the addition of "integer_exp" years to "date_exp".
_add_years ( date_exp, integer_exp )
_add_years ( 2002-04-30 , 1 )
Result: 2003-04-30
_add_years ( 2002-04-30 12:10:10.000 , 1 )
Result: 2003-04-30 12:10:10.000
Returns a number that is obtained from subtracting "date_exp" from today’s date. The returned value has the form YYYYMMDD, where YYYY represents the number of years, MM represents the number of months, and DD represents the number of days.
_age (date_exp )
Today's date=2003-02-05 _age ( 1990-04-30 )
Result: 120906 that is 12 years, 9 months and 6 days
Returns the day of week (1 to 7), where 1 is the first day of the week as indicated by the second parameter(1 to 7, 1 being Monday and 7 being Sunday). Note that in ISO 8601 standard, a week begins with Monday being day 1.
_day_of_week ( date_exp, integer )
_day_of_week ( 2003-01-01, 1 )
Result: 3
Returns the ordinal for the day of the year in "date_ exp" (1 to 366). Also known as Julian day.
_day_of_year ( date_exp )
_day_of_year ( 2003-03-01 )
Result: 61
Returns a positive or negative number representing the number of days between "date_exp1" and "date_exp2". If "date_exp1" < "date_exp2" then the result will be a negative number.
_days_between ( date_exp1, date_exp2 )
_days_between ( 2002-04-30 , 2002-06-21 )
Result: -52
Returns a number representing the number of days remaining in the month represented by "date_exp".
_days_to_end_of_month ( date_exp )
_days_to_end_of_month ( 2002-04-20 14:30:22.123 )
Result: 10
Returns a date or datetime, depending on the argument, that is obtained from converting "date_exp" to a date with the same year and month but with the day set to 1.
_first_of_month ( date_exp )
_first_of_month ( 2002-04-20 )
Result: 2002-04-01
_first_of_month ( 2002-04-20 12:10:10.000 )
Result: 2002-04-01 12:10:10.000
Returns a date or datetime, depending on the argument, that is the last day of the month represented by "date_exp".
_last_of_month ( date_exp )
_last_of_month ( 2002-01-14 )
Result: 2002-01-31
_last_of_month ( 2002-01-14 12:10:10.000 )
Result: 2002-01-31 12:10:10.000
Returns a timestamp constructed from "integer_exp1" (the year), "integer_exp2" (the month), and "integer_exp3" (the day). The time portion defaults to 00:00:00.000 .
_make_timestamp ( integer_exp1, integer_exp2, integer_exp3 )
_make_timestamp ( 2002 , 01 , 14 )
Result: 2002-01-14 00:00:00.000
Returns a positive or negative integer number representing the number of months between "date_exp1" and "date_exp2". If "date_exp1" < "date_exp2" then a negative number is returned.
_months_between ( date_exp1, date_exp2 )
_months_between ( 2002-01-30, 2002-04-03 )
Result: 2
Returns the number of the week of the year of "date_exp" according to the ISO 8601 standard. Week 1 of the year is the first week of the year to contain a Thursday, which is equivalent to the first week containing January 4th. A week starts on Monday (day 1) and ends on Sunday (day 7).
_week_of_year ( date_exp )
_week_of_year ( 2003-01-01 )
Result: 1
Returns a positive or negative integer number representing the number of years between "date_exp1" and "date_exp2". If "date_exp1" < "date_exp2" then a negative value is returned.
_years_between ( date_exp1, date_exp2 )
_years_between ( 2003-01-30, 2001-04-03 )
Result: 1
Returns a number representing the difference between "date_exp1" and "date_exp2". The returned value has the form YYYYMMDD, where YYYY represents the number of years, MM represents the number of months, and DD represents the number of days.
_ymdint_between ( date_exp1, date_exp2 )
_ymdint_between ( 1990-04-30 , 2003-02-05 )
Result: 120906 that is 12 years, 9 months and 6 days
This list contains functions used to access members of a set, usually in the context of Analysis Studio.
Returns the first members found in the set up to "numeric_exp_max" + "numeric_exp_overflow". If "numeric_exp_max" + "numeric_exp_overflow" is exceeded, then only the max number of members are returned.
_firstFromSet ( set_exp, numeric_exp_max , numeric_exp_overflow )
Returns the set containing "member_exp" when the size of "set_exp" is greater than "numeric_exp".
_remainderSet (member_exp, set_exp , numeric_exp )
This list contains functions that can be used within a macro. A macro may contain one or more macro functions. A macro is delimited by a number sign (#) at the beginning and at the end. Everything between the number signs is treated as a macro expression and is executed at run time. For macro functions that accept expressions of datatype timestamp with time zone as arguments, the accepted format is 'yyyy-mm-dd hh:mm:ss[.ff]+hh:mm' where fractional seconds are optional and can be represented by 1 to 9 digits. In lieu of a space separating the date portion to the time portion, the character 'T' is also accepted. Also, in lieu of the time zone '+hh:mm', the character 'Z' is accepted and will be processed internally as '+00:00'. The macro functions that return expressions of datatype timestamp with time zone return 9 digits by default for their fractional seconds. The macro function timestampMask() can be used to trim the output if required.
Concatenates two strings.
value1 + value2
# '{' + $runLocale + '}'# Result: {en-us}
Returns the timestamp with time zone (as a string) that results from adding "integer_exp" number of days to "string_exp", where "string_exp" represents a timestamp with time zone.
_add_days ( string_exp , integer_exp )
#_add_days ( '2005-11-01 12:00:00.000-05:00' , -1 )#
Result: 2005-10-31 12:00:00.000000000-05:00
#_add_days ( $current_timestamp , 1 )#
Result: 2005-11-02 12:00:00.000000000-05:00
#timestampMask ( _add_days ( $current_timestamp , 1 ) , 'yyyy-mm-dd' )#
Result: 2005-11-02
Returns the timestamp with time zone (as a string) that results from adding "integer_exp" number of months to "string_exp", where "string_exp" represents a timestamp with time zone.
_add_months ( string_exp , integer_exp )
#_add_months ( '2005-11-01 12:00:00.000-05:00' , -1 )#
Result: 2005-10-01 12:00:00.000000000-05:00
#_add_months ( $current_timestamp , 1 )#
Result: 2005-12-01 12:00:00.000000000-05:00
#timestampMask ( _add_months ( $current_timestamp , 1 ) , 'yyyy-mm-dd' )#
Result: 2005-12-01
Returns the timestamp with time zone (as a string) that results from adding "integer_exp" number of years to "string_exp", where "string_exp" represents a timestamp with time zone.
_add_years ( string_exp , integer_exp )
#_add_years ( '2005-11-01 12:00:00.000-05:00' , -1 )#
Result: 2004-11-01 12:00:00.000000000-05:00
#_add_years ( $current_timestamp , 1 )#
Result: 2006-11-01 12:00:00.000000000-05:00
#timestampMask ( _add_years ( $current_timestamp , 1 ) , 'yyyy-mm-dd' )#
Result: 2006-11-01
Constructs an array out of the list of parameters.
array ( string_exp | array_exp { , string_exp | array_exp
} ) #csv ( array ( 'a1' , array ( 'x1' , 'x2' ) , 'a2' ) )#
Result: 'a1' , 'x1' , 'x2' , 'a2'
Constructs a string from the elements of the array where the values are separated by commas. Optionally, the separator and quote strings can be specified. The default separator is a comma ( , ) and the default quote character is a single quote ( ' ).
csv ( array_exp [ , separator_string [ , quote_string ] ] )
#csv ( array ( 'a1' , 'a2' ) )#
Result: 'a1' , 'a2'
Surrounds the passed string with double quotes.
dq ( string_exp )
#dq ( 'zero' )#
Result: "zero"
Searches for and returns elements of an array that match the pattern specified in "pattern_string".
grep ( pattern_string , array_exp )
#csv ( grep ( 's' , array ( 'as', 'an', 'arts' ) ) )#
Result: 'as', 'arts'
Returns a timestamp with time zone (as a string) converting the day of "string_exp" to 1, where "string_exp" is a timestamp with time zone.
_first_of_month ( string_exp )
#_first_of_month ( '2005-11-11 12:00:00.000-05:00' )#
Result: 2005-11-01 12:00:00.000000000-05:00
#timestampMask ( _first_of_month ( '2005-11-11 12:00:00.000-05:00' ), 'yyyymmdd' )#
Result: 20051101
Returns a timestamp with time zone (as a string) that is the last day of the month represented by "string_exp", where "string_exp" is a timestamp with time zone.
_last_of_month ( string_exp )
#_last_of_month ( '2005-11-11 12:00:00.000-05:00' )#
Result: 2005-11-30 12:00:00.000000000-05:00
#timestampMask ( _last_of_month ( '2005-11-11 12:00:00.000-05:00' ) , 'yyyy-mm-dd' ) #
Result: 2005-11-30
Joins the elements of an array using the separator string.
join ( separator_string , array_exp )
# sq ( join ( ' | | ' , array ( 'as', 'an', 'arts' ) ) )#
Result: 'as | | an | | arts'
Prompts the user for a single value. Only "prompt_name" is required. The datatype defaults to string when not specified. The prompt is optional when "defaultText" is specified. "Text", when specified, will precede the value. "QueryItem" can be specified to take advantage of the prompt info properties of "queryItem". "Trailing_text", when specified, will be appended to the value.
prompt ( prompt_name , datatype , defaultText , text , queryItem , trailing_text )
select . . . where COUNTRY_MULTILINGUAL.COUNTRY_CODE
> #prompt('Starting CountryCode',
'integer',
'10'
)# Result: select . . . where COUNTRY_MULTILINGUAL.COUNTRY_CODE > 10
[gosales].[COUNTRY].[COUNTRY] = #prompt('countryPrompt','string','''Canada''')# Result: [gosales].[COUNTRY].[COUNTRY] = 'Canada'
The "defaultText" parameter must be specified such that it is literally valid in the context of the macro since no formatting takes place on this value. See details below.
The default string '''Canada''' in Example 2 is specified as a string using single quotes, in which the embedded single quotes are doubled up, thus 3 quotes. This results in the string being properly displayed within single quotes in the expression.
As a general rule for the string datatype, "defaultText' should always be specified as in the previous note, except in the context of a stored procedure parameter.
For "defaultText" of types 'date' or 'datetime', a special format should be used in the context of SQL. Examples of these formats are 'DATE ''2001-12-25''' and 'DATETIME ''2001-12-25 12:00:00'''. In all other contexts, use the date/datetime without the keyword and escaped single quotes (e.g. '2001-12-25').
Prompts the user for one or more values. Only "prompt_name" is required. The datatype defaults to string when not specified. The prompt is optional when "defaultText" is specified. "Text", when specified, will precede the list of values. "QueryItem" can be specified to take advantage of the prompt info properties of "queryItem". "Trailing_text", when specified, will be appended to the list of values.
promptmany ( prompt_name , datatype , defaultText , text , queryItem , trailing_text )
select . . . where COUNTRY_MULTILINGUAL.COUNTRY IN ( #promptmany ( 'CountryName' ) # )
Result: select . . . where COUNTRY_MULTILINGUAL.COUNTRY_CODE IN ('Canada' , 'The Netherlands' , 'Russia')
select . . .
from
gosales.gosales.dbo.COUNTRY_MULTILINGUAL COUNTRY_MULTILINGUAL,
gosales.gosales.dbo.COUNTRY XX
where COUNTRY_MULTILINGUAL.COUNTRY_CODE = XX.COUNTRY_CODE
#promptmany('Selected CountryCodes',
'integer',
' ',
' and COUNTRY_MULTILINGUAL.COUNTRY_CODE IN (',
'',
')'
)#
Result: select . . . from gosales.gosales.dbo.COUNTRY_MULTILINGUAL COUNTRY_MULTILINGUAL, gosales.gosales.dbo.COUNTRY XX where COUNTRY_MULTILINGUAL.COUNTRY_CODE = XX.COUNTRY_CODE and COUNTRY_MULTILINGUAL.COUNTRY_CODE IN ('Canada' , 'The Netherlands' , 'Russia')
Surrounds the passed string with square brackets.
sb ( string_exp )
#sb ( 'abc' )#
Result: [abc]
Surrounds the passed string with single quotes.
sq ( string_exp )
#sq ( 'zero' )#
Result: 'zero'
Sorts the elements of the array in alphabetical order. Duplicates are retained.
sort ( array_exp )
#csv ( sort ( array ( 's3', 'a', 'x' ) ) )#
Result: 'a', 's3', 'x'
Splits a string or string elements of the array into separate elements.
split ( pattern_string, string_exp | array_exp )
#csv ( split ( '::', 'ab=c::de=f::gh=i' ) )#
Result: 'ab=c' , 'de=f', 'gh=i'
#csv ( split ( '=' , split ( '::', 'ab=c::de=f::gh=i' ) ) )#
Result: 'ab' , 'c' , 'de' , 'f', 'gh' , 'i'
Searches for a pattern in a string or in the string elements of an array and substitutes the first occurrence of "pattern_string" with "replacement_string".
substitute ( pattern_string, replacement_string, string_exp | array_exp )
#sq ( substitute ( '^cn=', '***', 'cn=help' ) )#
Result: '***help'
#csv ( substitute ( '^cn=', '***', array ( 'cn=help' , 'acn=5' ) ) )#
Result: '***help' , 'acn=5'
#csv ( substitute ( 'cn=', '', array ( 'cn=help' , 'acn=5' ) ) )#
Result: 'help' , 'a5'
Returns "string_exp1", representing a timestamp with time zone, trimmed to the format specified in "string_exp2". The format in "string_exp2" must be one of the following: 'yyyy', 'mm', 'dd', 'yyyy-mm', 'yyyymm', 'yyyy-mm-dd', 'yyyymmdd', 'yyyy-mm-dd hh:mm:ss', 'yyyy-mm-dd hh:mm:ss+hh:mm', 'yyyy-mm-dd hh:mm:ss.ff3', 'yyyy-mm-dd hh:mm:ss.ff3+hh:mm', 'yyyy-mm-ddThh:mm:ss', 'yyyy-mm-ddThh:mm:ss+hh:mm', 'yyyy-mm-ddThh:mm:ss.ff3+hh:mm', or 'yyyy-mm-ddThh:mm:ss.ff3+hh:mm'. The macro functions that return a string representation of a timestamp with time zone show a precision of 9 digits for the fractional part of the seconds by default. The format options allow this to be trimmed down to a precision of 3 or 0.
timestampMask ( string_exp1, string_exp2 )
#timestampMask ( $current_timestamp, 'yyyy-dd-mm' )#
Result: 2005-11-01
#timestampMask ( '2005-11-01 12:00:00.000-05:00', 'yyyy-mm-dd hh:mm:ss+hh:mm' )#
Result: 2005-11-01 12:00:00-05:00
#timestampMask ( '2005-11-01 12:00:00.123456789-05:00', 'yyyy-mm-ddThh:mm:ss+hh:mm.ff3+hh:mm' )#
Result: 2005-11-01T12:00:00.123-05:00
Returns the string representing a timestamp with time zone resulting from adjusting "string_exp" to the time zone of the operating system. Note that the macro function timestampMask() can be used to trim the output.
toLocal ( string_exp )
#toLocal ( '2005-11-01 17:00:00.000-00:00' )# where OS local time zone is -05:00
Result: 2005-11-01 12:00:00.000000000-05:00
#timestampMask( toLocal ( '2005-11-01 17:00:00.000-00:00' ) , 'yyyy-mm-dd hh:mm:ss+hh:mm' )# where OS local time zone is -05:00
Result: 2005-11-01 12:00:00-05:00
#toLocal ( '2005-11-01 13:30:00.000-03:30' )# where OS local time zone is -05:00
Result: 2005-11-01 12:00:00.000000000-05:00
Returns the string representing a timestamp with time zone resulting from adjusting "string_exp" to the zero-point reference UTC time zone, also known as GMT time. Note that the macro function timestampMask() can be used to trim the output.
toUTC ( string_exp )
#toUTC ( '2005-11-01 12:00:00.000-05:00' )#
Result: 2005-11-01 17:00:00.000000000-00:00
#timestampMask( toUTC ( '2005-11-01 12:00:00.000-05:00' ) , 'yyyy-mm-dd hh:mm:ss.ff3+hh:mm' )#
Result: 2005-11-01 17:00:00.000-00:00
#toUTC ( $current_timestamp )#
Result: 2005-11-01 17:00:00.000000000-00:00
Removes duplicate entries from the array. The order of the elements is retained.
unique ( array_exp )
Example: #csv ( unique ( array ( 's3', 'a', 's3', 'x' ) ) )#
Result: 's3', 'a', 'x'
URL encodes the passed argument. Useful when specifying XML connection strings.
urlencode(prompt('userValue')) urlencode(prompt('some_val')) Result: %27testValue%27
Uses the identity information of the current authenticated user to look up values in the specified parameter map. Each individual piece of the user's identity (account name, group names, role names) is used as a key into the map. The unique list of values that is retrieved from the map is then returned as a string, where each value is surrounded by single quotes and where multiple values are separated by commas.
CSVIdentityName ( %parameter_map_name [ , separator_string ] )
#CSVIdentityName ( %security_clearance_level_map )#
Result: 'level_500' , 'level_501' , 'level_700'
Returns the pieces of the user's identity (account name, group names, role names) as a list of strings. The unique list of values is returned as a string, where each value is surrounded by single quotes and where multiple values are separated by commas.
CSVIdentityNameList ( [ separator_string ] )
#CSVIdentityNameList ( )#
Result: 'Everyone' , 'Report Administrators' , 'Query User'
Returns the passport.
CAMPassport ( )
#CAMPassport ( )#
Result: 111:98812d62-4fd4-037b-4354-26414cf7ebef:3677162321
Returns the pieces of the user's identity (account name, group names, role names) as a list of values separated by commas.
CAMIDList ( [ separator_string ] )
#CAMIDList ( )#
Result: CAMID("::Everyone"), CAMID(":Authors"), CAMID(":Query Users"), CAMID(":Consumers"), CAMID(":Metrics Authors")
Returns an array of the user's identities based on the identity type (account, group, or role). It can be used with the macro functions csv or join.
CAMIDListForType ( identity type )
[qs].[userRole] IN ( #csv ( CAMIDListForType ( 'role' ) ) # )
Result: [qs].[userRole] IN ( 'CAMID("::System Administrators")', 'CAMID(":Authors")' )
Returns NULL if "exp1" equals "exp2", otherwise returns "exp1".
nullif ( exp1, exp2 )
Associates a format with the expression. The keyword can be PERCENTAGE_0, PERCENTAGE_1 or PERCENTAGE_2.
_format ( expr , keyword )
_format( [Unit Sale Price] / [Unit Price] , PERCENTAGE_2 )
Result: 0.75123 displayed as 75.12%
Returns "numeric_exp" rounded to "integer_exp" places to the right of the decimal point. Note: "Integer_exp" must be a non-negative integer.
_round ( numeric_exp, integer_exp )
_round ( 1220.42369, 2 )
Result: 1220.42
Returns the absolute value of "numeric_exp". Negative values are returned as positive values.
abs ( numeric_exp )
abs ( 15 )
Result: 15
abs ( -15 )
Result: 15
Returns the ancestor of the specified member at either the specified (named) level or the specified number of levels above the member. Note: The result is not guaranteed to be consistent when there is more than one such ancestor.
ancestor ( member, level | integer )
Returns all the ancestors of a member at a specified level or distance above the member. (Most data sources support only one ancestor at a specified level, but some support more than one. Hence the result is a member set.)
ancestors ( member , level | index )
Sorts a set according to the value of "numeric_exp" evaluated at each of the members of "set_exp" and returns the bottom "index_exp" members.
bottomCount ( set_exp , index_exp , numeric_exp )
Sorts "numeric_exp2", evaluated at the corresponding member of "set_exp", and picks up the bottommost elements whose cumulative total is equal to or less than "numeric_exp1" percent of the total.
bottomPercent ( set_exp , numeric_exp1 , numeric_exp2 )
Sorts "numeric_exp2", evaluated at the corresponding member of "set_exp", and picks up the bottommost elements whose cumulative total is equal to or less than "numeric_exp1".
bottomSum ( set_exp , numeric_exp1 , numeric_exp2 )
Returns the caption values of the specified argument.
caption ( level | member | set_exp )
Converts "exp" to a specified data type. Some data types allow for a length and precision to be specified. Make sure that the target is of the appropriate type and size. The following datatypes can be specified: CHARACTER, VARCHAR, CHAR, NUMERIC, DECIMAL, INTEGER, SMALLINT, REAL, FLOAT, DATE, TIME, TIMESTAMP, TIME WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, and INTERVAL. When type casting to an INTERVAL type, one of the following interval qualifiers must be specified: YEAR, MONTH, or YEAR TO MONTH for the year-to-month interval datatype; DAY, HOUR, MINUTE, SECOND, DAY TO HOUR, DAY TO MINUTE, DAY TO SECOND, HOUR TO MINUTE, HOUR TO SECOND, or MINUTE TO SECOND for the day-to-second interval datatype. More notes for this function are included in the user guide.
cast ( exp, datatype_specification )
cast ( '123' , integer )
Result: 123
cast ( 12345 , VARCHAR ( 10 ) )
Result: a string containing 12345
When you convert a value of type TIMESTAMP to type DATE, the time portion of the timestamp value is ignored.
When you convert a value of type TIMESTAMP to type TIME, the date portion of the timestamp is ignored.
When you convert a value of type DATE to type TIMESTAMP, the time components of the timestamp are set to zero.
When you convert a value of type TIME to type TIMESTAMP, the date component is set to the current system date.
It is invalid to convert one interval datatype to the other (for instance because the number of days in a month is variable). Note that you can specify the number of digits for the leading qualifier only, i.e. YEAR(4) TO MONTH, DAY(5).
Errors will be reported if the target type and size are not compatible with the source type and size.
Returns the smallest integer greater than or equal to "numeric_exp".
ceil ( numeric_exp )
Returns the smallest integer greater than or equal to "numeric_exp".
ceiling ( numeric_exp )
ceiling ( 4.22 )
Result: 5
ceiling ( -1.23 )
Result: -1
Returns the number of characters in "string_exp".
char_length ( string_exp )
char_length ( 'Canada' )
Result: 6
Returns the number of characters in "string_exp".
character_length ( string_exp )
character_length ( 'Canada' )
Result: 6
Returns the set of children of a specified member.
children ( member )
Returns the last sibling among the descendants of a member at a specified level. Typically used with a time dimension.
closingPeriod ( level [, member ] )
Returns the first non-NULL argument (or NULL if all arguments are NULL). Requires two or more arguments.
coalesce ( exp_list )
Identifies a cell location (intersection) based on the specified members, each of which must be from a different dimension. However, completeTuple () implicitly includes the default member from all dimensions not otherwise specified in the arguments, rather than the current member. The value of this cell can be obtained with the "value" function. Similar to tuple ().
completeTuple ( member { , member } ) Returns the child member of "member2" with the same relative position as "member1" to its parent.
cousin ( member1 , member2 )
Returns a date value representing the current date of the computer that the database software runs on.
current_date
current_date
Result: 2003-03-04
Returns a time with time zone value, representing the current time of the computer that runs the database software if the database supports this function. Otherwise, it represents the current time of the computer that runs IBM Cognos 8 software.
current_time
current_time
Result: 16:33:11+05:00
Returns a datetime with time zone value, representing the current time of the computer that runs the database software if the database supports this function. Otherwise, it represents the current time of the computer that runs IBM Cognos 8 software.
current_timestamp
current_timestamp
Result: 2003-03-03 16:40:15.535000+05:00
Returns the current member of the hierarchy during an iteration. If "hierarchy" is not present in the context in which the expression is being evaluated, its default member is assumed.
currentMember ( hierarchy )
Returns the default member of "hierarchy".
defaultMember ( hierarchy )
Returns the set of descendants of a member, or set of members, at a specified level (qualified name) or distance (integer 0..n) from the root. Multiple options may be specified (separated by a space) to determine which members are to be returned.
descendants ( member | set_expr , level | distance [
, { self | before | beforewithmember | after } ] ) descendants([national].[Line].[Line].[Line1]->:[PC].[Line (Root)].[Dishwashers], 2, SELF AFTER)
Result: Result: AcmeWash MR AcmeWash AcmeWash HE
self: Only the members at the specified level are included in the final set (this is the default behaviour in the absence of any options).
before: If there are any intermediate levels between the member's level and the one specified, members from those levels are included. If the level specified is the same as the member upon which the function is applied, the member is included in the final set.
beforewithmember: If there are any intermediate levels between the member's level and the one specified, members from those levels are included. The member upon which the function is applied is also included in the final set.
after: If other levels exist after the specified level, members from those levels are included in the final set.
Returns an empty member set for "hierarchy".
emptySet ( hierarchy )
Returns the members of "set_exp1" that are not also in "set_exp2". Duplicates are retained only if the optional keyword ALL is supplied as the third argument.
except ( set_exp1 , set_exp2 [,ALL] )
Returns 'e' raised to the power of "numeric_exp". The constant 'e' is the base of the natural logarithm.
exp ( numeric_exp )
exp ( 2 )
Result: 7.389056
Returns an integer representing the value of datepart (year, month, day, hour, minute, second) in "datetime_exp".
extract ( datepart , datetime_exp )
extract ( year , 2003-03-03 16:40:15.535 )
Result: 2003
extract ( hour , 2003-03-03 16:40:15.535 )
Result: 16
Returns the set resulting from filtering a specified set based on the boolean condition. Each member is included in the result if and only if the corresponding value of "boolean_exp" is true.
filter ( set_exp , boolean_exp )
Returns the first child of a member.
firstChild ( member)
Returns the first child of the parent of a member.
firstSibling ( member )
Returns the largest integer less than or equal to "numeric_exp".
floor ( numeric_exp )
floor ( 3.22 )
Result: 3
floor ( -1.23 )
Result: -2
Evaluates "set_exp2" for each member of "set_exp1" and joins the resulting sets by union. If "ALL" is specified, duplicates in the result are retained.
generate ( set_exp1 , set_exp2 [ , ALL ] )
Returns the first "index_exp" elements of "set_exp". The default for "index_exp" is 1.
head ( set_exp [ , index_exp ] )
Orders the members of a set in a hierarchy. Members in a level are sorted in their natural order. This is the default ordering of the members along a dimension when no other sort conditions are specified.
hierarchize ( set_exp )
Returns the hierarchy that contains the specified level, member, or member set.
hierarchy ( level | member | set_exp )
Returns the intersection of "set_exp1" and "set_exp2". The result retains duplicates only when the optional keyword "ALL" is supplied as the third argument.
intersect ( set_exp1 , set_exp2 [ , ALL ] )
Returns a member from a specified location within a set. The index into the set is zero based.
item ( set_exp , index )
Returns the sibling member that is a specified number of positions prior to a specified member.
lag ( member , index_exp )
Returns the last child of a specified member.
lastChild ( member )
Returns the set of members from the same level that ends with the specified member. The number of members returned is the absolute value of "integer_exp". If "integer_exp" is negative, members following and including the specified member are returned. Typically used with a time dimension.
lastPeriods ( integer_exp , member )
Returns the last child of the parent of a specified member.
lastSibling ( member )
Returns the sibling member that is "index_exp" number of positions following a specified member.
lead ( member , index_exp )
Returns the level of a member.
level ( member )
Returns the level in the hierarchy whose distance from the root is specified by "index".
levels ( hierarchy , index )
Returns the corresponding member in the specified level or hierarchy (of the same dimension). For level-based hierarchies, a level must be specified as the second argument and for parent-child hierarchies, a hierarchy must be specified. An exception is thrown when the second parameter does not resolve to a hierarchy of the dimension that the member of the first parameter belongs to. Note that calculated members are not supported as the first argument.
linkMember ( member , level | hierarchy )
Returns the natural logarithm of the "numeric_exp".
ln ( numeric_exp )
ln ( 4 )
Result: 1.38629
Returns a time value, representing the current time of the computer that runs the database software.
localtime
localtime
Result: 16:33:11
Returns a datetime value, representing the current timestamp of the computer that runs the database software.
localtimestamp
localtimestamp
Result: 2003-03-03 16:40:15.535000
Returns "string_exp" with all uppercase characters shifted to lowercase.
lower ( string_exp )
lower ( 'ABCDEF' )
Result: 'abcdef'
Defines a member based on the specified expression in the specified hierarchy. "String1" is used to identify the member created by this function. It must be unique in the query and must be different from any other member in the same hierarchy. "String2" is used as the caption of the member; if it is absent, the caption is empty. To ensure predictable results, it is recommended to supply the hierarchy parameter. Note: All calculations used as grouping items, whose sibling items are other calculations or member sets, should be explicitly assigned to a hierarchy using this function. The results are not predictable otherwise. The only exception is where the calculation involves only members of the same hierarchy as the siblings. In this case, the calculation is assumed to belong to that hierarchy.
member ( value_exp [ , string1 [ , string2 [ , hierarchy ] ] ] )
Returns the set of members in a hierarchy or level. In the case of a hierarchy, the order of the members in the result is not guaranteed. If a predictable order is required, an explicit ordering function (such as hierarchize) must be used.
members ( hierarchy | level )
Returns the remainder (modulus) of "integer_exp1" divided by "integer_exp2". "Integer_exp2" must not be zero or an exception condition is raised.
mod ( integer_exp1, integer_exp2 )
mod ( 20 , 3 )
Result: 2
Returns the set of members of "set_exp2" evaluated in the context of the current member of "set_exp1".
nestedSet ( set_exp1 , set_exp2 )
Returns the next member in the level to which "member" exists.
nextMember ( member )
Returns the number of bytes in "string_exp".
octet_length ( string_exp )
octet_length ( 'ABCDEF' )
Result: 6
octet_length ( '' )
Result: 0
Returns the first sibling member among the descendants of a member at a specified level. Typically used with a time dimension.
openingPeriod ( level [ , member ] )
Arranges members of a specified set, as determined from the set of values created by evaluating "value_exp" for each value of the set, and modified by the third parameter. There are two varieties of order: hierarchized (ASC or DESC) and non-hierarchized (BASC or BDESC, where B stands for "break hierarchy"). The hierarchized ordering first arranges members according to their position in the hierarchy. It then orders the children of each member according to "value_exp". The non-hierarchized ordering arranges members in the set without regard to the hierarchy. In the absence of an explicit specification, ASC is the default.
order ( set_exp , value_exp [ , ASC | DESC | BASC | BDESC ] )
Returns the zero-based ordinal value (distance from the root level) of the specified level.
ordinal ( level )
Returns a member from a prior period in the same relative position as a specified member. This function is similar to the "Cousin" function, but is more closely related to time series. It takes the ancestor of "member" at "level" (called "ancestor") and the sibling of "ancestor" that lags by "int_exp" positions, and returns the parallel period of "member" among the descendants of that sibling. When unspecified, "int_exp" defaults to 1 and "member" defaults to the current member.
parallelPeriod ( level [ , int_exp [ , member ] ] )
Returns the member that is the parent of "member" or "measure".
parent ( member | measure )
Returns a set of sibling members from the same level as a given member, as constrained by "level". It locates the ancestor of "member" at "level", and returns that ancestor's descendants at the same level as "member" (up to and including "member"). Typically used with a time dimension.
periodsToDate ( level , member )
Returns the integer value representing the starting position of "string_exp1" in "string_exp2" or 0 when the "string_exp1" is not found.
position ( string_exp1 , string_exp2 )
position ( 'C' , 'ABCDEF' )
Result: 3
position ( 'H' , 'ABCDEF' )
Result: 0
Returns "numeric_exp1" raised to the power "numeric_exp2". If "numeric_exp1" is negative then "numeric_exp2" must result in an integer value.
power ( numeric_exp1, numeric_exp2 )
power ( 3 , 2 )
Result: 9
Returns the member that immediately precedes the specified member in the same level.
prevMember ( member )
Returns the value of the attribute that is associated with the role whose name is specified by "string" within the specified context. The second argument is optional only in a number of limited circumstances, where it can be derived from another context. Applications can be made portable across different data sources and models by accessing attributes by role, rather than by query item ID. (For dimensionally modelled relational data sources, assignment of roles is the modeller's responsibility.) Intrinsic roles that are defined for members of all data source types include: '_businessKey', '_memberCaption', '_memberDescription', '_memberUniqueName'.
roleValue ( string [ , member | set_exp ] )
roleValue ( '_memberCaption', [Sales].[Product].[Product].[Product line]->[all].[1] )
Result: Camping Equipment
Returns the root member of a single-root hierarchy.
rootMember ( hierarchy )
Returns the root members of a hierarchy.
rootMembers ( hierarchy )
Returns the list of members defined in the expression. The members must belong to the same hierarchy.
set ( member { , member } ) Returns the children of the parent of the specified member.
siblings ( member )
Returns the square root of "numeric_exp". "Numeric_exp" must be non-negative.
sqrt ( numeric_exp )
sqrt ( 9 )
Result: 3
Returns a subset of members from a specified set starting at "index_exp1" from the beginning. If the count "index_exp2" is specified, that many members are returned (if available). Otherwise, all remaining members are returned.
subset ( set_exp, index_exp1 [ , index_exp2 ] )
Returns the substring of "string_exp" that starts at position "integer_exp1" for "integer_exp2" characters or to the end of "string_exp" if "integer_exp2" is omitted. The first character in "string_exp" is at position 1.
substring ( string_exp , integer_exp1 [ , integer_exp2 ] )
substring ( 'abdefg', 3, 2)
Result: 'de'
Returns the last "index_exp" elements of "set exp". The default for "index_exp" is 1.
tail ( set_exp [ , index_exp ] )
Sorts a set according to the values of "numeric_exp" evaluated at each of the members of "set_exp" and returns the top "index_exp" members.
topCount ( set_exp , index_exp , numeric_exp )
Sorts "numeric_exp2", evaluated at the corresponding members of "set_exp", and picks up the topmost elements whose cumulative total is at least "numeric_exp1" percent of the total.
topPercent ( set_exp , numeric_exp1, numeric_exp2 )
Sorts "numeric_exp2", evaluated at the corresponding members of "set_exp", and picks up the topmost elements whose cumulative total is at least "numeric_exp1".
topSum ( set_exp , numeric_exp1 , numeric_exp2 )
Returns "string_exp" trimmed of leading and trailing blanks or trimmed of a certain character specified in "match_character_exp". "BOTH" is implicit when the first argument is not stated and blank is implicit when the second argument is not stated.
trim ( [ [ TRAILING | LEADING | BOTH ] [ match_character_exp ] , ] string_exp )
trim ( TRAILING 'A' , 'ABCDEFA' )
Result: 'ABCDEF'
trim ( BOTH ' ABCDEF ' )
Result: 'ABCDEF'
Identifies a cell location (intersection) based on the specified members, each of which must be from a different dimension. Implicitly includes the current member from all dimensions not otherwise specified in the arguments. The current member of any dimension not specified in the evaluating context is assumed to be the default member of that dimension. The value of this cell can be obtained with the "value" function.
tuple ( member { , member } ) Returns the union of "set_exp1" and "set_exp2". The result retains duplicates only when the optional keyword "ALL" is supplied as the third argument.
union ( set_exp1 , set_exp2 [ , ALL ] )
Removes all duplicates from the specified set. The remaining members retain their original order.
unique ( set_expr )
Returns "string_exp" with all lowercase characters shifted to uppercase.
upper ( string_exp )
upper ( 'abcdef' )
Result: 'ABCDEF'
Returns the value of the cell identified by a "tuple". Note that the default member of the Measures dimension is the Default Measure.
value ( tuple )
Returns the ASCII code value of the leftmost character of the argument as an integer.
ascii ( string_exp )
Returns the smallest integer greater than or equal to "numeric_exp".
ceiling ( numeric_exp )
Returns a string representation of a date/time value or a decimal number.
char ( exp )
Returns the character that has the ASCII code value specified by "integer_exp". "Integer_exp" should be between 0 and 255.
chr ( integer_exp )
Returns a string that is the result of concatenating "string_exp1" with "string_exp2".
concat ( string_exp1, string_exp2 )
Returns a date from a single input value. "Exp" can be a string or integer representation of a date.
date ( exp )
Returns the day of the month (1-31) from "date_exp". "Date_exp" can be a date value or a string representation of a date.
day ( date_exp )
Returns a character string containing the data source_specific name of the day (for example, Sunday through Saturday or Sun. through Sat. for a data source that uses English, or Sonntag through Samstag for a data source that uses German) for the day portion of "date_exp". "Date_exp" can be a date value or a string representation of a date.
dayname ( date_exp )
Returns the day of the week in "date_exp" as an integer in the range 1 to 7, where 1 represents Sunday. "Date_exp" can be a date value or a string representation of a date.
dayofweek ( date_exp )
Returns the day of the week in "date_exp" as an integer in the range 1 to 7, where 1 represents Monday. "Date_exp" can be a date value or a string representation of a date.
dayofweek_iso ( date_exp )
Returns the day of the year in "date_exp" as an integer in the range 1 to 366. "Date_exp" can be a date value or a string representation of a date.
dayofyear ( date_exp )
Returns an integer representation of a date. "Exp" can be a date value or a string representation of a date.
days ( exp )
Returns the decimal representation of "string_exp1" with precision "numeric_exp1", scale "numeric_exp2" and decimal character "string_exp2". "String_exp1" must be formatted as an SQL Integer or Decimal constant.
dec ( string_exp1 [ , numeric_exp1 [ , numeric_exp2 [ , string_exp2 ] ] ] )
Returns the decimal representation of "string_exp1" with precision "numeric_exp1", scale "numeric_exp2" and decimal character "string_exp2". "String_exp1" must be formatted as an SQL Integer or Decimal constant.
decimal ( string_exp1 [ , numeric_exp1 [ , numeric_exp2 [ , string_exp2 ] ] ] )
Returns an integer value representing the difference between the values returned by the data source_specific soundex function for "string_exp1" and "string_exp2". The value returned ranges from 0 to 4, with 4 indicating the best match. Note that 4 does not mean that the strings are equal.
difference ( string_exp1, string_exp2 )
Returns the character string representation of a non-floating point number.
digits ( numeric_exp )
Returns the floating-point representation of an expression. "Exp" can either be a numeric or string expression.
double ( exp )
Returns the operational state of a particular state monitor.
event_mon_state ( string_exp )
Returns the floating-point representation of a number.
float ( numeric_exp )
Returns the hexadecimal representation of a value.
hex ( exp )
Returns the hour, an integer from 0 (midnight) to 23 (11:00 pm), from "time_exp". "Time_exp" can be a time value or a string representation of a time.
hour ( time_exp )
Returns a string where "integer_exp2" characters have been deleted from "string_exp1" beginning at "integer_exp1" and where "string_exp2" has been inserted into "string_exp1" at start. The first character in the string is at position 1.
insert ( string_exp1, integer_exp1, integer_exp2, string_exp2 )
Returns the integer representation of an expression. "Exp" can be a numeric value or a string representation of a number.
integer ( exp )
Returns the integer representation of an expression. "Exp" can be a numeric value or a string representation of a number.
int ( exp )
Returns an integer value representing the number of days from January 1, 4712 BC (the start of the Julian date calendar) to the date value specified in "exp". "Exp" can be a date value or a string representation of a date.
julian_day ( exp )
Returns "string_exp" with all uppercase characters shifted to lowercase.
lcase ( string_exp )
Returns the leftmost "integer_exp" characters of "string_exp".
left ( string_exp, integer_exp )
Returns the length of the operand in bytes. Exception: double byte string types return the length in characters.
length ( exp )
Returns the starting position of the first occurrence of "string_exp1" within "string_exp2". The search starts at position start "integer_exp" of "string_exp2". The first character in a string is at position 1. If "string_exp1" is not found, zero is returned.
locate ( string_exp1, string_exp2 [ , integer_exp ] )
Returns a long string.
long_varchar ( string_exp )
Returns "string_exp" with leading spaces removed.
ltrim ( string_exp )
Returns the microsecond (time-unit) part of a value. "Exp" can be a timestamp or a string representation of a timestamp.
microsecond ( exp )
Returns an integer value in the range 0 to 86400 representing the number of seconds between midnight and time value specified in the argument. "Exp" can be a time value, a timestamp or a string representation of a time.
midnight_seconds ( exp )
Returns the minute (an integer from 0-59) from "time_exp". "Time_exp" can be a time value, a timestamp, or a string representation of a time.
minute ( time_exp )
Returns the month (an integer from 1-12) from "date_exp".
month ( date_exp )
Returns a character string containing the data source_specific name of the month (for example, January through December or Jan. through Dec. for an English data source, or Januar through Dezember for a German data source) for the month portion of "date_exp".
monthname ( date_exp )
Returns the quarter in "date_exp" as a number in the range 1 to 4, where 1 represents January 1 through March 31.
quarter ( date_exp )
Returns the number of radians converted from "numeric_exp" degrees.
radians ( numeric_exp )
Returns a string consisting of "string_exp" repeated "integer_exp" times.
repeat ( string_exp, integer_exp )
Replaces all occurrences of "string_exp2" in "string_exp1" with "string_exp3".
replace ( string_exp1, string_exp2, string_exp3 )
Returns the rightmost "integer_exp" characters of "string_exp".
right ( string_exp, integer_exp )
Returns "numeric_exp" rounded to "integer_exp" places to the right of the decimal point. If "integer_exp" is negative, "numeric_exp" is rounded to the nearest absolute value "integer_exp" places to the left of the decimal point, e.g., round-near (125, -1) rounds to 130.
round ( numeric_exp, integer_exp )
Returns "string_exp" with trailing spaces removed.
rtrim ( string_exp )
Returns the second (an integer from 0-59) from "time_exp".
second ( time_exp )
Returns an indicator of the sign of "numeric_exp": +1 if "numeric_exp" is positive, 0 if zero, or -1 if negative.
sign ( numeric_exp )
Returns the small integer representation of a number.
smallint ( exp )
Returns a 4 character string code obtained by systematically abbreviating words and names in "string_exp" according to phonetics. Can be used to determine if two strings sound the same, e.g., does sound-of ('SMITH') = sound-of ('SMYTH').
soundex ( string_exp )
Returns a string consisting of "integer_exp" spaces.
space ( integer_exp )
Returns the substring of "string_exp" that starts at position "integer_exp1" for "integer_exp2" characters. The first character in "string_exp" is at position 1.
substr ( string_exp, integer_exp1 [ , integer_exp2 ] )
Returns an unqualified name of a table or view based on the object name in "string_exp1" and the schema name given in "string_exp2". It is used to resolve aliases.
table_name ( string_exp1 [ , string_exp2 ] )
Returns the schema name portion of the two part table or view name based on the object name in "string_exp1" and the schema name in "string_exp2". It is used to resolve aliases.
table_schema ( string_exp1 [ , string_exp2 ] )
Returns a time from a value.
time ( exp )
Returns a timestamp from a value or a pair of values. "Exp1' must represent a date value, and "exp2" must represent a time value.
timestamp ( exp1 [ , exp2 ] )
Returns a datetime in the ISO format (yyyy-mm-dd hh:mm:ss.nnnnnn) converted from the IBM format (yyyy-mm-dd-hh.mm.ss.nnnnnn). If "exp" is a time, it inserts the value of the CURRENT DATE for the date elements and zero for the fractional time element.
timestamp_iso ( exp )
Returns an estimated number of intervals of type "exp1" based on the difference between two timestamps. "Exp2" is the result of subtracting two timestamp types and converting the result to CHAR. Valid values of "exp1" are: 1 Fractions of a second; 2 Seconds; 4 Minutes; 8 Hours; 16 Days; 32 Weeks; 64 Months; 128 Quarters; 256 Years.
timestampdiff ( exp1, exp2 )
Returns the string representation of a timestamp with the format of "string_exp".
to_char ( timestamp_exp , string_exp )
Returns "string_exp1" in which characters from "string_exp3" are translated to the equivalent characters in "string_exp2". "String_exp4" is a single character that is used to pad "string_exp2" if it is shorter than "string_exp3". If only "string_exp1" is present, then this function translates it to uppercase characters.
translate ( string_exp1 [ , string_exp2, string_exp3 [ , string_exp4 ] ] )
Returns "numeric_exp1" truncated to "numeric_exp2" places to the RIGHT of the decimal point. If "numeric_exp2" is negative, "numeric_exp1" is truncated to the absolute value of "numeric_exp2" places to the LEFT of the decimal point.
trunc ( numeric_exp1, numeric_exp2 )
Returns "numeric_exp1" truncated to "numeric_exp2" places to the RIGHT of the decimal point. If "numeric_exp2" is negative, "numeric_exp1" is truncated to the absolute value of "numeric_exp2" places to the LEFT of the decimal point.
truncate ( numeric_exp1, numeric_exp2 )
Returns "string_exp" with all lowercase characters shifted to uppercase.
ucase ( string_exp )
Returns the first non-null argument (or null if all arguments are null). The Value function takes two or more arguments.
value ( exp_list )
Returns a VARCHAR representation of exp, with length numeric_exp.
varchar ( exp [ , numeric_exp ] )
Returns the week of the year in "date_exp" as an integer value in the range 1 to 53.
week ( date_exp )
Returns the year from "date_exp".
year ( date_exp )
Returns the first "numeric_exp" characters of the value of "exp" cast as a string. The whole string is returned when the second argument is not specified.
cast_char ( exp [ , numeric_exp ] )
Returns the value of "exp" cast as a date.
cast_date ( exp )
Returns the value of "exp" cast as a decimal with the precision of "numeric_exp1" and scale of "numeric_exp2".
cast_decimal ( exp [ , numeric_exp1, numeric_exp2 ] )
Returns the value of "exp" cast as a double.
cast_double_precision ( exp )
Returns the value of "exp" cast as a float.
cast_float ( exp )
Returns the value of "exp" cast as a integer.
cast_integer ( exp )
Returns the value of "string_exp" cast as a longvarchar.
cast_longvarchar ( string_exp )
Returns the value of "exp" cast as a smallint.
cast_smallint ( exp )
Returns the value of "string_exp" cast as a time value.
cast_time ( string_exp )
Returns the value of "exp" cast as a datetime.
cast_timestamp ( exp )
Returns the value of "exp" cast as a varchar with length "integer_exp".
cast_varchar ( exp, integer_exp )
Returns the natural logarithm of "numeric_exp".
log ( numeric_exp )
Returns the base ten logarithm of "numeric_exp".
log10 ( numeric_exp )
Generates a random number using "integer_exp" as a seed value.
rand ( integer_exp )
Returns the arccosine of "numeric_exp" in radians. The arccosine is the angle whose cosine is "numeric_exp".
acos ( numeric_exp )
Returns the arcsine of "numeric_exp" in radians. The arcsine is the angle whose sine is "numeric_exp".
asin ( numeric_exp )
Returns the arctangent of "numeric_exp" in radians. The arctangent is the angle whose tangent is "numeric_exp".
atan ( numeric_exp )
Returns the hyperbolic arctangent of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
atanh (numeric_exp )
Returns the arctangent of the x and y coordinates specified by "numeric_exp1" and "numeric_exp2", respectively, in radians. The arctangent is the angle whose tangent is "numeric_exp2" / "numeric_exp1".
atan2 ( numeric_exp1, numeric_exp2 )
Returns the cosine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
cos ( numeric_exp )
Returns the hyperbolic cosine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
cosh ( numeric_exp )
Returns the cotangent of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
cot ( numeric_exp )
Returns "numeric_exp" radians converted to degrees.
degrees ( numeric_exp )
Returns the sine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
sin ( numeric_exp )
Returns the hyperbolic sine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
sinh ( numeric_exp )
Returns the tangent of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
tan ( numeric_exp )
Returns the hyperbolic tangent of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
tanh ( numeric_exp )
Returns the number of elements in a collection column (SET, MULTISET, LIST).
cardinality ( string_exp )
Returns the number of logical characters (which can be distinct from the number of bytes in some East Asian locales) in "string_exp".
char_length ( string_exp )
Returns a string that is the result of concatenating "string_exp1" to "string_exp2".
concat ( string_exp1, string_exp2 )
Returns the date value of "string_exp" or "date_exp" or "int_exp".
date ( string_exp | date_exp | int_exp )
Returns an integer that represents the day of the month.
day ( date_exp )
Adjusts the precision of a DATETIME or DATE expression. The expression cannot be a quoted string representation of a DATE value. If you do not specify first and last qualifiers, the default qualifiers are YEAR TO FRACTION(3). If the expression contains fields that are not specified by the qualifiers, the unwanted fields are discarded. If the first qualifier specifies a larger (that is, more significant) field than what exists in the expression, the new fields are filled in with values returned by the CURRENT function. If the last qualifier specifies a smaller field (that is, less significant) than what exists in the expression, the new fields are filled in with constant values. A missing MONTH or DAY field is filled in with 1, and the missing HOUR to FRACTION fields are filled in with 0.
extend ( date_exp , ' { ' YEAR TO SECOND ' } ' ) EXTEND (some_date_column , {YEAR TO SECOND} ) Returns the hexadecimal encoding of "integer_exp".
hex ( integer_exp )
Returns "string_exp", with the first letter of each word in uppercase and all other letters in lowercase. A word begins after any character other than a letter. Thus, in addition to a blank space, symbols such as commas, periods, colons, and so on, introduce a new word.
initcap ( string_exp )
Returns the number of bytes in "string_exp", not including any trailing blank spaces. For BYTE or TEXT "string_exp", LENGTH returns the full number of bytes, including any trailing blank spaces.
length ( string_exp )
Returns a copy of "string_exp1" that is left-padded by "string_exp2" to the total number of characters specified by "integer_exp". The sequence of "string_exp2" occurs as many times as necessary to make the return string the length specified by "integer_exp".
lpad ( string_exp1, integer_exp, string_exp2 )
Returns a type DATE value with three expressions that evaluate to integers that represent the month (integer_exp1), day (integer_exp2), and year (integer_exp3).
mdy ( integer_exp1, integer_exp2, integer_exp3 )
Returns an integer corresponding to the month portion of "date_exp".
month ( date_exp )
Returns the value of "exp1" if "exp1" is not NULL. If "exp1" is NULL, then the value of "exp2" is returned.
nvl ( exp1, exp2 )
Returns the number of bytes in "string_exp", including any trailing spaces.
octet_length ( string_exp )
Returns a copy of "string_exp1" in which every occurrence of "string_exp2" is replaced by "string_exp3". If you omit the "string_exp3" option, every occurrence of "string_exp2" is omitted from the return string.
replace ( string_exp1, string_exp2 [ , string_exp3 ] )
Returns the rounded value of "numeric_exp". If you omit "integer_exp", the value is rounded to zero digits or to the units place. The digit range of 32 (+ and -) refers to the entire decimal value.
round ( numeric_exp [ , integer_exp ] )
Returns a copy of "string_exp1" that is right-padded by "string_exp2" to the total number of characters specified by "integer_exp". The sequence of "string_exp2" occurs as many times as necessary to make the return string the length specified by "integer_exp".
rpad ( string_exp1, integer_exp, string_exp2 )
Returns the substring of "string_exp" that starts at position "integer_exp1". The first character in "string_exp" is at position 1. "Integer_exp2" can be used to select fewer characters, by default it selects to the end of the string.
substr ( string_exp, integer_exp1 [ , integer_exp2 ] )
Returns the character string "date_exp" with the specified "string_exp" formatting. You can use this function only with built-in data types.
to_char ( date_exp, string_exp )
Returns "string_exp1" as a date according to the date format you specify in the "string_exp2" parameter. If "string_exp1" is NULL, then a NULL value is returned.
to_date ( string_exp1, string_exp2 )
Returns the truncated value of "numeric_exp". If you omit "integer_exp", the value is truncated to zero digits or to the unit’s place. The digit limitation of 32 (+ and -) refers to the entire decimal value.
trunc ( numeric_exp [ , integer_exp ] )
Returns an integer that represents the day of the week; zero (0) represents Sunday, one (1) represents Monday, and so on.
weekday ( date_exp )
Returns a four-digit integer that represents the year.
year ( date_exp )
Returns the log of "numeric_exp" to base 10.
log10 ( numeric_exp )
Returns the natural logarithm of "numeric_exp".
logn ( numeric_exp )
Returns the root value of "numeric_exp". Requires at least one numeric argument (the radians argument). If only "numeric_exp1" is supplied, 2 is used as a default value for "numeric_exp2"; 0 cannot be used as the value of "numeric_exp2".
root ( numeric_exp1[ , numeric_exp2 ] )
Returns the arccosine of "numeric_exp" in radians. The arccosine is the angle whose cosine is "numeric_exp".
acos ( numeric_exp )
Returns the arcsine of "numeric_exp" in radians. The arcsine is the angle whose sine is "numeric_exp".
asin ( numeric_exp )
Returns the arctangent of "numeric_exp" in radians. The arctangent is the angle whose tangent is "numeric_exp".
atan ( numeric_exp )
Returns the arctangent of the x and y coordinates specified by "numeric_exp1" and "numeric_exp2", respectively, in radians. The arctangent is the angle whose tangent is "numeric_exp1".
atan2 ( numeric_exp1, numeric_exp2 )
Returns the cosine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
cos ( numeric_exp )
Returns the sine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
sin ( numeric_exp )
Returns the tangent of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
tan ( numeric_exp )
Returns a number representing the ascii code value of the leftmost character of "string_exp".
ascii(string_exp)
Returns the smallest integer greater than or equal to "numeric_exp".
ceiling(numeric_exp)
Returns the character that has the ASCII code value specified by "integer_exp". "Integer_exp" should be between 0 and 255.
chr(integer_exp)
Returns a string that is the result of concatenating "string_exp1" to "string_exp2".
concat(string_exp1, string_exp2)
Returns a date value representing the current date of the computer that the database software runs on.
curdate()
Returns a time value representing the current time of the computer that the database software runs on.
curtime()
Returns a character string containing the data source specific name of the day (for example, Sunday through Saturday or Sun. through Sat. for an English data source, or Sonntag through Samstag for a German data source) for the day portion of "date_exp".
dayname(date_exp)
Returns the day of the month (1-31) from "date_exp". Returns the days field (a signed integer) from "interval_exp".
dayofmonth(date_exp|interval_exp)
Returns the day of the week in "date_exp" as an integer (1-7), where 1 represents Monday.
dayofweek(date_exp)
Returns the day of the year in "date_exp" as an integer (1-366).
dayofyear(date_exp)
Returns the hour (an integer from 0 (midnight) to 23 (11:00 pm)) from "time_exp".
hour(time_exp)
Searches "string_exp1" for the first occurrence of "string_exp2" and returns an integer specifying the position of "string_exp2". The optional argument "integer_exp1" sets the starting position for the search. If omitted, the search begins at the first character position of "string_exp1". The optional argument "integer_exp2" specifies the type of string comparison. "Integer_exp1" is required if "integer_exp2" is specified.
instr ( [ integer_exp1 , ] string_exp1, string_exp2 [ , integer_exp2 ] )
Returns "string_exp" with all uppercase characters shifted to lowercase.
lcase(string_exp)
Returns the leftmost "integer_exp" characters of "string_exp".
left(string_exp, integer_exp)
Returns the number of characters in "string_exp", excluding trailing blanks and the string termination character.
length(string_exp)
Returns the starting position of the first occurrence of "string_exp1" within "string_exp2". The search starts at position "integer_exp" of "string_exp2". The first character in a string is at position 1. If "string_exp1" is not found then zero is returned.
locate(string_exp1, string_exp2 [ , integer_exp ] )
Returns "string_exp" with leading spaces removed.
ltrim(string_exp)
Returns the minute (an integer from 0-59) from "time_exp".
minute(time_exp)
Returns the month (an integer from 1-12) from "date_exp".
month(date_exp)
Returns a character string containing the data source specific name of the month (for example, January through December or Jan. through Dec. for an English data source, or Januar through Dezember for a German data source) for the month portion of "date_exp".
monthname(date_exp)
Returns a datetime value representing the current date and time of the computer that the database software runs on.
now()
Returns the starting position of "string_exp1" in "string_exp2". The first character in a string is at position 1.
position(string_exp1, string_exp2)
Returns the quarter in "date_exp" as a number (1-4), where 1 represents January 1 through March 31.
quarter(date_exp)
Returns the rightmost "integer_exp" characters of "string_exp".
right(string_exp, integer_exp)
Returns "numeric_exp" rounded to the nearest value "integer_exp" places right of the decimal point. If "integer_exp" is negative, "numeric_exp" is rounded to the nearest absolute value, "integer_exp" places to the left of the decimal point.
round(numeric_exp, integer_exp)
Returns "string_exp" with trailing spaces removed.
rtrim(string_exp)
Returns an indicator of the sign of "numeric_exp": +1 if positive, 0 if zero, or -1 if negative.
sign(numeric_exp)
Returns a string consisting of "integer_exp" spaces.
space(integer_exp)
Returns the substring of "string_exp" that starts at position "integer_exp1" for "integer_exp2" characters. The first character in "string_exp" is at position 1.
substr(string_exp, integer_exp1, integer_exp2)
Returns the substring of "string_exp" that starts at position "integer_exp1" for "integer_exp2" characters. The first character in "string_exp" is at position 1.
substring(string_exp, integer_exp1, integer_exp2)
Returns "string_exp" with trailing spaces removed.
truncate(string_exp)
Returns "string_exp" with all lowercase characters shifted to uppercase.
ucase(string_exp)
Returns the week of the year in "date_exp" as an integer value (1-53), where 1 represents the first week of the year.
week(date_exp)
Returns the year from "date_exp".
year(date_exp)
Returns the value of "exp" cast as a decimal.
cast_decimal(exp)
Returns the value of "exp" cast as a float.
cast_float(exp)
Returns the value of "exp" cast as an integer.
cast_integer(exp)
Returns the value of "string_exp" cast as a numeric value.
cast_numeric(string_exp)
Returns the value of "exp" cast as a real.
cast_real(exp)
Returns the value of "exp" cast as a smallint.
cast_smallint(exp)
Returns the value of "exp" cast as a varchar.
cast_varchar(exp)
Returns the natural logarithm of "numeric_exp".
log(numeric_exp)
Generates a random number using "integer_exp" as a seed value.
rand(integer_exp)
Returns the arctangent of "numeric_exp" in radians. The arctangent is the angle whose tangent is "numeric_exp".
atan(numeric_exp)
Returns the cosine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
cos(numeric_exp)
Returns the sine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
sin(numeric_exp)
Returns the tangent of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
tan(numeric_exp)
Returns the datetime resulting from adding "integer_exp" months to "date_exp".
add_months ( date_exp, integer_exp )
Returns a number representing the ascii code value of the leftmost character of "string_exp", e.g. ascii('A') is 65.
ascii ( string_exp )
Returns the smallest integer greater than or equal to "numeric_exp".
ceil ( numeric_exp )
Returns the number of characters in "string_exp".
char_length ( string_exp )
Returns the character that has the ASCII code value specified by "integer_exp". "Integer_exp" should be between 0 and 255.
chr ( integer_exp )
Returns a string that is the result of concatenating "string_exp1" to "string_exp2".
concat ( string_exp1, string_exp2 )
Compares "expr" to each search value one by one. If "expr" is equal to a search, then it returns the corresponding result. If no match is found, it returns "default". If "default" is omitted, it returns null.
decode ( expr , search , result [, search , result]... [, default] )
Returns internal representation of "expr" with the format of "numeric_exp1" starting from position "numeric_exp2" for "numeric_exp3".
dump ( expr [ , numeric_exp1 [ , numeric_exp2 [ , numeric_exp3 ] ] ] )
Returns the greatest value in "exp_list".
greatest ( exp_list )
Returns "string_exp", with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.
initcap ( string_exp )
Searches "string_exp1" starting at position "integer_exp1" for the "integer_exp2" occurance of "string_exp2". If "integer_exp1" is negative then the search is backwards from the end of "string_exp1". Returns an integer indicating the position of "string_exp2".
instr ( string_exp1, string_exp2 [ , integer_exp1 [ , integer_exp2 ] ] )
Searches "string_exp1" starting at position "integer_exp1" for the "integer_exp2" occurance of "string_exp2". If "integer_exp1" is negative then the search is backwards from the end of "string_exp1". The result returned indicates the position (byte number) where the search was found.
instrb ( string_exp1, string_exp2 [ , integer_exp1 [ , integer_exp2 ] ] )
Returns the least value in "exp_list".
least ( exp_list )
Returns the number of characters in "string_exp".
length ( string_exp )
Returns the number of bytes in "string_exp".
lengthb ( string_exp )
Returns "string_exp1" padded to length "integer_exp" with occurrences of "string_exp2". If "string_exp1" is longer than "integer_exp", the appropriate portion of "string_exp1" is returned.
lpad ( string_exp1, integer_exp [ , string_exp2 ] )
Returns "string_exp1", with leading characters removed up to the first character not in "string_exp2", e.g. ltrim('xyxXxyAB', 'xy') returns 'XxyAB'.
ltrim ( string_exp1 [ , string_exp2 ] )
Returns the number of months from "date_exp1" to "date_exp2". If "date_exp1" is later than "date_exp2" then the result will be a positive number. The days and time portion of the difference are ignored, i.e. the months are not rounded, except if "date_exp1" and "date_exp2" are the last days of a month.
months_between ( date_exp1, date_exp2 )
Returns the datetime in timezone "new_tz" for "datetime_exp" in "old_tz" timezone. "Old_tz" and "new_tz" can be one of 'AST', 'ADT', 'BST', 'BDT', 'CST', 'CDT', 'EST', 'EDT', 'HST', 'HDT', 'MST', 'MDT', 'NST', 'PST', 'PDT', 'YST' or 'YDT'.
new_time ( datetime_exp, old_tz, new_tz )
Returns the datetime of the first weekday named by "string_exp" that is later than "datetime_exp". The return value has the same hours, minutes, and seconds as "datetime_exp".
next_day ( datetime_exp, string_exp )
Returns "string_exp1" with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric. "String_exp2" specifies the sorting sequence.
nls_initcap ( string_exp1 [ , string_exp2 ] )
Returns "string_exp1" with all letters in lowercase. "String_exp2" specifies the sorting sequence.
nls_lower ( string_exp1 [ , string_exp2 ] )
Returns "string_exp1" with all letters in uppercase. "String_exp2" specifies the sorting sequence.
nls_upper ( string_exp1 [ , string_exp2 ] )
Returns "exp" if not null, otherwise returns "constant". Valid for "numeric_exp", "string_exp", "date_exp", and "time_exp".
nvl ( exp, constant )
Replaces all occurrences of "string_exp2" in "string_exp1" with "string_exp3". If "string_exp3" is not specified then it replaces all occurrences with null (ie: removes all occurances of "string_exp2").
replace ( string_exp1, string_exp2 [ , string_exp3 ] )
Returns "numeric_exp" rounded to the nearest value "integer_exp" places right of the decimal point. If "integer_exp" is negative, "numeric_exp" is rounded to the nearest absolute value "integer_exp" places to the left of the decimal point, e.g., round (125, -1) rounds to 130.
round ( numeric_exp [ , integer_exp ] )
Returns "string_exp1" right-padded to length "integer_exp" with occurrences of "string_exp2". If "string_exp1" is longer than "integer_exp", the appropriate portion of "string_exp1" is returned. If "string_exp2" is not specified then spaces are used.
rpad ( string_exp1, integer_exp [ , string_exp2 ] )
Returns "string_exp1", with final characters removed after the last character not in "string_exp2", e.g. rtrim('ABxXxyx', 'xy') returns 'ABxX'. If "string_exp2" is not specified, the final space characters are removed.
rtrim ( string_exp1 [ , string_exp2 ] )
Returns an indicator of the sign of "numeric_exp": +1 if positive, 0 if zero, or -1 if negative.
sign ( numeric_exp )
Returns a character string containing the phonetic representation of "string_exp".
soundex ( string_exp )
Returns the substring of "string_exp" that starts at position "integer_exp1". The first character in "string_exp" is at position 1. "Integer_exp2" can be used to select fewer characters; by default it selects characters to the end of the string.
substr ( string_exp, integer_exp1 [ , integer_exp2 ] )
Returns the substring of "string_exp" that starts at position "numeric_exp1" expressed in bytes. The first byte in "string_exp" is at position 1. "Integer_exp2" can be used to select fewer bytes; by default it selects bytes to the end of the string.
substrb ( string_exp, numeric_exp1 [ , numeric_exp2 ] )
Returns a datetime value representing the current date and time of the computer that the database software runs on.
{ sysdate } Returns the string representation of "exp" with the format of "string_exp". "Exp" can either be a date value or a numeric value.
to_char ( exp [ , string_exp ] )
Converts "string_exp1" to a datetime value as specified by the format "string_exp2". "String_exp3" specifies the format elements. (e.g. language)
to_date ( string_exp1 [ , string_exp2 [ , string_exp3 ] ] )
Converts "string_exp1" to a numeric value as specified by the format "string_exp2". "String_exp3" specifies the format elements. (e.g. currency information)
to_number ( string_exp1, string_exp2, string_exp3 )
Returns "string_exp1", with all occurrences of each character in "string_exp2" replaced by its corresponding character in "string_exp3".
translate ( string_exp1, string_exp2, string_exp3 )
Truncates "date_exp" using the format specified by "string_exp". For example, if "string_exp" is 'YEAR' then "date_exp" is truncated to the first day of the year.
trunc ( date_exp, string_exp )
Truncates digits from "numeric_exp1" using "numeric_exp2" as the precision.
trunc ( numeric_exp1, numeric_exp2 )
Returns the username of the current Oracle user.
{ user } Returns the number of bytes in the internal representation of "exp". "Exp" must be a string expression.
vsize ( exp )
Returns the logarithm of "numeric_exp2" to the base "numeric_exp1".
log ( numeric_exp1, numeric_exp2 )
Returns the arccosine of "numeric_exp" in radians. The arccosine is the angle whose cosine is "numeric_exp".
acos ( numeric_exp )
Returns the arcsine of "numeric_exp" in radians. The arcsine is the angle whose sine is "numeric_exp".
asin ( numeric_exp )
Returns the arctangent of "numeric_exp" in radians. The arctangent is the angle whose tangent is "numeric_exp".
atan ( numeric_exp )
Returns the arctangent of the x and y coordinates specified by "numeric_exp1" and "numeric_exp2", respectively, in radians. The arctangent is the angle whose tangent is "numeric_exp2" / "numeric_exp1".
atan2 ( numeric_exp1 ,numeric_exp2 )
Returns the cosine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
cos ( numeric_exp )
Returns the hyperbolic cosine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
cosh ( numeric_exp )
Returns the sine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
sin ( numeric_exp )
Returns the hyperbolic sine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
sinh ( numeric_exp )
Returns the tangent of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
tan ( numeric_exp )
Returns the hyperbolic tangent of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
tanh ( numeric_exp )
Returns the smallest integer greater than or equal to "numeric_exp" or "string_exp". Note that "string_exp" must represent a valid numeric value.
ceil ( numeric_exp | string_exp )
Returns "string_exp1" concatenated with "string_exp2".
concat ( string_exp1 , string_exp2 )
Returns the database username (authorization ID) of the current user.
{ current_user } Returns a date value. "Exp" can be either characters or a timestamp.
date ( exp )
Adds an interval to a datetime value and returns a result that is the same datetime data type as that of "datetime_exp". The datepart refers to year, month, day, hour, minute, second. The interval must be an integer and "datetime_exp" can be date, time, or timestamp.
dateadd ( { datepart }, interval, datetime_exp ) Determines the difference between two datetime expressions and returns an integer result in "datepart" units. "Datepart" refers to year, month, day, hour, minute, or second. "Datetime_exp1" and "datetime_exp2" can be dates, times, or timestamps.
datediff ( { datepart }, datetime_exp1, datetime_exp2
) Extracts the specified datepart component and returns its value as a character string. The datepart refers to year, month, day, hour, minute, or second. "Datetime_exp" can be a date, a time, or a timestamp.
datename ( { datepart }, datetime_exp ) Converts a specified value to a decimal value and returns a value with the data type decimal (precision, scale). The default value of precision is 9. The default value of scale is 0.
dec ( exp, [precision, scale] )
Converts a specified value to a decimal value and returns a value with the data type decimal (precision, scale). The default value of precision is 9. The default value of scale is 0.
decimal ( exp, [precision, scale] )
Compares and converts "exp" to another value. If "exp" matches the target, it is replaced, otherwise it is replaced by default or by NULL if no default is specified. The expressions can be any data type as long as they are all the same data type.
decode ( exp, target, replacement [,default] )
Converts a specified value into a double-precision floating-point value.
float ( numeric_exp )
Tests "exp" for missing values and replaces each one with a specified value. If "exp" is NULL, "substitute" is returned, otherwise it returns the value of "exp". The expressions can be any data type as long as they are all the same data type.
ifnull ( exp, substitute )
Converts "numeric_exp" into an integer value and returns an integer value. If the argument is NULL, it returns NULL.
int ( numeric_exp )
Converts "numeric_exp" into an integer value and returns an integer value. If the argument is NULL, it returns NULL.
integer ( numeric_exp )
Returns an integer result specifying the number of characters in "string_exp". If "string_exp" is NULL, it returns NULL.
length ( string_exp )
Returns an integer result specifying the number of bytes in "string_exp". If "string_exp" is NULL, it returns NULL.
lengthb ( string_exp )
Removes leading blanks from "string_exp". If "string_exp" is NULL, it returns NULL.
ltrim ( string_exp )
Returns NULL if both "exp1" and "exp2" have the same value. If they have different values, the value of "exp1" is returned. "Exp1" and "exp2" can be any data type as long as they are the same data type.
nullif ( exp1, exp2 )
Returns an integer that is relative to the beginning byte position of "string_exp1" in "string_exp2". If "string_exp1" is not located, the result is 0. If "string_exp1" is of zero length, the result is 1. If "string_exp1" is NULL, an error message is returned. If "string_exp2" is NULL, the result is 0.
positionb ( string_exp1, string_exp2 )
Returns a real value. If "numeric_exp" is NULL, it returns NULL.
real ( numeric_exp )
Returns "numeric_exp" rounded to the nearest value "integer_exp" places to the right of the decimal point. If "integer_exp" is negative, "numeric_exp" is rounded to the nearest absolute value "integer_exp" places to the left of the decimal point, e.g., round (125, -1) rounds to 130.
round ( numeric_exp, integer_exp )
Removes trailing blanks from "string_exp". If "string_exp" is NULL, it returns NULL.
rtrim ( string_exp )
Determines the sign of "numeric_exp", and returns 1 for a positive value, –1 for a negative value, and 0 for zero.
sign ( numeric_exp )
Converts numeric or datetime values to character strings. "Exp" can be either numeric or datetime.
string ( exp [, length [, scale]] )
Returns a substring of "string_exp" that begins at position "start_integer" and continues for "length_integer" characters. If "length_integer" is not specified, a substring from the start to the end of "string_exp" is returned.
substr ( string_exp, start_integer, length_integer )
Returns a substring of "string_exp" that begins at position "start_integer" and continues for "length_integer" bytes. If "length_integer" is not specified, a substring from the start to the end of "string_exp" is returned.
substrb ( string_exp, start_integer, length_integer )
Creates a time value from a character string or a time-stamp data type expression.
time ( exp )
Creates a time-stamp value from a character string.
timestamp ( timestamp_exp )
Creates a time-stamp value from "time_exp" and "date_exp". If either "time_exp" or "date_exp" is NULL, the resulting time-stamp expression is also NULL.
timestamp ( date_exp, time_exp )
This function is a datetime scalar function that operates on a date, time, or timestamp data type and returns the character string specified by a given format.
to_char ( source_date, format_str )
Returns a number representing the ascii code value of the leftmost character of "string_exp", e.g. ascii('A') is 65.
ascii ( string_exp )
Returns the character that has the ASCII code value specified by "integer_exp". "Integer_exp" should be between 0 and 255. e.g. char(65) has the value 'A'.
char ( integer_exp )
Searches "string_exp2" for the first occurrence of "string_exp1" and returns an integer. "Start_location" is the character position to start searching for "string_exp1" in "string_exp2". If "start_location" is not specified, is a negative number, or is zero, the search starts at the beginning of "string_exp2".
charindex ( string_exp1, string_exp2 [ , start_location ] )
Returns the name of the current user.
{ current_user } Returns the length in bytes of "string_exp".
datalength ( string_exp )
Returns the date resulting from the addition of "integer_exp" units (indicated by datepart (day, month, year)) to "date_exp".
dateadd ( {datepart}, integer_exp, date_exp ) Returns the number of units indicated by datepart (day, month, year) between "date_exp1" and "date_exp2".
datediff ( {datepart}, date_exp1, date_exp2 ) Returns part of a datetime, smalldatetime, date or time value as an ASCII string. Note that the "datepart" argument must be a keyword representing a datepart or its abbreviation recognized by Microsoft SQL Server and must be enclosed in curly brackets.
datename ( ' { ' datepart ' } ' , date_exp ) datename ( {mm}, 2000-01-01 ) Result: January.
Returns part of a datetime, smalldatetime, date or time value (for example, the month) as an integer. Note that the "datepart" argument must be a keyword representing a datepart or its abbreviation recognized by Microsoft SQL Server and must be enclosed in curly brackets.
datepart ( ' { ' datepart ' } ' , date_exp ) datepart ( {wk}, 2000-01-01 ) Result: 1 (first week of the year).
Returns the day portion of "date_exp". Same as extract (day from date_exp).
day ( date_exp )
Returns an integer value representing the difference between the values returned by the data source_specific soundex function for "string_exp1" and "string_exp2". The value returned ranges from 0 to 4, with 4 indicating the best match. Note that 4 does not mean that the strings are equal.
difference ( string_exp1, string_exp2 )
Returns a datetime value representing the current date and time of the computer that the database software runs on.
getdate ()
Returns the leftmost "integer_exp" characters of "string_exp".
left ( string_exp, integer_exp )
Returns "string_exp" with leading spaces removed.
ltrim ( string_exp )
Returns the month portion of "date_exp". Same as extract (month from date_exp).
month ( date_exp )
Returns an integer which represents the starting position of the first occurrence of "string_exp1" in the "string_exp2". Returns 0 if "string-exp1" is not found. The % wildcard character must precede and follow "string_exp1", except when searching for first or last characters.
patindex ( string_exp1, string_exp2 )
patindex ( '%nos%', 'Cognos' )
Result: 4
Replaces all occurrences of "string_exp2" in "string_exp1" with "string_exp3".
replace ( string_exp1 , string_exp2 , string_exp3 )
Returns a string consisting of "string_exp" repeated "integer_exp" times.
replicate ( string_exp, integer_exp )
Returns "string_exp" in reverse order.
reverse ( string_exp )
Returns the rightmost "integer_exp" characters of "string_exp".
right ( string_exp, integer_exp )
Returns "numeric_exp" rounded to the nearest value "integer_exp" places to the right of the decimal point.
round ( numeric_exp, integer_exp )
Returns "string_exp" with trailing spaces removed.
rtrim ( string_exp )
Returns an indicator of the sign "numeric_exp": +1 if "numeric_exp" is positive, 0 if zero or -1 if negative.
sign ( numeric_exp )
Returns a four character string representing the sound of the words in "string_exp".
soundex ( string_exp )
Returns a string consisting of "integer_exp" spaces.
space ( integer_exp )
Returns a string representation of "numeric_exp" where "integer_exp1" is the length of the string returned and "integer_exp2" is the number of decimal digits.
str ( numeric_exp [ , integer_exp1 [ , integer_exp2 ] ] )
Returns a string where "integer_exp2" characters have been deleted from "string_exp1" beginning at "integer_exp1" and where "string_exp2" has been inserted into "string_exp1" at start. The first character in a string is at position 1.
stuff ( string_exp1, integer_exp1, integer_exp2, string_exp2 )
Returns the year portion of "date_exp". Same as extract (year from date_exp).
year ( date_exp )
Returns the value of "exp" cast as a character. A limit of 30 characters is returned.
cast_char ( exp )
Returns the value "exp" cast as a float.
cast_float ( exp )
Returns the value of "exp" cast as an integer.
cast_integer ( exp )
Returns the value of "exp" cast as a real.
cast_real ( exp )
Returns the value of "exp" cast as a small integer.
cast_smallint ( exp )
Returns the natural logarithm of "numeric_exp".
log ( numeric_exp )
Returns the base ten logarithm of "numeric_exp".
log10 ( numeric_exp )
Returns the constant value of pi as a floating point value.
pi ()
Generates a random number using "integer_exp" as the seed value.
rand ( integer_exp )
Returns the arccosine of "numeric_exp" in radians. The arccosine is the angle whose cosine is "numeric_exp".
acos ( numeric_exp )
Returns the arcsine of "numeric_exp" in radians. The arcsine is the angle whose sine is "numeric_exp".
asin ( numeric_exp )
Returns the arctangent of "numeric_exp" in radians. The arctangent is the angle whose tangent is "numeric_exp".
atan ( numeric_exp )
Returns the arctangent of the x and y coordinates specified by "numeric_exp1" and "numeric_exp2", respectively, in radians. The arctangent is the angle whose tangent is "numeric_exp1".
atn2 ( numeric_exp1, numeric_exp2 )
Returns the cosine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
cos ( numeric_exp )
Returns the cotangent of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
cot ( numeric_exp )
Returns "numeric_exp" radians converted to degrees.
degrees ( numeric_exp )
Returns the number of radians converted from "numeric_exp" degrees.
radians ( numeric_exp )
Returns the sine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
sin ( numeric_exp )
Returns the tangent of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
tan ( numeric_exp )
Returns the account string for the current user.
{account} Returns the date or the datetime resulting from adding "integer_exp" months to 'date_exp" or "datetime_exp".
add_months ( date_exp | datetime_exp, integer_exp )
Returns the number of bytes contained in "byte_exp". "Byte_exp" is restricted to BYTE or VARBYTE.
bytes ( byte_exp )
Evaluates a list of conditions and returns the position of the first condition that evaluates to TRUE, provided that no prior condition in the list evaluates to UNKNOWN. The keywords must be enclosed in curly brackets. The NO CASE is an optional condition that evaluates to TRUE if every "conditional_exp" in the list evaluates to FALSE. The NO CASE OR UNKNOWN condition evaluates to TRUE if every "conditional_exp" in the list evaluates to FALSE, or if a "conditional_exp" evaluates to UNKNOWN and all prior conditions in the list evaluate to FALSE. The UNKNOWN is an optional condition that evaluates to TRUE if a conditional_expression evaluates to UNKNOWN and all prior conditions in the list evaluate to FALSE.
case_n ( condition_exp_list [, NO CASE | UNKNOWN | NO CASE OR UNKNOWN [, UNKNOWN ]] )
Returns the hexadecimal representation for "string_exp".
char2hexint ( string_exp )
Returns an integer value representing the number of logical characters or bytes contained in "string_exp".
characters ( string_exp )
Returns the name of the default database for the current user.
{database} Returns the current date.
{date} Returns the declared format for "exp". The data type returned by a FORMAT phrase is a variable character string of up to 30 characters.
format ( expression )
Returns the starting position of "string_exp2" in "string_exp1".
index ( string_exp1, string_exp2 )
Computes the base 10 logarithm of "numeric_exp". "Numeric_exp" must be a non-zero, positive, numeric expression.
log ( numeric_exp )
Returns NULL if "scalar_exp1" and "scalar_exp2" are equal. Otherwise, it returns "scalar_exp1". "Scalar_exp1" and "scalar_exp2" can be any data type.
nullif ( scalar_exp1, scalar_exp2 )
Converts data from zero to null to avoid division by zero.
nullifzero ( numeric_exp )
Returns the current profile for the session or NULL if none.
{profile} Returns a random integer number for each row of the results table. "Lower_bound" and "upper_bound" are integer constants. The limits for "lower_bound" and "upper_bound" range from -2147483648 to 2147483647 inclusive. "Upper_bound" must be greater than or equal to "lower_bound".
random ( lower_bound, upper_bound )
Returns the current role for the session or NULL if none.
{role} Returns the number of the session for the current user.
{session} Returns a character string that represents the Soundex code for "string_exp".
soundex ( string_exp )
Returns the substring of "string_exp" that starts at position "integer_exp1". The first character in "string_exp" is at position 1. "Integer_exp2" can be used to select fewer characters, by default it selects character to the end of the string.
substr ( string_exp, integer_exp1 [ , integer_exp2 ] )
Returns the current time based on a 24-hour day.
{time} Returns the data type defined for "exp".
type ( exp )
Returns the user name of the current user.
{user} Returns a character string that represents the vargraphic code for "string_exp".
vargraphic ( string_exp )
Converts data from NULL to 0 to avoid errors created by a NULL value. If "numeric_exp" is not NULL, it returns the value of "numeric_exp". If "numeric_exp" is a character string, it is converted to a numeric value of FLOAT data type. If "numeric_exp" is NULL or zero, it returns zero.
zeroifnull ( numeric_exp )
Returns the arccosine of "numeric_exp" in radians. The arccosine is the angle whose cosine is "numeric_exp". "Numeric_exp" must be between -1 and 1, inclusive.
acos ( numeric_exp )
Returns the inverse hyperbolic cosine of "numeric_exp" where "numeric_exp" can be any real number equal to or greater than 1.
acosh ( numeric_exp )
Returns the arcsine of "numeric_exp" in radians. The arcsine is the angle whose sine is "numeric_exp". "Numeric_exp" must be between -1 and 1, inclusive.
asin ( numeric_exp )
Returns the inverse hyperbolic sine of "numeric_exp" where "numeric_exp" can be any real number.
asinh ( numeric_exp )
Returns the arctangent of "numeric_exp" in radians where the arctangent is the angle whose tangent is "numeric_exp".
atan ( numeric_exp )
Returns the arctangent of the x and y coordinates specified by "numeric_exp1" and "numeric_exp2", respectively, in radians. The returned angle will be between - and π radians, excluding π.
atan2 ( numeric_exp1, numeric_exp2 )
Returns the inverse hyperbolic tangent of "numeric_exp" where "numeric_exp" can be any real number between 1 and -1, excluding 1 and -1.
atanh (numeric_exp )
Returns the cosine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
cos ( numeric_exp )
Returns the hyperbolic cosine of "numeric_exp" where "numeric_exp" can be any real number.
cosh ( numeric_exp )
Returns the sine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
sin ( numeric_exp )
Returns the hyperbolic sine of "numeric_exp" where "numeric_exp" can be any real number.
sinh ( numeric_exp )
Returns the tangent of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
tan ( numeric_exp )
Returns the hyperbolic tangent of "numeric_exp" where "numeric_exp" can be any real number.
tanh ( numeric_exp )
Returns the arccosine of "numeric_exp in radians". The arccosine is the angle whose cosine is "numeric_exp".
arccos ( numeric_exp )
Returns the arcsine of "numeric_exp" in radians. The arcsine is the angle whose sine is "numeric_exp".
arcsin ( numeric_exp )
Returns the arctangent of "numeric_exp" in radians. The arctangent is the angle whose tangent is "numeric_exp".
arctan ( numeric_exp )
Returns the cosine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
cos ( numeric_exp )
Returns the sine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
sin ( numeric_exp )
Returns the tangent of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
tan ( numeric_exp )
Returns the hyperbolic cosine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
coshyp ( numeric_exp )
Returns the hyperbolic sine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
sinhyp ( numeric_exp )
Returns the hyperbolic tangent of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
tanhyp ( numeric_exp )
Returns the base ten logarithm of "numeric_exp".
log10 ( numeric_exp )
Returns a number representing the ascii code value of the leftmost character of "string_exp".
ascii ( string_exp )
ascii( 'A' )
Result: 65
Converts "integer_exp" to a character value. Char is usually used as the inverse of ascii where "integer_exp" must be between 0 and 255. If the resulting value is the first byte of a multibyte character, the character may be undefined.
char ( integer_exp )
Searches "string_exp2" for the first occurrence of "string_exp1" and returns an integer that represents its starting position. If "string_exp1" is not found, zero is returned. If "string_exp1" contains wildcard characters, charindex treats them as literals.
charindex ( string_exp1, string_exp2 )
Returns the length in bytes of "string_exp".
datalength ( string_exp )
Returns the date resulting from adding "integer_exp" units indicated by datepart (day, month, year) to "date_exp". Note that the datepart argument must be enclosed in curly brackets.
dateadd ( ' { ' datepart ' } ' , integer_exp, date_exp
) dateadd ( {dd}, 16, 1997-06-16 ) Result: Jul 2, 1997
Returns the number of units indicated by datepart (day, month, year) between "date_exp1" and "date_exp2". Note that the datepart argument must be enclosed in curly brackets.
datediff ( ' { ' datepart ' } ' , date_exp1, date_exp2
) datediff ( {yy}, 1984-01-01, 1997-01-01 ) Result: 13
Returns part of a datetime, smalldatetime, date, or time value as an ASCII string. Note that the datepart argument must be enclosed in curly brackets.
datename ( ' { ' datepart ' } ' , date_exp ) datename ( {mm}, 1999-05-01 ) Result: May
Returns part of a datetime, smalldatetime, date, or time value (e.g. month) as an integer. Note that the datepart argument must be enclosed in curly brackets.
datepart ( ' { ' datepart ' } ' , date_exp ) datepart ( {mm}, 1999-05-01 ) Result: 5
Returns the day of the month (1-31) from "date_exp".
day ( date_exp )
Returns an integer value representing the difference between the values returned by the data source specific soundex function for "string_exp1" and "string_exp2". The value returned ranges from 0 to 4, with 4 indicating the best match. Note that 4 does not mean that the strings are equal.
difference ( string_exp1, string_exp2 )
Returns current system date and time.
getdate ()
Returns the leftmost "integer_exp" characters of "string_exp".
left ( string_exp, integer_exp )
Returns "string_exp" with any leading spaces removed.
ltrim ( string_exp )
Returns the month (1-12) from "date_exp".
month ( date_exp )
Returns an integer representing the starting position of the first occurrence of "string_exp1" in "string_exp2' or returns 0 if "string-exp1" is not found. By default, patindex returns the offset in characters. The offset can be returned in bytes by setting the return type to bytes. The % wildcard character must precede and follow the pattern in "string_exp1", except when searching for first or last characters.
patindex ( string_exp1, string_exp2 [ using {bytes |
chars | characters} ] ) Returns a random float value between 0 and 1, using the optional "integer_exp" as a seed value.
rand ( integer_exp )
Returns a string with the same datatype as "string_exp", containing the same expression repeated "integer_exp" times or as many times as will fit into a 225-byte space, whichever is less.
replicate ( string_exp, integer_exp )
Returns the reverse of "string_exp".
reverse ( string_exp )
Returns the rightmost "integer_exp" characters of "string_exp".
right ( string_exp, integer_exp )
Returns "numeric_exp" rounded to the nearest value "integer_exp" places to the right of the decimal point.
round ( numeric_exp, integer_exp )
Returns "string_exp" with trailing spaces removed.
rtrim ( string_exp )
Returns a four-character soundex code for character strings that are composed of a contiguous sequence of valid single- or double byte Roman letter.
soundex ( string_exp )
Returns a string with "integer_exp" single byte spacing.
space ( integer_exp )
Returns a string representation of "numeric_exp". "Integer_exp1" is the length of the returned string and has a default setting of 10. "Integer_exp2" is the number of decimal digits and has a default setting of 0. Both are optional values.
str ( numeric_exp [ , integer_exp1 [ , integer_exp2 ] ] )
Deletes "integer_exp2" characters from "string_exp1" starting at "integer_exp1", and inserts "string_exp2" into "string_exp1" at that position. To delete characters without inserting other characters, "string_exp2" should be NULL and not " ", which indicates a single space.
stuff ( string_exp1, integer_exp1, integer_exp2, string_exp2 )
Returns the substring of "string_exp" that starts at position "integer_exp1". "Integer_exp2" specifies the number of characters in the substring.
substring ( string_exp, integer_exp1, integer_exp2 )
Returns a unichar expression having the value "integer_exp". If "integer_exp" is in the range 0xD800..0xDFFF, the operation is aborted. If the "integer_exp" is in the range 0..0xFFFF, a single Unicode value is returned. If "integer_exp" is in the range 0x10000..0x10FFFF, a surrogate pair is returned.
to_unichar ( integer_exp )
Returns 1 if the Unicode value at "integer_exp" is the high half of a surrogate pair (which should appear first in the pair). Otherwise, it returns 0. This function allows you to write explicit code for surrogate handling. Particularly, if a substring starts on a Unicode character where uhighsurr() is true, extract a substring of at least 2 Unicode values, as substr() does not extract just 1. Substr() does not extract half of a surrogate pair.
uhighsurr ( string_exp, integer_exp )
Returns 1 if the Unicode value at "integer_exp" is the low half of a surrogate pair (which should appear second in the pair). Otherwise, it returns 0. This function allows you to explicitly code around the adjustments performed by substr(), stuff(), and right(). Particularly, if a substring ends on a Unicode value where ulowsurr() is true, extract a substring of 1 less characters (or 1 more), since substr() does not extract a string that contains an unmatched surrogate pair.
ulowsurr ( string_exp, integer_exp )
Returns the Unicode scalar value for the first Unicode character in "string_exp". If the first character is not the high-order half of a surrogate pair, then the value is in the range 0..0xFFFF. If the first character is the high-order half of a surrogate pair, a second value must be a low-order half, and the return value is in the range 0x10000..0x10FFFF. If this function is called on a uchar_exp containing an unmatched surrogate half, the operation is aborted.
uscalar ( string_exp )
Returns the year from date_exp.
year ( date_exp )
Returns the natural logarithm of "numeric_exp".
log ( numeric_exp )
Returns the base ten logarithm of "numeric_exp".
log10 ( numeric_exp )
Returns the constant value of pi as a floating point value.
pi ()
Returns an indicator denoting the sign of "numeric_exp": +1 if "numeric_exp" is positive, 0 if zero or -1 if negative.
sign ( numeric_exp )
Returns the arccosine of "numeric_exp" in radians. The arccosine is the angle whose cosine is "numeric_exp".
acos ( numeric_exp )
Returns the arcsine of "numeric_exp" in radians. The arcsine is the angle whose sine is "numeric_exp".
asin ( numeric_exp )
Returns the arctangent of "numeric_exp" in radians. The arctangent is the angle whose tangent is "numeric_exp".
atan ( numeric_exp )
Returns the tangent of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
tan ( numeric_exp )
Returns the angle, in radians, whose tangent is "numeric_exp1" / "numeric_exp2".
atn2 ( numeric_exp1, numeric_exp2 )
Returns the cosine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
cos ( numeric_exp )
Returns the cotangent of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
cot ( numeric_exp )
Returns "numeric_exp" radians converted to degrees.
degrees ( numeric_exp )
Returns the degree equivalent of "numeric_exp". Results are of the same type as "numeric_exp". For expressions of type numeric or decimal, the results have an internal precision of 77 and a scale equal to that of "numeric_exp". When the money datatype is used, an internal conversion to float may cause some loss of precision.
radians ( numeric_exp )
Returns the sine of "numeric_exp" where "numeric_exp" is an angle expressed in radians.
sin ( numeric_exp )
Returns the datetime resulting from adding "integer_exp" days to "timestamp_exp".
_add_days (timestamp_exp, integer_exp)
Returns the datetime resulting from adding "integer_exp" months to "timestamp_exp".
_add_months (timestamp_exp, integer_exp)
Returns the datetime resulting from adding "integer_exp" years to "timestamp_exp".
_add_years (timestamp_exp, integer_exp)
Returns a number that is obtained from subtracting "timestamp_exp" from today's date in YYYYMMDD format (years, months, days).
_age (timestamp_exp)
Returns the day of week (between 1 and 7), where 1 is the first day of the week (Monday) as indicated by "integer_exp". Note that in ISO 8601 standard, a week begins with Monday as day 1.
_day_of_week (timestamp_exp, integer_exp)
_day_of_week (2003-01-01, 1) will return 3 because 2003-01-01 was a Wednesday.
Result: 3
Returns the ordinal for the day of the year in "timestamp_ exp" (1 to 366). Also known as Julian day.
_day_of_year (timestamp_exp)
Returns a positive or negative number representing the number of days between "timestamp_exp1" and "timestamp_exp2". If "timestamp_exp1" < "timestamp_exp2", the result will be a negative number.
_days_between (timestamp_exp1, timestamp_exp2)
Returns a number representing the number of days remaining in the month represented by "timestamp_exp".
_days_to_end_of_month (timestamp_exp)
Returns a datetime that is the first day of the month represented by "timestamp_exp".
_first_of_month (timestamp_exp)
Returns a datetime that is the last day of the month represented by "timestamp_exp".
_last_of_month (timestamp_exp)
Returns a timestamp constructed from "integer_exp1" (the year), "integer_exp2" (the month) and "integer_exp3" (the day). The time portion defaults to 00:00:00.000 .
_make_timestamp (integer_exp1, integer_exp2, integer_exp3)
Returns a positive or negative number representing the number of months between "timestamp_exp1" and "timestamp_exp2". If "timestamp_exp1" < "timestamp_exp2", the result will be a negative number.
_months_between (timestamp_exp1, timestamp_exp2)
Returns the week number (1-53) of the year, represented by "timestamp_exp". According to the ISO 8601, week 1 of the year is the first week to contain a Thursday, which is equivalent to the first week containing January 4th. A week starts on a Monday (day 1) and ends on a Sunday (day 7).
_week_of_year (timestamp_exp)
Returns a positive or negative integer number representing the number of years between "timestamp_exp1" and "timestamp_exp2". If "timestamp_exp1" < "timestamp_exp2" a negative value is returned.
_years_between (timestamp_exp1, timestamp_exp2)
Returns a number representing the difference between "timestamp_exp1" and "timestamp_exp2". This value has the form YYYYMMDD, where YYYY represents the number of years, MM represents the number of months, and DD represents the number of days.
_ymdint_between (timestamp_exp1, timestamp_exp2)
Returns the absolute value of "numeric_exp". If "numeric_exp" is negative, a positive value is returned.
abs (numeric_exp)
Returns the date value of the AsOfDate expression, if it is defined. Otherwise, AsOfDate returns the report execution date.
AsOfDate ()
Returns the time value of the AsOfTime expression, if it is defined. Otherwise, AsOfTime returns the report execution time.
AsOfTime ()
Returns the burst key.
BurstKey ()
Returns the distribution list of burst recipients.
BurstRecipients ()
Returns the smallest integer greater than or equal to "numeric_exp".
ceiling (numeric_exp)
Returns the value of the current crosstab cell.
CellValue ()
Returns the number of characters in "string_exp".
character_length (string_exp)
Returns the current column number.
ColumnNumber ()
Returns the date and time when the cube was created. "Dimension" specifies from which cube to retrieve the metadata. If the dimension source is an IBM Cognos PowerCube (.mdc), the function returns a blank string. The initial creation date of a PowerCube is not maintained.
CubeCreatedOn (dimension)
Returns the current period for the cube. "Dimension" specifies from which cube to retrieve the metadata.
CubeCurrentPeriod (dimension)
Returns the date time that data in the cube was last updated. "Dimension" specifies from which cube to retrieve the metadata.
CubeDataUpdatedOn (dimension)
Returns the name of the default measure for the cube. "Dimension" specifies from which cube to retrieve the metadata.
CubeDefaultMeasure (dimension)
Returns the description of the cube. "Dimension" specifies from which cube to retrieve the metadata.
CubeDescription (dimension)
Returns TRUE if the cube is optimized. "Dimension" specifies from which cube to retrieve the metadata.
CubeIsOptimized (dimension)
Returns the name of the cube. "Dimension" specifies from which cube to retrieve the metadata.
CubeName (dimension)
Returns the date time that the cube schema was last updated. "Dimension" specifies from which cube to retrieve the metadata.
CubeSchemaUpdatedOn (dimension)
Returns the constant 'e' raised to the power of "numeric_exp". The constant 'e' is the base of the natural logarithm.
exp (numeric_exp)
Returns an integer representing the value of "datepart_exp" ("year", "month", "day", "hour", "minute", "second" (=default)) in "datetime_exp".
extract (datepart_exp , datetime_exp)
Returns the largest integer less than or equal to "numeric_exp".
floor (numeric_exp)
Returns the run locale (deprecated).
GetLocale ()
Returns the current horizontal page count.
HorizontalPageCount ()
Returns the current horizontal page number.
HorizontalPageNumber ()
Returns boolean 1 (TRUE) when the cell is in the scope of the data items and MUNs; otherwise, returns 0 (FALSE).
InScope (dataItem, MUN, ...)
Returns boolean 1 (TRUE) when the report will be distributed to the recipient; otherwise returns 0 (FALSE).
IsBursting ('recipientName') Returns boolean 1 (TRUE) if the current node is a crosstab column node member.
IsCrosstabColumnNodeMember ()
Returns boolean 1 (TRUE) if the current node is a crosstab row node member.
IsCrosstabRowNodeMember ()
Returns boolean 1 (TRUE) if the current column is the first column.
IsFirstColumn ()
Returns boolean 1 (TRUE) if the current node is an innermost crosstab column node member.
IsInnerMostCrosstabColumnNodeMember ()
Returns boolean 1 (TRUE) if the current node is an innermost crosstab row node member.
IsInnerMostCrosstabRowNodeMember ()
Returns boolean 1 (TRUE) if the current column is the last column.
IsLastColumn ()
Returns boolean 1 (TRUE) if the current node is the last innermost crosstab column node member.
IsLastInnerMostCrosstabColumnNodeMember ()
Returns boolean 1 (TRUE) if the current node is the last innermost crosstab row node member.
IsLastInnerMostCrosstabRowNodeMember ()
Returns boolean 1 (TRUE) if the current node is an outermost crosstab column node member.
IsOuterMostCrosstabColumnNodeMember ()
Returns boolean 1 (TRUE) if the current node is an outermost crosstab row node member.
IsOuterMostCrosstabRowNodeMember ()
Returns boolean 1 (TRUE) if the page count is available for the current execution of the report.
IsPageCountAvailable ()
Returns the natural logarithm of "numeric_exp".
ln (numeric_exp)
Returns the run locale.
Locale ()
Returns "string_exp" with all uppercase characters shifted to lowercase.
lower (string_exp)
Adds "integer_exp" to "string_exp".
mapNumberToLetter (string_exp, integer_exp)
mapNumberToLetter ('a', 1) will result in 'b'. Result: 'b'
Returns an integer value representing the remainder (modulo) of "integer_exp1" / "integer_exp2".
mod (integer_exp1, integer_exp2)
Returns the model path.
ModelPath ()
Returns the current system time.
Now ()
Returns NULL if "string_exp1" equals "string_exp2" (case insensitive), otherwise returns "string_exp1".
nullif (string_exp1, string_exp2)
Returns the number of bytes in "string_exp".
octet_length (string_exp)
Returns the current page count. This function works only when the report output is PDF or Excel. If you save the report output, this function works for all formats.
PageCount ()
Returns the current page name.
PageName ()
Returns the current page number.
PageNumber ()
Returns parameter count of the variable identified by "parameterName".
ParamCount ('parameterName') Returns a string that is the parameter display value of the variable identified by "parameterName".
ParamDisplayValue ('parameterName') Returns the parameter name of the variable identified by "parameterName".
ParamName ('parameterName') Returns all parameter names.
ParamNames ()
Returns a string that is the parameter value of the variable identified by "parameterName".
ParamValue ('parameterName') Returns the integer value representing the starting position of "string_exp1" in "string_exp2". Returns 0 if "string_exp1" is not found.
position (string_exp1, string_exp2)
Returns "num_exp1" raised to the power of "num_exp2".
power (num_exp1, num_exp2)
Returns the author locale.
ReportAuthorLocale()
Returns the date that the report was created.
ReportCreateDate ()
Returns the report execution date and time.
ReportDate ()
Returns the report description. This function works only when the report is run from an IBM Cognos Connection.
ReportDescription ()
Returns the report id.
ReportID ()
Returns the run locale.
ReportLocale ()
Returns the report name. This function works only when the report is run from an IBM Cognos Connection.
ReportName ()
Returns the value of the run option variable identified by "optionName". Possible values for "optionName" are: attachmentEncoding, burst, cssURL, email, emailAsAttachment, emailAsURL, emailBody, emailSubject, emailTo, emailToAddress, history, metadataModel, outputEncapsulation, outputFormat, outputLocale, outputPageDefinition, outputPageOrientation, primaryWaitThreshold, print, printer, printerAddress, prompt, promptFormat, saveAs, saveOutput, secondaryWaitThreshold, verticalElements, xslURL.
ReportOption ('optionName') Returns the name of the output format. Possible return values are: CSV, HTML, layoutDataXML, MHT, PDF, rawXML, singleXLS, spreadsheetML, XLS, XML, XLWA.
ReportOutput ()
Returns the report path. This function works only when the report is run from an IBM Cognos Connection.
ReportPath ()
Returns the product locale.
ReportProductLocale ()
Returns the date when the report was last saved.
ReportSaveDate ()
Returns "numeric_exp" rounded to the nearest value "integer_exp" places to the right of the decimal point. If "integer_exp" is negative, "numeric_exp" is rounded to the nearest absolute value "integer_exp" places to the left of the decimal point.
round (numeric_exp, integer_exp)
round (125, -1) rounds to 130.
Result: 130.
Returns the current row.
RowNumber ()
Returns the locale of the server that runs the report.
ServerLocale ()
Returns the name of the server that runs the report.
ServerName ()
Returns the square root of "numeric_exp". "Numeric_exp" must be non-negative.
sqrt (numeric_exp)
Returns the substring of "string_exp" that starts at position "integer_exp1" for "integer_exp2" characters or to the end of "string_exp" if "integer_exp2" equals -1. The first character in "string_exp" is at position 1.
substring (string_exp , integer_exp1, integer_exp2)
Returns the table of contents heading count for a specified heading level.
TOCHeadingCount (headingLevel)
Returns the current system date.
Today ()
Returns "string_exp" trimmed of any leading and trailing blanks or trimmed of the character specified by "match_character_exp". "Trim_what_exp" may be: "LEADING", "TRAILING", or "BOTH" (default). "Match_character_exp" must be an empty string to trim blanks, or specify a character to be trimmed.
trim (trim_what_exp, match_character_exp, string_exp)
Returns "string_exp" with all lowercase characters shifted to uppercase.
upper (string_exp)
Returns the url encoded value of the input text.
URLEncode ('text') Returns a date as a string in YYYY-MM-DD format.
date2string (date_exp)
Converts a date to a timestamp. The time part of the timestamp will equal zero.
date2timestamp (date_exp)
Converts a date to a timestamp with a time zone. The time and time zone parts of the timestamp will equal zero.
date2timestampTZ (date_exp)
Returns a date time interval as a string in "DDDD HH:MM:SS.FFFFFFF" or "-DDDD HH:MM:SS.FFF" format.
DTinterval2string (DTinterval_exp)
Returns a date time interval as a string in "HHHH:MM:SS.FFFFFFF" or "HH:MM:SS.FFF". Days are converted to hours.
DTinterval2stringAsTime (DTinterval_exp)
Converts an integer to a date time interval. "String_exp" specifies what "integer_exp" represents: "ns" = nanoseconds, "s" = seconds (default), "m" = minutes, "h" = hours, "d" = days.
int2DTinterval (integer_exp, string_exp)
Converts an integer to a year month interval. "String_exp" specifies what "integer_exp" represents: "y" = years, "m" = months (default).
int2YMinterval (integer_exp, string_exp)
Converts a number to a string, using the %g format specifier (C/C++ syntax).
number2string (num_exp)
Returns a date string as a date in "YYYY-MM-DD" format.
string2date (string_exp)
Returns a floating point number. "String_exp" has the following form:"[whitespace] [sign] [digits] [digits] [ {d | D |e | E }[sign]digits]"
string2double (string_exp)
Returns a date-time interval string as a date time interval in "[-]DD HH:MM[:SS[.FFF]]" format.
string2DTinterval (string_exp)
Returns an integer. "String_exp" has the following form: "[whitespace] [{+ | -}] [digits]"
string2int32 (string_exp)
Returns a long integer. "String_exp" has the following form: "[whitespace] [{+ | -}] [digits]"
string2int64 (string_exp)
Returns a time string as a time in "HH:MM:SS.FFFFFFF" format.
string2time (string_exp)
Returns a timestamp string as a timestamp in "YYYY-MM-DD [T|t|[white space]+] HH:MM:SS.FFFFFFF" format.
string2timestamp (string_exp)
Returns a timestamp with time zone "YYYY-MM-DD HH:MM:SS.FFFFFFF +HHMM" or "YYYY-MM-DD [T|t] HH:MM:SS.FFF -HHMM" format.
string2timestampTZ (string_exp)
Returns a year-month interval string as a Year Month Interval in "[-]YY MM" format.
string2YMinterval (string_exp)
Returns a time as a string in HH:MM:SS.FFF format.
time2string (time_exp)
Converts a timestamp to a date. The time part of the timestamp will be ignored.
timestamp2date (timestamp_exp)
Returns a timestamp as a string in YYYY-MM-DD HH:MM:SS.FFFFFFF format.
timestamp2string (timestamp_exp)
Converts a timestamp to a timestamp with a time zone. The displacement part of the timestamp with the time zone will be zero.
timestamp2timestampTZ (timestamp_exp)
Converts a timestamp with a time zone to a date. The time and time zone parts of the timestamp will be ignored.
timestampTZ2date (timestampTZ_exp)
Returns a timestamp with the time zone as a string in "YYYY-MM-DD HH:MM:SS.FFFFFFF +HHMM" or "YYYY-MM-DD HH:MM:SS.FFF -HHMM" format.
timestampTZ2string (timestampTZ_exp)
Converts a timestamp with time zone to a timestamp. The displacement part of the timestamp with the time zone will be ignored.
timestampTZ2timestamp (timestampTZ_exp)
Returns a time with the time zone as a string in "HH:MM:SS.FFF +HHMM" or "HH:MM:SS.FFFFFFF -HHMM" format.
timeTZ2string (timeTZ_exp)
"-05:30" means a TimeZone of GMT minus 5 hours and 30 minutes.
Result: GMT minus 5 hours and 30 minutes
Returns a year month interval as a string in "(YY MM)" or "-(YY MM)" format.
YMinterval2string (YMinterval_exp)
