Use xSQL to Query Tables

Developers, users, and programs can interact with Connect Data Services using Xactly’s implementation of the SQL-92 specification.

NOTE: While Connect does not support all functionality defined in the SQL-92 specification, many operations, commands, and functions required to solve ETL and data integration tasks have been implemented.

 

SELECT

Use SELECT to query a table or function-derived table. SELECT requires both the schema name and the table name for a given table, and returns a result set.

For example, to query the sales order batch table, use:

select period_id, batch_name, batch_status, 
item_count, created_by_name, created_date 
from xactly.xc_user_batch 
limit 20;

NOTE: In the statement above, we used the LIMIT clause to restrict our results to n rows.

 

SELECT with WHERE

Use SELECT with WHERE to narrow your row result set to specific matches.

For example, to find batches with the uppercase string ‘BATCH’ in them, use:

select period_id, batch_name, batch_status, 
item_count, created_by_name, created_date 
from xactly.xc_user_batch 
where Uppercase(batch_name) like '%FEB%';

SELECT with ORDER BY

Use SELECT with ORDER BY to sort your results.

For example, to order the period table by start date, use:

select name, start_date, end_date 
from xactly.xc_period 
order by start_date;

SELECT with Aliases

You can use SELECT with aliases.

For example, to replace the column headings with custom strings in the result set, use:

select per.name PERIOD_NAME, 
per.start_date PERIOD_START_DATE, 
per.end_date PERIOD_END_DATE 
from xactly.xc_period per;

SELECT with Scalar Functions

Connect’s xSQL supports a library of scalar functions commonly used in ETL and query operations. Xactly Connect supports data-type conversions, string and date manipulations, aggregations/grouping, mathematical operations, among others.

Connect also has derived-table functions to read CSV files as a table,  connect to and query SFDC objects, and more. Examples of derived-table functions are covered in other lessons. For the complete list, see the xSQL and xCL Reference.

Important: Function names are case-sensitive.

For example, to use the LookupPeriodName() function, use:

select incentive_date, 
LookupPeriodName(incentive_date, 'monthly') 
from xactly.xc_order_stage 
limit 10;

 

You can use the Uppercase() function to convert first names, for example, as we do here:

select first_name, 
Uppercase(first_name) 
from xactly.xc_participant 
limit 10;

JOINS

Joins are useful for working with the intersections of different tables. Connect supports INNER and OUTER joins (RIGHT, LEFT, and FULL).

For example, use an INNER JOIN to determine the period type for each row in the xc_period table:

select per.name, per.period_type_id_fk, pt.period_type_id, pt.name 
from xactly.xc_period per 
join xactly.xc_period_type pt on pt.period_type_id = per.period_type_id_fk;

 

 

In the next example, we use a LEFT OUTER JOIN to show all payees who received payments in JAN-2016 or FEB-2016:

SELECT part.employee_id, part.first_name, pay.period_name, sum(amount)
FROM xactly.xc_participant part
LEFT JOIN xactly.xc_payment pay on pay.eff_participant_id = part.participant_id 
and pay.period_name in ('JAN-2016', 'FEB-2016')
ORDER BY part.first_name, pay.period_name desc;

 

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, depending on the SQL statement’s requirements.

 

NOTE: Payees who did not receive payment in those months are listed with null values for period_name and sum.

 

INSERT

INSERT puts rows into a table. You can INSERT literal values, or use it with SELECT to add to a table dynamically.

 

INSERT into a Table

Use INSERT statements with literal values to add new rows to a table. For example:

insert into staging.order_item(batch_name, order_code, item_code, 
incentive_date, amount) 
values ('test_batch','abc123','42',ToDate('2015-04-01'), 1000000), 
('test_batch','xyz678','1001', ToDate('2015-04-02'), 66.12);

 

The status_message ‘ok’ confirms our two rows were added.

 

INSERT with SELECT

Use INSERT with SELECT to insert rows into a target table dynamically.

In our example, we’ll set a couple variables, verify they’re resolving correctly, insert some rows into a table, and verify that our rows were added.

 

