A Expression Editor Reference

This topic describes the expression elements that you can use in the Expression Editor.

Topics:

SQL Operators

SQL operators are used to specify comparisons between expressions.

You can use various types of SQL operators.

Operator	Description
`BETWEEN`	Determines if a value is between two non-inclusive bounds. For example: `"COSTS"."UNIT_COST" BETWEEN 100.0 AND 5000.0` `BETWEEN` can be preceded with `NOT` to negate the condition.
`IN`	Determines if a value is present in a set of values. For example: `"COSTS"."UNIT_COST" IN(200, 600, 'A')`
`IS NULL`	Determines if a value is null. For example: `"PRODUCTS"."PROD_NAME" IS NULL`
`LIKE`	Determines if a value matches all or part of a string. Often used with wildcard characters to indicate any character string match of zero or more characters (%) or any single character match (_). For example: `"PRODUCTS"."PROD_NAME" LIKE 'prod%'`

Conditional Expressions

You use conditional expressions to create expressions that convert values.

The conditional expressions described in this section are building blocks for creating expressions that convert a value from one form to another.

Note:

In CASE statements, AND has precedence over OR
Strings must be in single quotes

Expression Example Description

Expression	Example	Description
`CASE` (If)	`CASE` `WHEN score-par < 0 THEN 'Under Par'` `WHEN score-par = 0 THEN 'Par'` `WHEN score-par = 1 THEN 'Bogey'` `WHEN score-par = 2 THEN 'Double Bogey'` `ELSE 'Triple Bogey or Worse'` `END`	Evaluates each `WHEN` condition and if satisfied, assigns the value in the corresponding `THEN` expression. If none of the `WHEN` conditions are satisfied, it assigns the default value specified in the `ELSE` expression. If no `ELSE` expression is specified, the system automatically adds an `ELSE NULL`.
`CASE` (Switch)	`CASE Score-par` `WHEN -5 THEN 'Birdie on Par 6'` `WHEN -4 THEN 'Must be Tiger'` `WHEN -3 THEN 'Three under par'` `WHEN -2 THEN 'Two under par'` `WHEN -1 THEN 'Birdie'` `WHEN 0 THEN 'Par'` `WHEN 1 THEN 'Bogey'` `WHEN 2 THEN 'Double Bogey'` `ELSE 'Triple Bogey or Worse'` `END`	Also referred to as `CASE (Lookup)`. The value of the first expression is examined, then the `WHEN` expressions. If the first expression matches any `WHEN` expression, it assigns the value in the corresponding `THEN` expression. If none of the `WHEN` expressions match, it assigns the default value specified in the `ELSE` expression. If no `ELSE` expression is specified, the system automatically adds an `ELSE NULL`. If the first expression matches an expression in multiple `WHEN` clauses, only the expression following the first match is assigned.

CASE (If)

CASE

WHEN score-par < 0 THEN 'Under Par'

WHEN score-par = 0 THEN 'Par'

WHEN score-par = 1 THEN 'Bogey'

WHEN score-par = 2 THEN 'Double Bogey'

ELSE 'Triple Bogey or Worse'

END

Evaluates each WHEN condition and if satisfied, assigns the value in the corresponding THEN expression.

If none of the WHEN conditions are satisfied, it assigns the default value specified in the ELSE expression. If no ELSE expression is specified, the system automatically adds an ELSE NULL.

CASE (Switch)

CASE Score-par

WHEN -5 THEN 'Birdie on Par 6'

WHEN -4 THEN 'Must be Tiger'

WHEN -3 THEN 'Three under par'

WHEN -2 THEN 'Two under par'

WHEN -1 THEN 'Birdie'

WHEN 0 THEN 'Par'

WHEN 1 THEN 'Bogey'

WHEN 2 THEN 'Double Bogey'

ELSE 'Triple Bogey or Worse'

END

Also referred to as CASE (Lookup). The value of the first expression is examined, then the WHEN expressions. If the first expression matches any WHEN expression, it assigns the value in the corresponding THEN expression.

If none of the WHEN expressions match, it assigns the default value specified in the ELSE expression. If no ELSE expression is specified, the system automatically adds an ELSE NULL.

If the first expression matches an expression in multiple WHEN clauses, only the expression following the first match is assigned.

Functions

There are various types of functions that you can use in expressions.

Topics:

Aggregate Functions

Aggregate functions perform operations on multiple values to create summary results.

