WQL Schema Tables

The Workday ODBC Connector enables data retrieval using Workday Query Language (WQL) within the WQL schema. The Workday Query Language (WQL) enables you to use SQL-like syntax to access Workday data using data sources and fields.

WQL enables you to query Workday for data and explore:

  • Data sources
  • Data source filters
  • Fields

For more information about WQL, see https://doc.workday.com/admin-guide/en-us/reporting-and-analytics/custom-reports-and-analytics/workday-query-language-wql-/aht1611188422513.html.

Each Workday Data Source and its fields are represented as a Single Table (with fields as columns) within the WQL schema.

WQL (Workflow Query Language) is similar to SQL but includes unique features like dataSourceFilters, Parameters, WHERE ON, and specialized operators. Find them below:

  • WQL startsWith, contains, endsWith Operators.
    Example:
    WQL: WHERE cf_JournalLabel contains 'Accounts Receivable:' AND cf_BusinessDocument startsWith '82' AND cf_BusinessDocument endsWith '90'
    SQL: WHERE cf_JournalLabel LIKE '%Accounts Receivable:%' AND cf_BusinessDocument LIKE '82%' AND cf_BusinessDocument LIKE '%90'
  • WQL is not empty Operator. This operator is also not SQL-compliant. Instead, use IS NOT NULL and <> in SQL. For numeric columns, IS NOT NULL is sufficient, while for strings, combine IS NOT NULL with <columnName> <> .
    Example:
    WQL: WHERE journalNumber is not empty AND (journalNumber startsWith '90' OR reverses is not empty)
    SQL: WHERE journalNumber IS NOT NULL AND journalNumber <> '' AND (journalNumber LIKE '90%' OR (reverses IS NOT NULL AND reverses <>))
  • Define DataSourceFilters in SQL by using the WHERE clause to represent the filter string. To know which DataSourceFilters can be applied to a DataSource Query, use “WQL”. ”DataSourceFilters” Table by passing the DataSource Id of a Datasource.
    Example: Suppose the DataSource name is “journalLines” and it’s Id is “abcd”, the below query returns all the DataSourceFilter names and it’s required parameters:SELECT DISTINCT alias, requiredParameterAlias, requiredParameterType from DataSourceFilters WHERE dataSourceId = ‘abcd'.
    WQL: (dataSourceFilter = journalFilterByAccountingDate, accountingDateOnOrAfter = '2017-01-01', accountingDateOnOrBefore = '2017-06-30', company = 'cb550da820584750aae8f807882fa79a')
    SQL: WHERE DataSourceFilterString = '(dataSourceFilter = journalFilterByAccountingDate, accountingDateOnOrAfter = 2017-01-01, accountingDateOnOrBefore = 2017-06-30, company = cb550da820584750aae8f807882fa79a)'
  • Use the WHERE clause to specify WQL query parameters in SQL:
    Example:
    WQL: PARAMETERS organizations=ec80221ba8a210255a49528540100000, endDate='2021-12-12', startDate='2021-06-06'
    SQL: WHERE ParameterString = 'PARAMETERS organizations=ec80221ba8a210255a49528540100000, endDate=2021-12-12, startDate=2021-06-06'
  • To filter results based on related business object field values, use SQL WHERE with field specifications.
    Example:
    WQL: WHERE ON location addresses is NOT EMPTY AND currency in (9e996ffdd3e14da0ba7275d5400bafd4) WHERE ON dependents legal Name_LastName starts with 'N'.
    Filtering on Single instance, or Multi Instance, or currency type fields requires Workday ID of the corresponding field value. Instead of ID lookup, you can directly filter by the Field's value using "relatedBusinessObject{field1} <filter_cond> fieldValue". For Example: to filter on Company name, "WHERE company{name} = 'Global Modern Services, Ltd (Canada)'". Alternatively, You can also use SubQueries to get the WorkdayID of values of these types like this: "WHERE company_id = (select workdayID from companies where cf_cf_Name = 'Global Modern Services, Ltd (Canada)')". However the prior approach gives better performance as it's directly filtered by WQL, but the later one is manually filtered and takes more time.
    SQL: WHERE location{addresses} <> '' AND location{currency_id} in ('9e996ffdd3e14da0ba7275d5400bafd4') AND dependents{legalName_LastName} LIKE 'N%'

    Note: For Single Instance, Multi Instance, Self-referencing instance, and currency fields, use _id if filtering by ID instead of value.

For more information about WQL features and syntax, see https://doc.workday.com/admin-guide/en-us/reporting-and-analytics/custom-reports-and-analytics/workday-query-language-wql-/wql-reference/trh1644452418127.html?toc=1.20.9.0WQL.

Data type Mapping

The following table is a list of supported WQL types:

WQL Type SQL Type
Multi instance WVARCHAR
Currency Numeric
Text WVARCHAR
Time Timestamp
Date Timestamp
Single instance WVARCHAR
Self referencing instance WVARCHAR

Limitations

  • If the column name in the WQL query is same as any SQL reserved keyword, like public, usage, or default, then it needs to be escaped using double quotations before sending it to the connector for execution.

    For example:

    SELECT instance1, usage FROM inventoryDailyUsageDataSource

    This query will not be executed and the connector will throw "Syntax error near usage<<<?>>>" since usage is a SQL reserved keyword. The query needs to be modified like this to make it work:

    SELECT instance1, "usage" FROM inventoryDailyUsageDataSource