Skip to content
KiweelyKiweelyKiweely

Fixing WQL Error 804 on GROUP BY and Related Business Object Aggregation Limits

Grouping a large transactional data source like allActiveAndTerminatedWorkers to populate dropdowns can fail with Error 804 in implementation tenants, while selecting RBO subfields with { } alongside COUNT() or GROUP BY fails validation. Learn the exact WQL grouping rules and solutions.

  • Checked against Workday on
  • Applies to Workday Query Language
  • 4 min read
  • Published
  • By Kiweely
On this page

Why GROUP BY on allActiveAndTerminatedWorkers Throws Error 804

A common anti-pattern in Workday Extend forms is querying a massive transactional worker data source such as allActiveAndTerminatedWorkers with GROUP BY solely to populate a dropdown (instanceList) of distinct setup values like timeType or contingentWorkerType:

sql
SELECT timeType
FROM allActiveAndTerminatedWorkers
WHERE workerIsContingentWorker IS NOT EMPTY
  AND timeType IS NOT EMPTY
GROUP BY timeType
ORDER BY timeType ASC

In a small WCP development tenant with a few hundred sample workers, this query returns in milliseconds. When the same app is promoted to a customer Implementation (IMPL) or Sandbox tenant containing hundreds of thousands of active and historical worker records, the query fails with Error 804.

Root Cause: Scanning Non-Indexed Transactional Populations to Extract Setup Metadata

allActiveAndTerminatedWorkers is a non-indexed transactional data source that spans every active and terminated worker in the tenant. Workday imposes a postfiltering, pregrouping limit of 1,000,000 instances on non-indexed report data sources, and evaluating object-graph security and grouping across every worker record in a large tenant hits memory/processing ceilings (Error 804).

Furthermore, WHERE workerIsContingentWorker IS NOT EMPTY does not restrict the population to contingent workers—because workerIsContingentWorker is a Boolean field, both true and false are non-empty values! Filtering for contingent workers requires WHERE workerIsContingentWorker = true.

Fixes for Error 804 When Fetching Distinct Reference Values

Never scan and group all historical worker transactions just to retrieve a list of 2 Time Type values (Full time, Part time) or a handful of Contingent Worker Type values:

  1. Query a Dedicated Setup or Indexed Data Source: If a setup/lookup data source exists for the reference object (or if you use an indexed data source such as indexedAllWorkers with a selective dataSourceFilter and WHERE workerIsContingentWorker = true), the scanned instance count drops dramatically.
  2. Use Hardcoded or Attribute Store Reference Options for Static Setup Types: Master setup values like Time Type (Full_Time, Part_Time) rarely change. Storing them in an Extend Attribute Store or static list eliminates a slow runtime query on every page load.
  3. Call Get_References via a Synchronous Orchestration: Workday’s Integrations SOAP service exposes Get_References (for example, Position_Time_Type_ID or Contingent_Worker_Type_ID), which reads the setup table directly in milliseconds regardless of worker headcount.
  4. Apply .distinct() in PMD on a Narrowed Dataset: When you already have a small scoped WQL endpoint on the page, call .distinct() in PMD script (getTimeType.data.distinct()) rather than forcing an unindexed GROUP BY across the tenant.

Why GROUP BY and Aggregations Fail When SELECT Contains RBO {...} Fields

A second GROUP BY failure occurs when you try to group by a single-instance Related Business Object (fruit) while also expanding subfields of that RBO (fruit{color, name}) inside the SELECT clause so you can display color and name as separate columns in a PMD grid:

sql
SELECT fruit{color, name} AS fruit, count() AS fruitCount
FROM forestDatasource
GROUP BY fruit

Executing this query returns a validation error:

json
[
  {
    "error": "You can't use aggregation functions when the SELECT clause specifies a Related Business Object (RBO) field.",
    "location": "Invalid SELECT clause"
  }
]

WQL’s Strict Rules on GROUP BY and RBOs

Workday documents two hard restrictions across Concept: Related Business Objects in WQL and WQL Reference: GROUP BY:

  1. No RBO {} traversal in grouped or aggregated queries: You cannot access fields from RBOs (field{subField1, subField2}) in any query that uses a GROUP BY clause or an aggregation function (COUNT(), SUM(), AVG(), MIN(), MAX()).
  2. Prohibited GROUP BY field types: You cannot group by aggregated fields (SUM(field1)), Currency fields, or Multi-Instance fields.

How to Group by an RBO Instance and Still Display Its Attributes

When you remove {color, name} from the SELECT clause, the grouped query succeeds:

sql
SELECT fruit, count() AS fruitCount
FROM forestDatasource
GROUP BY fruit

Each returned row contains the single-instance fruit object ({"descriptor": "...", "id": "..."}) and fruitCount. To display additional attributes of fruit (such as color and name) as separate columns in a PMD grid, choose one of two approaches:

  1. Create Lookup Related Value (LRV) Calculated Fields on the Primary Business Object: Create two Lookup Related Value calculated fields on the PBO (cf_fruitColor and cf_fruitName) that pull color and name from fruit onto the PBO level. Because those calculated fields belong to the PBO rather than an RBO {} block, you can include them in both SELECT and GROUP BY:
    sql
    SELECT fruit, cf_fruitColor, cf_fruitName, count() AS fruitCount
    FROM forestDatasource
    GROUP BY fruit, cf_fruitColor, cf_fruitName
  2. Join the RBO Lookup Metadata in PMD: Run the grouped query (SELECT fruit, count() AS fruitCount FROM forestDatasource GROUP BY fruit) alongside a second lightweight query against the fruit setup data source (SELECT workdayID, color, name FROM fruitsDatasource), and merge the color and name properties by matching fruit.id in your PMD script.

How to Verify in the Tenant

  1. Open View WQL Query Result in an implementation or sandbox tenant with realistic data volume and test your GROUP BY query.
  2. Enable Edit Report Performance Log Settings > WQL Log Settings for your data source, run the query, and open View Report Performance Logs to inspect Data Source Instance Count and Grouping Processing Mode (Indexed vs. Object Graph).
  3. Confirm that replacing a full-table allActiveAndTerminatedWorkers scan with a setup lookup or indexed filter eliminates Error 804, and verify that grouped queries use PBO fields or Lookup Related Value calculated fields instead of {} RBO blocks.

Sources checked

Related guides

All guides

Let Workday check the work.

Kiweely writes WQL and builds orchestrations in your own tenant, and Workday, not Kiweely, says whether they passed.

Get the next guide in your inbox.

One email a month with new guides and one Extend tip. Nothing else.

We’ll email you once to confirm. Unsubscribe in one click from any issue.