Function	Example	Description
Avg	`Avg(Sales)`	Calculates the average (mean) of a numeric set of values.
Bin	`Bin(UnitPrice BY ProductName)`	Selects any numeric attribute from a dimension, fact table, or measure containing data values and places them into a discrete number of bins. This function is treated like a new dimension attribute for purposes such as aggregation, filtering, and drilling.
Count	`Count(Products)`	Determines the number of items with a non-null value.
First	`First(Sales)`	Selects the first non-null returned value of the expression argument. The `First` function operates at the most detailed level specified in your explicitly defined dimension.
Last	`Last(Sales)`	Selects the last non-null returned value of the expression.
Max	`Max(Revenue)`	Calculates the maximum value (highest numeric value) of the rows satisfying the numeric expression argument.
Median	`Median(Sales)`	Calculates the median (middle) value of the rows satisfying the numeric expression argument. When there are an even number of rows, the median is the mean of the two middle rows. This function always returns a double.
Min	`Min(Revenue)`	Calculates the minimum value (lowest numeric value) of the rows satisfying the numeric expression argument.
StdDev	`StdDev(Sales) StdDev(DISTINCT Sales)`	Returns the standard deviation for a set of values. The return type is always a double.
StdDev_Pop	`StdDev_Pop(Sales) StdDev_Pop(DISTINCT Sales)`	Returns the standard deviation for a set of values using the computational formula for population variance and standard deviation.
Sum	`Sum(Revenue)`	Calculates the sum obtained by adding up all values satisfying the numeric expression argument.

Analytics Functions

Analytics functions allow you to explore data using models such as trendline and cluster.

Function	Example	Description
Trendline	`TRENDLINE(revenue, (calendar_year, calendar_quarter, calendar_month) BY (product), 'LINEAR', 'VALUE')`	Fits a linear or exponential model and returns the fitted values or model. The numeric_expr represents the Y value for the trend and the series (time columns) represent the X value.
Cluster	`CLUSTER((product, company), (billed_quantity, revenue), 'clusterName', 'algorithm=k-means;numClusters=%1;maxIter=%2;useRandomSeed=FALSE;enablePartitioning=TRUE', 5, 10)`	Collects a set of records into groups based on one or more input expressions using K-Means or Hierarchical Clustering.
Outlier	`OUTLIER((product, company), (billed_quantity, revenue), 'isOutlier', 'algorithm=mvoutlier')`	This function classifies a record as Outlier based on one or more input expressions using K-Means or Hierarchical Clustering or Multi-Variate Outlier detection Algorithms.
Regr	`REGR(revenue, (discount_amount), (product_type, brand), 'fitted', '')`	Fits a linear model and returns the fitted values or model. This function can be used to fit a linear curve on two measures.
Evaluate_Script	`EVALUATE_SCRIPT('filerepo://obiee.Outliers.xml', 'isOutlier', 'algorithm=mvoutlier;id=%1;arg1=%2;arg2=%3;useRandomSeed=False;', customer_number, expected_revenue, customer_age)`	Executes an R script as specified in the script_file_path, passing in one or more columns or literal expressions as input. The output of the function is determined by the output_column_name.

Calendar Functions

Calendar functions manipulate data of the data types DATE and DATETIME based on a calendar year.

