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:

 

1 SQL> 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:

 

1 SQL> 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.