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:
SELECT timeType
FROM allActiveAndTerminatedWorkers
WHERE workerIsContingentWorker IS NOT EMPTY
AND timeType IS NOT EMPTY
GROUP BY timeType
ORDER BY timeType ASCIn 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:
- 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
indexedAllWorkerswith a selectivedataSourceFilterandWHERE workerIsContingentWorker = true), the scanned instance count drops dramatically. - 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. - Call
Get_Referencesvia a Synchronous Orchestration: Workday’sIntegrationsSOAP service exposesGet_References(for example,Position_Time_Type_IDorContingent_Worker_Type_ID), which reads the setup table directly in milliseconds regardless of worker headcount. - 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 unindexedGROUP BYacross 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:
SELECT fruit{color, name} AS fruit, count() AS fruitCount
FROM forestDatasource
GROUP BY fruitExecuting this query returns a validation error:
[
{
"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:
- No RBO
{}traversal in grouped or aggregated queries: You cannot access fields from RBOs (field{subField1, subField2}) in any query that uses aGROUP BYclause or an aggregation function (COUNT(),SUM(),AVG(),MIN(),MAX()). - Prohibited
GROUP BYfield types: You cannot group by aggregated fields (SUM(field1)),Currencyfields, orMulti-Instancefields.
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:
SELECT fruit, count() AS fruitCount
FROM forestDatasource
GROUP BY fruitEach 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:
- Create Lookup Related Value (
LRV) Calculated Fields on the Primary Business Object: Create twoLookup Related Valuecalculated fields on the PBO (cf_fruitColorandcf_fruitName) that pullcolorandnamefromfruitonto the PBO level. Because those calculated fields belong to the PBO rather than an RBO{}block, you can include them in bothSELECTandGROUP BY:sql SELECT fruit, cf_fruitColor, cf_fruitName, count() AS fruitCount FROM forestDatasource GROUP BY fruit, cf_fruitColor, cf_fruitName - 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 thefruitsetup data source (SELECT workdayID, color, name FROM fruitsDatasource), and merge thecolorandnameproperties by matchingfruit.idin your PMD script.
How to Verify in the Tenant
- Open View WQL Query Result in an implementation or sandbox tenant with realistic data volume and test your
GROUP BYquery. - 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 (
Indexedvs.Object Graph). - Confirm that replacing a full-table
allActiveAndTerminatedWorkersscan with a setup lookup or indexed filter eliminatesError 804, and verify that grouped queries use PBO fields orLookup Related Valuecalculated fields instead of{}RBO blocks.