Function	Example	Description
Current_Date	`Current_Date`	Returns the current date.
Current_Time	`Current_Time(3)`	Returns the current time to the specified number of digits of precision, for example: HH:MM:SS.SSS If no argument is specified, the function returns the default precision.
Current_TimeStamp	`Current_TimeStamp(3)`	Returns the current date/timestamp to the specified number of digits of precision.
DayName	`DayName(Order_Date)`	Returns the name of the day of the week for a specified date expression.
DayOfMonth	`DayOfMonth(Order_Date)`	Returns the number corresponding to the day of the month for a specified date expression.
DayOfWeek	`DayOfWeek(Order_Date)`	Returns a number between 1 and 7 corresponding to the day of the week for a specified date expression. For example, 1 always corresponds to Sunday, 2 corresponds to Monday, and so on through to Saturday which returns 7.
DayOfYear	`DayOfYear(Order_Date)`	Returns the number (between 1 and 366) corresponding to the day of the year for a specified date expression.
Day_Of_Quarter	`Day_Of_Quarter(Order_Date)`	Returns a number (between 1 and 92) corresponding to the day of the quarter for the specified date expression.
Hour	`Hour(Order_Time)`	Returns a number (between 0 and 23) corresponding to the hour for a specified time expression. For example, 0 corresponds to 12 a.m. and 23 corresponds to 11 p.m.
Minute	`Minute(Order_Time)`	Returns a number (between 0 and 59) corresponding to the minute for a specified time expression.
Month	`Month(Order_Time)`	Returns the number (between 1 and 12) corresponding to the month for a specified date expression.
MonthName	`MonthName(Order_Time)`	Returns the name of the month for a specified date expression.
Month_Of_Quarter	`Month_Of_Quarter(Order_Date)`	Returns the number (between 1 and 3) corresponding to the month in the quarter for a specified date expression.
Now	`Now()`	Returns the current timestamp. The `Now` function is equivalent to the `Current_Timestamp` function.
Quarter_Of_Year	`Quarter_Of_Year(Order_Date)`	Returns the number (between 1 and 4) corresponding to the quarter of the year for a specified date expression.
Second	`Second(Order_Time)`	Returns the number (between 0 and 59) corresponding to the seconds for a specified time expression.
TimeStampAdd	`TimeStampAdd(SQL_TSI_MONTH, 12,Time."Order Date")`	Adds a specified number of intervals to a timestamp, and returns a single timestamp. Interval options are: SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR
TimeStampDiff	`TimeStampDiff(SQL_TSI_MONTH, Time."Order Date",CURRENT_DATE)`	Returns the total number of specified intervals between two timestamps. Use the same intervals as TimeStampAdd.
Week_Of_Quarter	`Week_Of_Quarter(Order_Date)`	Returns a number (between 1 and 13) corresponding to the week of the quarter for the specified date expression.
Week_Of_Year	`Week_Of_Year(Order_Date)`	Returns a number (between 1 and 53) corresponding to the week of the year for the specified date expression.
Year	`Year(Order_Date)`	Returns the year for the specified date expression.

Conversion Functions

Conversion functions convert a value from one form to another.

Function	Example	Description
Cast	`Cast(hiredate AS CHAR(40)) FROM employee`	Changes the data type of an expression or a null literal to another data type. For example, you can cast a customer_name (a data type of Char or Varchar) or birthdate (a datetime literal). Use `Cast` to change to a Date data type. Don’t use `ToDate`.
IfNull	`IfNull(Sales, 0)`	Tests if an expression evaluates to a null value, and if it does, assigns the specified value to the expression.
IndexCol	`SELECT IndexCol(VALUEOF (NQ_SESSION.GEOGRAPHY_LEVEL), Country, State, City), Revenue FROM Sales`	Uses external information to return the appropriate column for the signed-in user to see.
NullIf	`SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID" FROM employees e, job_history j WHERE e.employee_id = j.employee_id ORDER BY last_name, "Old Job ID";`	Compares two expressions. If they’re equal, then the function returns null. If they’re not equal, then the function returns the first expression. You can’t specify the literal NULL for the first expression.
To_DateTime	`SELECT To_DateTime ('2009-03-0301:01:00', 'yyyy-mm-dd hh:mi:ss') FROM sales`	Converts string literals of dateTime format to a DateTime data type.

Display Functions

Display functions operate on the result set of a query.

Function	Example	Description
BottomN	`BottomN(Sales, 10)`	Returns the n lowest values of expression, ranked from lowest to highest.
Filter	`Filter(Sales USING Product = 'widget')`	Computes the expression using the given preaggregate filter.
Mavg	`Mavg(Sales, 10)`	Calculates a moving average (mean) for the last n rows of data in the result set, inclusive of the current row.
Msum	`SELECT Month, Revenue, Msum(Revenue, 3) as 3_MO_SUM FROM Sales`	Calculates a moving sum for the last n rows of data, inclusive of the current row. The sum for the first row is equal to the numeric expression for the first row. The sum for the second row is calculated by taking the sum of the first two rows of data, and so on. When the nth row is reached, the sum is calculated based on the last n rows of data.
NTile	`Ntile(Sales, 100)`	Determines the rank of a value in terms of a user-specified range. It returns integers to represent any range of ranks. The example shows a range from 1 to 100, with the lowest sale = 1 and the highest sale = 100.
Percentile	`Percentile(Sales)`	Calculates a percent rank for each value satisfying the numeric expression argument. The percentile rank ranges are from 0 (1st percentile) to 1 (100th percentile), inclusive.
Rank	`Rank(Sales)`	Calculates the rank for each value satisfying the numeric expression argument. The highest number is assigned a rank of 1, and each successive rank is assigned the next consecutive integer (2, 3, 4,...). If certain values are equal, they are assigned the same rank (for example, 1, 1, 1, 4, 5, 5, 7...).
Rcount	`SELECT month, profit, Rcount(profit) FROM sales WHERE profit > 200`	Takes a set of records as input and counts the number of records encountered so far.
Rmax	`SELECT month, profit, Rmax(profit) FROM sales`	Takes a set of records as input and shows the maximum value based on records encountered so far. The specified data type must be one that can be ordered.
Rmin	`SELECT month, profit, Rmin(profit) FROM sales`	Takes a set of records as input and shows the minimum value based on records encountered so far. The specified data type must be one that can be ordered.
Rsum	`SELECT month, revenue, Rsum(revenue) as RUNNING_SUM FROM sales`	Calculates a running sum based on records encountered so far. The sum for the first row is equal to the numeric expression for the first row. The sum for the second row is calculated by taking the sum of the first two rows of data, and so on.
TopN	`TopN(Sales, 10)`	Returns the n highest values of expression, ranked from highest to lowest.

