Skip to content
KiweelyKiweelyKiweely

Fixing WQL “Cannot construct instance source on grouped results” with COUNT(DISTINCT)

Combining SELECT COUNT(DISTINCT field) with a WHERE ON Related Business Object (RBO) filter in WQL throws “Invalid WQL query: Cannot construct instance source on grouped results.” unless the PBO WHERE clause also includes WHERE <rbo> IS NOT EMPTY.

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

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:

sql
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:

json
{
  "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:

text
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:

sql
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:

  1. WHERE ON must always precede WHERE: Placing WHERE before WHERE ON is a syntax error in WQL. If you filter multiple RBOs, stack each WHERE ON <rbo> clause above the single WHERE clause:
    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 EMPTY
  2. COUNT(DISTINCT field) only accepts Text and Single Instance fields: As documented in WQL Reference: SELECT, you cannot pass a Multi-Instance, Numeric, Currency, or Boolean field into COUNT(DISTINCT field).
  3. Do not include RBO {} fields in SELECT when using COUNT(DISTINCT): Because COUNT(DISTINCT ...) is an aggregation function, adding checkStatus{statusType} to the SELECT clause triggers "You can't use aggregation functions when the SELECT clause specifies a Related Business Object (RBO) field." Keep checkStatus in WHERE ON and WHERE only.
  4. Use single quotes for date literals and never pass empty IN () lists: Format dates as 'YYYY-MM-DD' and ensure any dynamic WHERE 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:

  1. Add the PBO field (cycleName) on the Columns tab.
  2. Add the RBO subfilter (checkStatus -> statusType = 'Complete') on the Subfilter tab.
  3. Add checkStatus is not empty on the Filter tab.
  4. Run the Convert Report to WQL task on that report. Workday will output the exact canonical WHERE ON and WHERE aliases required by that data source, after which you can wrap the selected field in COUNT(DISTINCT ...).

How to Verify in the Tenant

  1. Open View WQL Query Result in your tenant and first run the query without COUNT(DISTINCT ...)—selecting cycleName and checkStatus{statusType} with both WHERE ON checkStatus ... and WHERE checkStatus IS NOT EMPTY—to verify the exact rows returned.
  2. Replace the SELECT list with SELECT COUNT(DISTINCT cycleName) while keeping both WHERE ON checkStatus ... and WHERE checkStatus IS NOT EMPTY.
  3. Confirm that the query executes without Cannot construct instance source on grouped results and that the returned integer matches the distinct count of cycleName values from Step 1.

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.