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 batch table, use:
1 SQL> 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:
1 SQL> 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:
1 SQL> 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:
1 SQL> select per.name PERIOD_NAME, per.start_date PERIOD_START_DATE, per.end_date PERIOD_END_DATE from xactly.xc_period per;
SELECT with Column Functions
Connect’s xSQL supports a library of column functions commonly used in ETL operations. Xactly Connect supports data-type conversions, string and date manipulations, aggregations/grouping, mathematical operations, as well as derived-table functions to read CSV files as a table, connect to SFDC objects, and more. For the complete list, see the xSQL and xCL Reference.
Important: Function names are case-sensitive.
For example, to use the LookupPeriodName() function, use:
1 SQL> 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:
1 SQL> select first_name, Uppercase(first_name) from xactly.xc_participant limit 10;