Evaluate Functions

Evaluate functions are database functions that can be used to pass through expressions to get advanced calculations.

Embedded database functions can require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

Function	Example	Description
Evaluate	`SELECT EVALUATE('instr(%1, %2)', address, 'Foster City') FROM employees`	Passes the specified database function with optional referenced columns as parameters to the database for evaluation.
Evaluate_Aggr	`EVALUATE_AGGR('REGR_SLOPE(%1, %2)', sales.quantity, market.marketkey)`	Passes the specified database function with optional referenced columns as parameters to the database for evaluation. This function is intended for aggregate functions with a `GROUP BY` clause.

Mathematical Functions

The mathematical functions described in this section perform mathematical operations.

Function	Example	Description
Abs	`Abs(Profit)`	Calculates the absolute value of a numeric expression.
Acos	`Acos(1)`	Calculates the arc cosine of a numeric expression.
Asin	`Asin(1)`	Calculates the arc sine of a numeric expression.
Atan	`Atan(1)`	Calculates the arc tangent of a numeric expression.
Atan2	`Atan2(1, 2)`	Calculates the arc tangent of `y`/`x`, where `y` is the first numeric expression and `x` is the second numeric expression.
Ceiling	`Ceiling(Profit)`	Rounds a non-integer numeric expression to the next highest integer. If the numeric expression evaluates to an integer, the `CEILING` function returns that integer.
Cos	`Cos(1)`	Calculates the cosine of a numeric expression.
Cot	`Cot(1)`	Calculates the cotangent of a numeric expression.
Degrees	`Degrees(1)`	Converts an expression from radians to degrees.
Exp	`Exp(4)`	Sends the value to the power specified. Calculates `e` raised to the n-th power, where `e` is the base of the natural logarithm.
ExtractBit	`Int ExtractBit(1, 5)`	Retrieves a bit at a particular position in an integer. It returns an integer of either 0 or 1 corresponding to the position of the bit.
Floor	`Floor(Profit)`	Rounds a non-integer numeric expression to the next lowest integer. If the numeric expression evaluates to an integer, the `FLOOR` function returns that integer.
Log	`Log(1)`	Calculates the natural logarithm of an expression.
Log10	`Log10(1)`	Calculates the base 10 logarithm of an expression.
Mod	`Mod(10, 3)`	Divides the first numeric expression by the second numeric expression and returns the remainder portion of the quotient.
Pi	`Pi()`	Returns the constant value of pi.
Power	`Power(Profit, 2)`	Takes the first numeric expression and raises it to the power specified in the second numeric expression.
Radians	`Radians(30)`	Converts an expression from degrees to radians.
Rand	`Rand()`	Returns a pseudo-random number between 0 and 1.
RandFromSeed	`Rand(2)`	Returns a pseudo-random number based on a seed value. For a given seed value, the same set of random numbers are generated.
Round	`Round(2.166000, 2)`	Rounds a numeric expression to `n` digits of precision.
Sign	`Sign(Profit)`	This function returns the following: 1 if the numeric expression evaluates to a positive number -1 if the numeric expression evaluates to a negative number 0 if the numeric expression evaluates to zero
Sin	`Sin(1)`	Calculates the sine of a numeric expression.
Sqrt	`Sqrt(7)`	Calculates the square root of the numeric expression argument. The numeric expression must evaluate to a nonnegative number.
Tan	`Tan(1)`	Calculates the tangent of a numeric expression.
Truncate	`Truncate(45.12345, 2)`	Truncates a decimal number to return a specified number of places from the decimal point.

String Functions

String functions perform various character manipulations. They operate on character strings.

