SELECT (DML)

Summary

The SELECT statement retrieves rows from a table.

Syntax

Basic SELECT Syntax
SELECT
FROM <schema_name.table_name>
WHERE
GROUP BY
HAVING
ORDER BY
UNION|UNION ALL|DIFFERENCE
LIMIT
 
DISTINCT
IN()
BETWEEN
=, <, <=, >, >=
LIKE

 

 

SELECT with JOIN
SELECT <cols>
FROM <schema_name.table_name1>
[ RIGHT, LEFT, FULL ]JOIN <schema_name.table_name1..n> ON table1.<col> = table2.<col>;

 

 

SELECT Data From SFDC Using SFDC() Table Function
SELECT [SFDC Object column1, column2..n]
FROM SFDC(TableName='<SFDC object name>', UserName='<sfdc_user_name>', Password='<sfdc_user_passwd_with_token>', Endpoint='https://login.salesforce.com/services/Soap/u/33.0');

 

 

SELECT Data From a CSV
SELECT [* or column1, column2..n]
FROM ReadFile(FilePath='<file location and name on server', FirstLineNames=<true or false>, Trim=<true or false>, BlankIsNull=<true or false>, Separator='<column delimited by char>');

Details

XSQL introduces a shortcut syntax unavailable in most other SQL dialects. For example, using the ORDER BY * clause orders a result set based on the order of columns found in the SELECT list. Aggregate functions used with GROUP BY also allow shortcut syntax.

Examples

Basic Examples
/* Example 1: The simplest SELECT statement */
SELECT * FROM xactly.xc_participant;
 
 
/* Example 2: SELECT listed columns with table alias and WHERE clause */
SELECT part.last_name, part.first_name, part.participant_id
FROM xactly.xc_participant part
WHERE Uppercase(part.last_name) = 'SMITH';
 
/* Example 3: SELECT with ORDER BY */
SELECT last_name, first_name, participant_id
FROM xactly.xc_participant
ORDER BY first_name, last_name;
/* Example 4: SELECT with ORDER BY *. This will sort the result set by the SELECT column list starting left to right. */
SELECT last_name, first_name, participant_id
FROM xactly.xc_participant
ORDER BY *;
 
/* Example 5: SELECT with AGGREGATE function and traditional GROUP BY with HAVING */
SELECT period_name, Sum(amount)
FROM xactly.xc_payment
GROUP BY period_name
HAVING Sum(amount) >= 1000;
 
/* Example 6: SELECT with AGGREGATE function, multiplication, and inferred GROUP BY */
SELECT period_name, Sum(amount * 100)
FROM xactly.xc_payment;
 
 
/* Example 7: SELECT with column alias and string concatenation */
SELECT part.last_name || ', ' || part.first_name as FULL_NAME , part.participant_id EMP_ID
FROM xactly.xc_participant part;

 

 

SELECT with JOIN Examples
/* Example 1: INNER JOIN. Note: "INNER" is optional here. Default JOIN is an INNER join. */
SELECT pay.period_name, pay.amount, ut.name
FROM xactly.xc_payment pay
INNER JOIN xactly.xc_unit_type ut ON ut.unit_type_id = pay.amount_unit_type_id;
 
/* Example 2: LEFT OUTER JOIN.  */
SELECT DISTINCT part.employee_id, part.last_name, pay.period_name
FROM xactly.xc_participant part
LEFT JOIN xactly.xc_payment pay on pay.eff_participant_id = part.participant_id and pay.period_name in ('DEC-2014', 'AUG-2014')
ORDER BY part.last_name, pay.period_name;
 
/* Example 3: RIGHT OUTER JOIN */
SELECT DISTINCT part.employee_id, part.last_name, pos.name
FROM xactly.xc_participant part
RIGHT JOIN xactly.xc_position pos on pos.participant_id = part.participant_id
ORDER BY part.last_name, pos.name;
 
/* Example 4: FULL OUTER JOIN */
SELECT DISTINCT part.employee_id, part.last_name, pos.name
FROM xactly.xc_participant part
FULL JOIN xactly.xc_position pos on pos.participant_id = part.participant_id
ORDER BY part.last_name, pos.name;

 

XSQL supports connections to SFDC objects, allowing you to use XSQL to connect to a Salesforce.com account to SELECT data from SFDC objects.

 

The default EndPoint server is https://login.salesforce.com/services/Soap/u/33.0. You can also establish connections to Saleforce.com sandbox URLs.

 

The SFDC() XSQL function returns an XSQL table. You can join the SFDC() tables to other SFDC() tables or XSQL local tables.

 

Use XSQL query syntax, not SOQL syntax. Also, all query and connection limits, governors, and so on, imposed by SFDC continue to apply.

SELECT Data From SFDC Using SFDC() Table Function
/* Example 1: SELECT data from SFDC Opportunity */
select Id, AccountId, Name, CreatedDate, CreatedById from SFDC(TableName='Opportunity', UserName='elmerfudd@acme.com', Password='GetTheWabbitg9iJG9zJ4KvAsxFfiWAW7v46');
 
/* Example 2: JOIN SFDC Opportunity and User objects */
select a.Id, a.AccountId, a.Name, CreatedDate, a.CreatedById, u.name
from SFDC(TableName='Opportunity', UserName='elmerfudd@acme.com'', Password='GetTheWabbitg9iJG9zJ4KvAsxFfiWAW7v46') a
inner join SFDC(TableName='User', UserName='elmerfudd@acme.com'', Password='GetTheWabbitg9iJG9zJ4KvAsxFfiWAW7v46') u on a.CreatedById = u.Id;

 

XSQL can also query a CSV file as a table. Note that the CSV file must be present on the Xactly Connect XSQL server.

 

SELECT Data From a CSV Example
/* SELECT all columns from Q2-2014.csv */
SELECT *
FROM ReadFile(FilePath='/DELTA/demo/Q2-2014.csv', FirstLineNames=true, Trim=true, BlankIsNull=true)

Related Commands

LIMIT