Create and Use Global Variables

Connect’s xSQL language allows you to create global variables. These variables persist across sessions and users, and can store:

• Literal values
• Expressions that resolve to a single value
• Expressions that return result sets

There are many ways to use variables. You can use variables in other xSQL expressions and xCL expressions, such as SELECT and INSERT statements. You can set a variable in an ETL step or pipeline and use it in a later step or pipeline.

 

In this chapter, we’ll explore several common uses of variables.

For a list of the variables currently set your environment, see Show Variables.

 

SET a Variable

Use SET to define a new variable if it doesn’t already exist, and to reset the variable’s expression if it does.

The = (‘equals’) operator assigns the definition of the command or expression to the named variable:

set v_period_name = 'APR-2015';

NOTE: Avoid recursive references with the same command or expression; they result in an error upon evaluation.

Check to confirm our variable is defined:

show variables;

 

Calculate Variable Values

Variables hold expressions, not values. SET assigns an expression to a named variable, but that expression is not resolved to an actual value until it is evaluated, each time it is evaluated.

You can resolve a variable’s value directly in another xSQL or xCL expression, or explicitly with the EVAL command.

To resolve a variable only once, in advance, see Evaluate Variable Expression Once.

NOTE: A variable’s result set can have multiple columns and rows, as we’ll see in the following examples.

Use Variables in xSQL Statements

Use variables directly in command statements. Connect’s xSQL and xCL languages support variable substitution using the colon syntax:

:variable_name

In this example, we use the variable we set above with a WHERE statement in a SELECT command:

select period_id, 
name, 
start_date, 
end_date 
from xactly.xc_period 
where name = :v_period_name;

NOTE: The value of the variable expression is evaluated when the xSQL or xCL command is executed, not before.

DANGER: In the above example, the variable expression is evaluated for every row processed by the query. This could cause slow performance. Variables have a special syntax to force the value of a variable to be fixed when the variable is SET (vs. when another statement references it). More on this in a minute.

 

Use EVAL to Calculate a Variable Value

EVAL calculates the value of the expression in a variable.

In our first example, use it to retrieve a variable’s calculated value directly:

eval v_period_name;

Because EVAL returns a result set, it can be queried like a table, as we do in this slightly more interesting use of EVAL:

select * 
from (eval v_period_name);

 

Use Variables within Variables

You can even use variables to define other variables!

In this example, we use :v_period_name in a SET expression defining the new variable v_period_info:

set v_period_info = select name, 
start_date, 
end_date, 
:v_period_name as var_value 
from xactly.xc_period 
where name = :v_period_name;

In the command above, the v_period_info variable’s expression is the SELECT statement itself. The return value of the variable, that is, of its SELECT statement expression, is not determined until the variable is evaluated.

NOTE: The v_period_info variable that we just set returns a result set with many columns, not a single value.

Now let’s evaluate the new variable:

eval v_period_info;

To query a variable’s result set values, use SQL.

Here, we’ll use EVAL in a SELECT statement to retrieve the start date:

select start_date 
from (eval v_period_info);

 

Remove Variables with DROP or UNSET

Variables are persistent across sessions and users. In some cases, best practices dictate that we remove a variable. The DROP and UNSET commands both do this, and function the same way.

Let’s see our current variables:

show variables;

 

Use DROP to remove a variable:

drop variable v_period_info;

 

Use UNSET to remove a variable:

unset v_period_name;

 

Check to make sure those variables are gone:

show variables;

 

Evaluate Variable Expression Once

It can be beneficial, in some cases, to evaluate a variable only once, at creation, instead of every time it is referenced. For example, it might be wise to set the value of a variable at creation if you later reference the variable in a SELECT statement. Otherwise, Connect will evaluate the variable for each row processed by the SELECT. This is not a problem with smaller result sets. However, evaluating a variable for 20,000 rows is probably not a great idea for performance reasons.

Use *= to force Connect to evaluate a variable expression immediately, when setting the variable.

In this example, we SET the v_period_name variable, and use *= to force Connect to resolve its value in the same statement:  

The Now() scalar function illustrates the difference.

set v_now_evaluated_every_time = Now();
set v_now_evaluated_once_when_set *= Now();

eval v_now_evaluated_every_time;
eval v_now_evaluated_every_time;

eval v_now_evaluated_once_when_set;
eval v_now_evaluated_once_when_set;

 

Use Variables for Text Substitution

xSQL and xCL commands can employ runtime text substitution. The xCL parser checks the text of each command, evaluates any variables that appear in the curly-bracket {:variable} syntax, and substitutes the text of the evaluated variable directly into the command before execution. For our example, we will first define a variable to hold a comma-delimited list of columns:

set col_list = 'participant_id, first_name, hire_date, effective_start_date, effective_end_date';

Now we can use text substitution in a SELECT statement to replace the variable’s column list. Instead of entering a list of columns, we use the variable with curly-bracket syntax:

select {:col_list} 
from xactly.xc_participant;

For more applications of text substitution, see Send Email from Connect.