Why COUNT(DISTINCT) + WHERE ON Throws Cannot construct instance source on grouped results
When querying a Prism Published Data Source, an Extend Model Business Object, or a Workday reporting data source, you often need to count the unique number of parent values (SELECT COUNT(DISTINCT field)) while filtering on a Related Business Object (RBO) using WHERE ON:
SELECT COUNT(DISTINCT cycleName)
FROM appNameWorkerDetails
WHERE ON checkStatus
statusType = 'Complete'
AND completionDate >= '2026-06-01'
AND completionDate <= '2026-07-01'
WHERE department IN (356850fb684a10dec08d71cf278f916d)When you execute this query, Workday returns HTTP 400 Bad Request with the validation error:
{
"error": "Invalid WQL query: Cannot construct instance source on grouped results."
}At first glance, this error makes it look as though WQL does not support combining COUNT(DISTINCT ...) with WHERE ON RBO filters. In reality, WQL supports COUNT(DISTINCT field) alongside WHERE ON on both Extend Business Objects and Prism data sources—provided you satisfy one critical PBO filter requirement.
Root Cause: How WHERE ON Interacts with PBO Instance Filtering During Grouping
In WQL, a WHERE ON <rbo> clause filters the instances of the Related Business Object, not the rows of the Primary Business Object (PBO).
If a PBO row has three checkStatus RBO instances and none of them match statusType = 'Complete', the WHERE ON checkStatus filter reduces that row’s checkStatus collection to empty—but the PBO row itself still remains in the result set unless the PBO WHERE clause explicitly excludes rows where checkStatus is empty!
When you execute a non-aggregated query (SELECT cycleName, checkStatus{statusType}), WQL can evaluate the WHERE ON subfilter per row after retrieving the PBO instances. However, when you ask WQL to compute an aggregate over grouped results (SELECT COUNT(DISTINCT cycleName)), the query engine must filter the PBO instance source before constructing the grouped aggregation buckets. If WHERE ON checkStatus is present without WHERE checkStatus IS NOT EMPTY in the main WHERE clause, the engine attempts to apply the RBO subfilter after grouping the PBO instances, which fails with:
Invalid WQL query: Cannot construct instance source on grouped results.The Fix: Pair WHERE ON <rbo> with WHERE <rbo> IS NOT EMPTY
To allow WQL to construct the filtered PBO instance source before evaluating COUNT(DISTINCT ...), add <rbo> IS NOT EMPTY to the main WHERE clause for every Related Business Object filtered in a WHERE ON clause:
SELECT COUNT(DISTINCT cycleName) AS distinctCycleCount
FROM appNameWorkerDetails
WHERE ON checkStatus
statusType = 'Complete'
AND completionDate >= '2026-06-01'
AND completionDate <= '2026-07-01'
WHERE
checkStatus IS NOT EMPTY
AND department IN (356850fb684a10dec08d71cf278f916d)With checkStatus IS NOT EMPTY in the WHERE clause, Workday pushes the RBO existence check (WHERE ON + IS NOT EMPTY) into the PBO instance filter before grouping and counting distinct cycleName values.
Clause Ordering and Field Type Rules for COUNT(DISTINCT) and WHERE ON
When writing combined COUNT(DISTINCT) and WHERE ON queries, verify four additional WQL rules:
WHERE ONmust always precedeWHERE: PlacingWHEREbeforeWHERE ONis a syntax error in WQL. If you filter multiple RBOs, stack eachWHERE ON <rbo>clause above the singleWHEREclause:sql SELECT COUNT(DISTINCT cycleName) FROM appNameWorkerDetails WHERE ON checkStatus statusType = 'Complete' WHERE ON location country IN (bc33aa3152ec42d4995f4791a106ed09) WHERE checkStatus IS NOT EMPTY AND location IS NOT EMPTYCOUNT(DISTINCT field)only acceptsTextandSingle Instancefields: As documented in WQL Reference: SELECT, you cannot pass aMulti-Instance,Numeric,Currency, orBooleanfield intoCOUNT(DISTINCT field).- Do not include RBO
{}fields inSELECTwhen usingCOUNT(DISTINCT): BecauseCOUNT(DISTINCT ...)is an aggregation function, addingcheckStatus{statusType}to theSELECTclause triggers"You can't use aggregation functions when the SELECT clause specifies a Related Business Object (RBO) field."KeepcheckStatusinWHERE ONandWHEREonly. - Use single quotes for date literals and never pass empty
IN ()lists: Format dates as'YYYY-MM-DD'and ensure any dynamicWHERE prismField IN (...)filter contains at least one valid value.
Using Convert Report to WQL to Verify Complex RBO Filters
If a Prism or Extend data source still rejects a complex WHERE ON condition, build a quick custom Advanced Report on the same data source:
- Add the PBO field (
cycleName) on the Columns tab. - Add the RBO subfilter (
checkStatus->statusType = 'Complete') on the Subfilter tab. - Add
checkStatusis not empty on the Filter tab. - Run the Convert Report to WQL task on that report. Workday will output the exact canonical
WHERE ONandWHEREaliases required by that data source, after which you can wrap the selected field inCOUNT(DISTINCT ...).
How to Verify in the Tenant
- Open View WQL Query Result in your tenant and first run the query without
COUNT(DISTINCT ...)—selectingcycleNameandcheckStatus{statusType}with bothWHERE ON checkStatus ...andWHERE checkStatus IS NOT EMPTY—to verify the exact rows returned. - Replace the
SELECTlist withSELECT COUNT(DISTINCT cycleName)while keeping bothWHERE ON checkStatus ...andWHERE checkStatus IS NOT EMPTY. - Confirm that the query executes without
Cannot construct instance source on grouped resultsand that the returned integer matches the distinct count ofcycleNamevalues from Step 1.