Function	Example	Description
Ascii	`Ascii('a')`	Converts a single character string to its corresponding ASCII code, between 0 and 255. If the character expression evaluates to multiple characters, the ASCII code corresponding to the first character in the expression is returned.
Bit_Length	`Bit_Length('abcdef')`	Returns the length, in bits, of a specified string. Each Unicode character is 2 bytes in length (equal to 16 bits).
Char	`Char(35)`	Converts a numeric value between 0 and 255 to the character value corresponding to the ASCII code.
Char_Length	`Char_Length(Customer_Name)`	Returns the length, in number of characters, of a specified string. Leading and trailing blanks aren’t counted in the length of the string.
Concat	`SELECT DISTINCT Concat ('abc', 'def') FROM employee`	Concatenates two character strings.
Insert	`SELECT Insert('123456', 2, 3, 'abcd') FROM table`	Inserts a specified character string into a specified location in another character string.
Left	`SELECT Left('123456', 3) FROM table`	Returns a specified number of characters from the left of a string.
Length	`Length(Customer_Name)`	Returns the length, in number of characters, of a specified string. The length is returned excluding any trailing blank characters.
Locate	`Locate('d' 'abcdef')`	Returns the numeric position of a character string in another character string. If the character string isn’t found in the string being searched, the function returns a value of 0.
LocateN	`Locate('d' 'abcdef', 3)`	Like Locate, returns the numeric position of a character string in another character string. LocateN includes an integer argument that enables you to specify a starting position to begin the search.
Lower	`Lower(Customer_Name)`	Converts a character string to lowercase.
Octet_Length	`Octet_Length('abcdef')`	Returns the number of bytes of a specified string.
Position	`Position('d', 'abcdef')`	Returns the numeric position of `strExpr1` in a character expression. If `strExpr1` isn’t found, the function returns 0.
Repeat	`Repeat('abc', 4)`	Repeats a specified expression `n` times.
Replace	`Replace('abcd1234', '123', 'zz')`	Replaces one or more characters from a specified character expression with one or more other characters.
Right	`SELECT Right('123456', 3) FROM table`	Returns a specified number of characters from the right of a string.
Space	`Space(2)`	Inserts blank spaces.
Substring	`Substring('abcdef' FROM 2)`	Creates a new string starting from a fixed number of characters into the original string.
SubstringN	`Substring('abcdef' FROM 2 FOR 3)`	Like Substring, creates a new string starting from a fixed number of characters into the original string. SubstringN includes an integer argument that enables you to specify the length of the new string, in number of characters.
TrimBoth	`Trim(BOTH '_' FROM '_abcdef_')`	Strips specified leading and trailing characters from a character string.
TrimLeading	`Trim(LEADING '_' FROM '_abcdef')`	Strips specified leading characters from a character string.
TrimTrailing	`Trim(TRAILING '_' FROM 'abcdef_')`	Strips specified trailing characters from a character string.
Upper	`Upper(Customer_Name)`	Converts a character string to uppercase.

System Functions

The USER system function returns values relating to the session.

It returns the user name you signed in with.

Time Series Functions

Time series functions are aggregate functions that operate on time dimensions.

The time dimension members must be at or below the level of the function. Because of this, one or more columns that uniquely identify members at or below the given level must be projected in the query.

Function	Example	Description
Ago	`SELECT Year_ID, Ago(sales, year, 1)`	Calculates the aggregated value of a measure from the current time to a specified time period in the past. For example, `AGO` can produce sales for every month of the current quarter and the corresponding quarter-ago sales.
Periodrolling	`SELECT Month_ID, Periodrolling (monthly_sales, -1, 1)`	Computes the aggregate of a measure over the period starting `x` units of time and ending `y` units of time from the current time. For example, `PERIODROLLING` can compute sales for a period that starts at a quarter before and ends at a quarter after the current quarter.
ToDate	`SELECT Year_ID, Month_ID, ToDate (sales, year)`	Aggregates a measure from the beginning of a specified time period to the currently displayed time. For example, this function can calculate Year to Date sales.
Forecast	`FORECAST(numeric_expr, ([series]), output_column_name, options, [runtime_binded_options])`	Creates a time-series model of the specified measure over the series using either Exponential Smoothing or ARMIA and outputs a forecast for a set of periods as specified by numPeriods.

Constants

You can use constants in expressions.

Available constants include Date, Time, and Timestamp.

Constant	Example	Description
Date	`DATE [2014-04-09]`	Inserts a specific date.
Time	`TIME [12:00:00]`	Inserts a specific time.
TimeStamp	`TIMESTAMP [2014-04-09 12:00:00]`	Inserts a specific timestamp.

Types

You can use data types, such as CHAR, INT, and NUMERIC in expressions.

For example, you use types when creating CAST expressions that change the data type of an expression or a null literal to another data type.