Use Scalar Functions

Much like other RDBMS and ETL tools, you can use the scalar functions in xSQL to:

In this chapter, we’ll show you a few examples. For a complete list of supported DML (Data Manipulation Language) and other functions, see the xSQL and xCL Reference.

IMPORTANT: Function names are case-sensitive.

 

Aggregate Rows

Aggregate functions group rows and provide summary information about those rows, depending on the function used. Common function calls are count(), sum(), avg(), min(), and max().

NOTE: Xactly has extended and streamlined SQL-92’s syntax for aggregate functions. While GROUP BY, ORDER BY, and HAVING clauses are supported, GROUP BY is optional, as are the other two in many cases, depending on the SQL statement’s requirements.

In this exercise, we’ll use count() and min() to analyze data about order stages:

select batch_name, 
count(*), 
min(incentive_date) 
from xactly.xc_order_stage;

Convert Data Types

It can be necessary to change the data type of a particular value. For example, a numeric or date value might be stored as a string from a CSV file.

In our first example, we convert a string literal to an integer:

select TypeNameOf('42'), '42', 
TypeNameOf(ToInteger('42')), ToInteger('42') 
from Empty(rows=10);

NOTE: Our example statement also used the TypeNameOf() and Empty(rows=n) functions, which we used to output a value’s data type and generate some dummy rows.

In our next example, we convert a string to a date:

select TypeNameOf('04/1/1972'), '04/1/1972', 
TypeNameOf(ToDate('04/1/1972','M/d/yyyy')), ToDate('04/1/1972','M/d/yyyy') 
from Empty(rows=5);

Manipulate Dates

You can control date formats and presentation with scalar functions.

For example, here, we convert the date_time value from effective_start_date to a string, and change the date mask:

select effective_start_date, 
FormatDateTime(effective_start_date, 'MM/dd/yyyy') || ', ' || DayName(effective_start_date) my_string
from xactly.xc_participant limit 15;

Manipulate Strings

Use string manipulation functions such as Concat() to modify strings,  combine multiple values into a single value, parse strings, and more.

In our example, we show two methods side-by-side. In the first (m1), we use Concat() to combine the string literal “Method 1: “ with the “name” column from the xactly.xc_period table. In the second example, we use the double pipe (||) method to accomplish the same task.

 

select Concat('Method 1: ', name) m1, 
'Method 2: ' || name m2 
from xactly.xc_period limit 15;

In our next exercise, we use IndexOf(), Length(), and SubString() to parse the year from a period name:

select 'APR-2015' orig_string,
IndexOf('APR-2015', '-') index_of_dash,
Length('APR-2015') len_of_orig_string,
SubString('APR-2015', IndexOf('APR-2015', '-') + 1, Length('APR-2015')) parsed_the_year
from Empty(rows=3);

 

Replace Null with a Value

Sometimes, it is necessary not to leave certain fields empty. Use Nvl() to replace null with another value:

select first_name, 
Nvl(middle_name, 'No middle name') 
from xactly.xc_participant limit 15;

Generate a Sequence of Numbers and Unique Ids

In our next exercise, we use SeqNum() and UUID() to generate number sequences (long data type) and random 128-bit Java UUIDs:

select SeqNum(1), 
UUID(), 
participant_id 
from xactly.xc_participant 
limit 20;