Workday

SELECT Statements

A SELECT statement can consist of the following basic clauses.

• SELECT

• INTO

• FROM

• JOIN

• WHERE

• GROUP BY

• HAVING

• UNION

• ORDER BY

• LIMIT

SELECT Syntax

The following syntax diagram outlines the syntax supported by the SQL engine of the provider:

SELECT { [ TOP <numeric_literal> | DISTINCT ] { * | { <expression> [ [ AS ] <column_reference> ] | { <table_name> | <correlation_name> } .* } [ , ... ] } [ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ] { FROM <table_reference> [ [ AS ] <identifier> ] } [ , ... ] [ [ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } ] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ] ] [ ... ] [ WHERE <search_condition> ] [ GROUP BY <column_reference> [ , ... ] [ HAVING <search_condition> ] [ UNION [ ALL ] <select_statement> ] [ ORDER BY <column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ] [ LIMIT <expression> [ { OFFSET | , } <expression> ] ] } | SCOPE_IDENTITY() <expression> ::= | <column_reference> | @ <parameter> | ? | COUNT( * | { [ DISTINCT ] <expression> } ) | { AVG | MAX | MIN | SUM | COUNT } ( <expression> ) | NULLIF ( <expression> , <expression> ) | COALESCE ( <expression> , ... ) | CASE <expression> WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ] [ ELSE { <expression> | NULL } ] END | <literal> | <sql_function> <search_condition> ::= { <expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ] } [ { AND | OR } ... ]

Examples

1. Return all columns:

   SELECT * FROM [CData].[Human_Resources].Workers

2. Rename a column:

   SELECT [Legal_Name_Last_Name] AS MY_Legal_Name_Last_Name FROM [CData].[Human_Resources].Workers

3. Cast a column's data as a different data type:

   SELECT CAST(Contract_Pay_Rate AS VARCHAR) AS Str_Contract_Pay_Rate FROM [CData].[Human_Resources].Workers

4. Search data:

   SELECT * FROM [CData].[Human_Resources].Workers WHERE Legal_Name_Last_Name = 'Morgan'

5. Return the number of items matching the query criteria:

   SELECT COUNT(*) AS MyCount FROM [CData].[Human_Resources].Workers

6. Return the number of unique items matching the query criteria:

   SELECT COUNT(DISTINCT Legal_Name_Last_Name) FROM [CData].[Human_Resources].Workers

7. Return the unique items matching the query criteria:

   SELECT DISTINCT Legal_Name_Last_Name FROM [CData].[Human_Resources].Workers

8. Summarize data:

   SELECT Legal_Name_Last_Name, MAX(Contract_Pay_Rate) FROM [CData].[Human_Resources].Workers GROUP BY Legal_Name_Last_Name

   See Aggregate Functions below for details.

9. Retrieve data from multiple tables.

   SELECT Human_Resources.Workers.Worker_Reference_WID, Human_Resources.Workers.Legal_Name_Last_Name, Human_Resources.Workers_Address_Data.Municipality FROM Human_Resources.Workers INNER JOIN Human_Resources.Workers_Address_Data ON Human_Resources.Workers.Worker_Reference_WID = Human_Resources.Workers_Address_Data.Workers_Worker_Reference_WID

   See JOIN Queries below for details.

10. Sort a result set in ascending order:

    SELECT Worker_Reference_WID, Legal_Name_Last_Name FROM [CData].[Human_Resources].Workers ORDER BY Legal_Name_Last_Name ASC

11. Restrict a result set to the specified number of rows:

    SELECT Worker_Reference_WID, Legal_Name_Last_Name FROM [CData].[Human_Resources].Workers LIMIT 10

12. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.

    SELECT * FROM [CData].[Human_Resources].Workers WHERE Legal_Name_Last_Name = @param

Aggregate Functions

COUNT

Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM [CData].[Human_Resources].Workers WHERE Legal_Name_Last_Name = 'Morgan'

COUNT(DISTINCT)

Returns the number of distinct, non-null field values matching the query criteria.

SELECT COUNT(DISTINCT Worker_Reference_WID) AS DistinctValues FROM [CData].[Human_Resources].Workers WHERE Legal_Name_Last_Name = 'Morgan'

AVG

Returns the average of the column values.

SELECT Legal_Name_Last_Name, AVG(Contract_Pay_Rate) FROM [CData].[Human_Resources].Workers WHERE Legal_Name_Last_Name = 'Morgan' GROUP BY Legal_Name_Last_Name

MIN

Returns the minimum column value.

SELECT MIN(Contract_Pay_Rate), Legal_Name_Last_Name FROM [CData].[Human_Resources].Workers WHERE Legal_Name_Last_Name = 'Morgan' GROUP BY Legal_Name_Last_Name

MAX

Returns the maximum column value.

SELECT Legal_Name_Last_Name, MAX(Contract_Pay_Rate) FROM [CData].[Human_Resources].Workers WHERE Legal_Name_Last_Name = 'Morgan' GROUP BY Legal_Name_Last_Name

SUM

Returns the total sum of the column values.

SELECT SUM(Contract_Pay_Rate) FROM [CData].[Human_Resources].Workers WHERE Legal_Name_Last_Name = 'Morgan'

JOIN Queries

The Provider for Workday supports standard SQL joins like the following examples.

Inner Join

An inner join selects only rows from both tables that match the join condition:

SELECT Human_Resources.Workers.Worker_Reference_WID, Human_Resources.Workers.Legal_Name_Last_Name, Human_Resources.Workers_Address_Data.Municipality FROM Human_Resources.Workers INNER JOIN Human_Resources.Workers_Address_Data ON Human_Resources.Workers.Worker_Reference_WID = Human_Resources.Workers_Address_Data.Workers_Worker_Reference_WID

Left Join

A left join selects all rows in the FROM table and only matching rows in the JOIN table:

SELECT Human_Resources.Workers.Worker_Reference_WID, Human_Resources.Workers.Legal_Name_Last_Name, Human_Resources.Workers_Address_Data.Municipality FROM Human_Resources.Workers LEFT JOIN Human_Resources.Workers_Address_Data ON Human_Resources.Workers.Worker_Reference_WID = Human_Resources.Workers_Address_Data.Workers_Worker_Reference_WID

Date Literal Functions

The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.

L_TODAY()

The current day.

SELECT * FROM MyTable WHERE MyDateField = L_TODAY()

L_YESTERDAY()

The previous day.

SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()

L_TOMORROW()

The following day.

SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()

L_LAST_WEEK()

Every day in the preceding week.

SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()

L_THIS_WEEK()

Every day in the current week.

SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()

L_NEXT_WEEK()

Every day in the following week.

SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()

Also available:

• L_LAST/L_THIS/L_NEXT MONTH

• L_LAST/L_THIS/L_NEXT QUARTER

• L_LAST/L_THIS/L_NEXT YEAR

L_LAST_N_DAYS(n)

The previous n days, excluding the current day.

SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)

L_NEXT_N_DAYS(n)

The following n days, including the current day.

SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)

Also available:

• L_LAST/L_NEXT_90_DAYS

L_LAST_N_WEEKS(n)

Every day in every week, starting n weeks before current week, and ending in the previous week.

SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)

L_NEXT_N_WEEKS(n)

Every day in every week, starting the following week, and ending n weeks in the future.

SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)

Also available:

• L_LAST/L_NEXT_N_MONTHS(n)

• L_LAST/L_NEXT_N_QUARTERS(n)

• L_LAST/L_NEXT_N_YEARS(n)