1. Set variables.

First, we’ll set two variables to calculate the time period:

set v_period_end_date *= ToDate('2015-04-30');

 

 

set v_period_name *= LookupPeriodName(:v_period_end_date,'MONTHLY');

 

2. Check variables.     

Use EVAL to retrieve and verify the current value of a variable:

eval v_period_name;

3. Insert some rows.

This operation uses INSERT with a SELECT statement. Notice how we can reference the v_period_end_date variable we created a moment ago in the SQL query.

insert into staging.order_item(batch_name, 
order_code, 
item_code, 
amount, 
amount_unit_type_name, 
incentive_date) 
select 'test_batch',
employee_id || '_' || first_name,
'Trigger_' || :v_period_name,
1,
ut.name,
:v_period_end_date
from xactly.xc_participant ptp
inner join xactly.xc_unit_type ut on ut.name = 'QUANTITY'
where :v_period_end_date between ptp.effective_start_date and ptp.effective_end_date;

 

NOTE: SELECT syntax uses a colon to call a variable (:variable_name).

 

4. Check your table.

Use SELECT to verify that the items you inserted now appear in the table:

select batch_name, order_code, 
item_code, amount, 
amount_unit_type_name, incentive_date 
from staging.order_item;

UPDATE

 

UPDATE changes field values in a table. You can use it to make a simple, global change to that field, or use it with WHERE to make the change to every row in the table that has a matching value.

 

Simple UPDATE

Use UPDATE to set the value of a particular field:

update staging.order_item
set customer_name = 'Acme Inc.';

 

Check your change by retrieving that field from the table:

select customer_name, count(*) 
from staging.order_item;

Use UPDATE to Make Global Edits

To change all fields that contain the same value to a new value, use UPDATE with WHERE.

In our first example, we’ll do a simple update by setting the batch_name variable:

update staging.order_item
set batch_name = 'TriggersOrdersBatches';

 

Check your update by retrieving that field from the table:

select batch_name, count(*) 
from staging.order_item;

 

Now let’s try a more complicated example and make a batch edit. We’ll update all items where product_name is null to ‘UNKNOWN’:

update staging.order_item
set product_name = 'UNKNOWN'
where product_name is null;

DELETE

DELETE removes rows from tables.

Use DELETE with WHERE to remove all rows that contain a matching field value.

First, let’s see how many rows are in the table:

select count(*) 
from staging.order_item;

Next, we’ll delete several rows based on the value of order_code:

delete from staging.order_item 
where order_code like 'ARep%';

The command output returns the number of affected rows.

Verify the operation by counting the rows that remain:

select count(*) from staging.order_item;

 

We see that there are 36 fewer rows.

 

Conditional Expressions

Use conditional expressions to perform different computations or actions based on whether a boolean condition evaluates to true or false. To use conditional expressions within Connect’s xSQL statements and xCL expressions, use CASE or IF/THEN/ELSE.

NOTE: CASE and IF/THEN/ELSE are logically interchangeable.

 

CASE

 

In this example, we’ll use the AllTypes() table function (mostly used in testing) to run a simple computation:

SELECT integer1,
CASE WHEN integer1 > 5 AND integer1 <= 8
THEN integer1 || ' is > 5 and <= 8'
WHEN integer1 >= 9
THEN integer1 ||' is >= 9'
ELSE integer1 || ' outside of range. '
END as CASE_MESSAGE
FROM AllTypes();

In the command output, we see that the correct computation results are reflected in the CASE_MESSAGE column.

 

IF/THEN/ELSE

In our IF/THEN/ELSE example, we’ll use AllTypes() again, but this time with a simple true/false computation:

SELECT boolean1,
IF boolean1 = true
THEN 'Yay, it''s TRUE!!!'
ELSE 'Oh no! FALSE!!'
END as IF_THEN_ELSE_MESSAGE
FROM AllTypes();

 

When our condition is met, we get one result in the IF_THEN_ELSE_MESSAGE column. Otherwise, we get the other message.