StreamBase Expression Language and Functions

A useful tool for understanding and testing simple expressions is the sbd --eval command, documented in the sbd help topic. Use sbd --eval to test the results of simple expressions as you read this topic, and before using them in your StreamBase applications. For example:

$ sbd --eval "1e1 * (15 % -4)"

(double) 30.0

sbd --eval does not work with aggregate functions. You must evaluate aggregate functions using other methods, such as running a test application and checking the results.

Back to Top ^

Expressions use the StreamBase static data types. The currently supported types are:

See StreamBase Data Types for details.

Back to Top ^

Expressions are case sensitive. Function names should be all lowercase.

Back to Top ^

Only use alphabetic characters, numbers, and underscores. The first character must be alphabetic or an underscore. Do not use hyphens or other special characters. Identifier rules apply to the name you assign to any StreamBase component, including schemas, fields, operators, tables, lock sets, and modules.

Back to Top ^

When you name fields, do not use the following reserved words:

In an Input Stream's Properties View, StreamBase Studio does not prevent you from specifying a reserved word for a field name. However, a schema that contains reserved words causes one or more typecheck errors in the connected downstream components. The error message in the Typecheck View identifies the offending field's name. Rename the field in the Input Stream's Property View.

Back to Top ^

If a name conflict occurs in a field name that's used in an expression within an operator instance, you can qualify them to clarify the inbound tuple stream to which you are referring.

In the Properties view for a Gather operator, you can qualify the fields as input[port-number].field-name.

Example: input1.is_alarm, input2.is_alarm, input3.is_alarm.

In the Properties view for a Join operator, you can qualify the fields in the two input streams as input1.field-name and input2.field-name. The input1.field-name refers to a field in the stream arriving at the top port (#1). The input2.field-name refers to a field in the stream arriving at the bottom port (#2).

Examples:

input1.SKU
input2.SKU

In the Properties view for a Query operator, you can qualify the fields in the two input streams as input.field-name and old.field-name. The input.field-name refers to the current input tuple, while old.field-name refers to the field's old (prior) value.

Examples:

Table1_current_Name
Nasdaq100Table_old_Symbol

Back to Top ^

You can use +, -, *, /, and %. The ^ expression is not supported.

For example, in the Compliance sample that we provide with the StreamBase kit, the is_alarm field is a bool. In one of the Compliance application's Map operators, we use this expression for the is_alarm check on each tuple:

((((sector_in_fund_value_t + (shares_traded * price_at_trade)) * 100) / fund_value_t) > 25)

This expression is designed to execute the 25% sector test; that is, one sector is not allowed to be more than 25% of the total fund value. is_alarm is set to true if the test fails.

The modulus operator ( % ) finds the remainder of a division operation. Division and modulus are always related such that a/b*b+(a%b) == a, where a/b is an integer division. For example, 15%4 can be resolved as follows: as:

15 / 4 * 4 + MOD == 15
3 * 4 + MOD = 15
12 + MOD = 15
MOD = 3

This relationship is preserved with negative divisors. As a result, whenever the quotient is negative, the modulus is negative. For example:

15 % 4 = 3
15 % -4 = 3
-15 % 4 = -3
    -15% -4 = -3

Also note that if you divide a smaller number by a larger number, the modulus is always the smaller number. For example:

4 % 15 = 4

This behavior may be different than modulus in some programming languages.

Back to Top ^

Unary operators act on only one operand in an expression. +a and -a are valid for integer and double types.

Back to Top ^

You can use the <, <=, = or ==, >, >=, and != relational operators. The relational operator <> is not supported.

Back to Top ^

You can use && or AND, || or OR, and !.

For related information on Boolean logic and nulls, see Using Nulls in StreamBase Applications.

Back to Top ^

The precedence order of mathematical operators in expressions, from highest to lowest, is as follows:

You can use parentheses in expressions to override the default precedence.

Back to Top ^

To recast data types, use one of the StreamBase type conversion functions. For example, to cast a value to a double in an expression, use the double() function. To cast a value to a string, use the string() function, and so on. Casting may not be necessary if type coercion is supported for the data types. See the next section.

Back to Top ^

Some StreamBase functions support data type coercion of input arguments from long to double data types. Type coercion is the implicit conversion of a value from one data type to another. For example, the floor function is listed in this reference as taking a double argument. However, you can enter an integer or long value instead: the function implicitly converts the value to its double equivalent.

There are only two possible data type coercions:

Observe the following rules for coercion:

Back to Top ^

The StreamBase parser ignores any spaces, new lines, and tab characters in expressions.

This section describes how to specify literals in expressions, for each data type. (For information about the data types themselves, see StreamBase Data Types).

Back to Top ^

You can use nulls in the StreamBase expression language, one for each data type:

blob(null)
bool(null)
int(null)
double(null)
long(null)
string(null)
timestamp(null)

Each null literal is case sensitive; for example, Int(NULL) is invalid. The data type of each null is never implicit: you must specify which type of null you are using in the expression.

In general, when you apply any arithmetic operator or function with data-type(null) as one of the arguments, the result is null. Three exceptions are the isnull() and notnull() functions, which test whether an expression evaluates to null, and the coalesce() function, which selects a non-null value from a list of potentially null arguments.

For related information, please see Using Nulls in StreamBase Applications.

Back to Top ^

In an expression such as: if p then e1 else e2, p must be a valid bool expression, and both e1 and e2 must be expressions that have the same type. If p is true, then e1 will be evaluated and the result returned. If p is false, e2 will be evaluated and the result returned. In either case the other sub-expression is not evaluated.

Back to Top ^

You can use combinations of if-then and else-if-then statements to form compound conditional expressions. For example:

if i==0 then "Buy" else if i==1 then "Sell" else if i==2 then "Hold" else "None of the above"

Here is a second example (indented for clarity), where we nest an if then else in a then clause:

if p1
    then
        if p2
            then 1
            else 2
else
        if p2
            then 3
            else 4

Notice how each if clause contains a then clause and an else clause.

Back to Top ^

You can enter an expression that performs concatenation. For a string variable, the concatenation expression is

stringvar + x

For example: b + 25. For a numeric variable with a static string, the concatenation expression is

"staticstring" + numvar

For example: "foo" + a. For two variables, the concatenation expression is

stringvar + numvar

For example: b+a. Note that when you use an expression to concatenate a number to a string, the total size becomes the original string size + 12.

Back to Top ^

When used with comparison operators ( == != > < <= >= ) you must compare time interval-to-interval, or timestamp-to-timestamp. You cannot use the comparison operators with interval-to-timestamp.

To express constant intervals, you can use the seconds() and minutes() functions. For example, if you want to add 60 seconds to a timestamp, you can enter expressions such as:

t + seconds(60)
t + minutes(1)

Back to Top ^

You can declare operator-parameters in the sbd.sbconf file, and then use them as parameters within StreamBase expressions. StreamBase Server Configuration XML explains how to declare operator-parameters.

To then reference a parameter in an expression, wrap the operator-parameter name in braces and prefix the open brace with a dollar sign. If you are referencing an operator-parameter that is defined as a string, use quotes around the entire reference.

For example, consider an sbd.sbconf file that defines these two operator-parameters:

<operator-parameters>
    <operator-parameter name="MyInt" value="2"/>
    <operator-parameter name="MyString" value="somestring"/>
</operator-parameters>

You could reference the first parameter (for example, in an output field) using an expression like 35* ${myInt}. The expression would evaluate at run time to 70. This StreamSQL statement references the second parameter:

SELECT * FROM InputStream1 "${MyString}" AS source

Notice the quotation marks: they are needed so that the expression is resolved as a string.

Back to Top ^

This section provides reference information for all the StreamBase functions. You can also get help on functions directly in StreamBase Studio when you edit an expression: In the Properties view, the Functions tab shows the syntax of each function and provides editing shortcuts. See Properties View for details.

StreamBase provides two types of functions:

Simple functions are evaluated over one set of arguments, and return a single result. The strlen() function is an example of a simple function. Simple functions can be used in expressions for any StreamBase operator, except Heartbeat, Metronome, and Union, which do not use an expression.

Aggregate functions are evaluated over multiple sets of arguments and return a single result. In StreamBase applications, aggregate functions are used only in Aggregate operators and in Query operators that do query read operations. The avg() function, which calculates an average value for tuples in an Aggregate window, is an example of an aggregate function.

function_name(arg1,arg2,arg3,...)

Back to Top ^

For your convenience, here is a complete, alphabetized index into the available functions provided by StreamBase, with links to the function descriptions in this section. As described in the preceding section, if you do not find a built-in function that you need here, you can use the StreamBase custom function APIs to implement your own function and configure your application to use it. For details, please see these topics in the API Guide:

Back to Top ^

This section describes the simple functions that you can use in expressions for any StreamBase operator, except the Heartbeat, Metronome, and Union operators, which do not accept an expression.

This section is organized according to categories of simple functions: