---
title: "Building a Driving/Subset Query (for the Transform Rule Accelerator)"
slug: "building-a-driving-subset-query-for-the-transform-rule-accelerator-1"
updated: 2026-09-04T09:34:22Z
published: 2026-09-04T09:34:22Z
canonical: "knowledge.curiositysoftware.ie/building-a-driving-subset-query-for-the-transform-rule-accelerator-1"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://knowledge.curiositysoftware.ie/llms.txt
> Use this file to discover all available pages before exploring further.

# Building a Driving/Subset Query (for the Transform Rule Accelerator)

This page covers how to build a piece of subset SQL that auto-builds scripts across related tables. That SQL is useful on its own (for quick subsetting or data exploration), and it's also useful in applying it to the **transform rule accelerator** at to auto-populate a Transform's sources and targets, see [Setting Up a Transform Data Activity](/kb/docs/setting-up-a-transform-data-activity).

### 1. Add the tables into a subset definition

Open a subset definition and add the tables you're interested in - you don't need to be precise or turn anything off at this point.

![Screenshot of a subset definition UI showing a rules table with multiple tables listed, badges indicating column attributes, group dropdowns (Driving Table / Subset Table), and action controls on the right](https://cdn.document360.io/77f722a6-2d0a-49fa-8074-572515a6c4b8/Images/Documentation/building-a-drivingsubset-query-for-the-transform-rule-accelerator_page_0001_img_01.png)

### 2. Add driving criteria as a variable

Give the subset some driving criteria (via the Driving table) by writing a query that uses the `${...}` curly-brace variable nomenclature (e.g. a customer-ID filter). Using this nomenclature is what lets the platform convert the criteria into a variable, one that gets substituted with a real value later, whether that's from a generated form or filled in by hand.

![Dialog titled Edit DRIVER.CUSTOMER_ID IN (%1) showing tabs Basic Info and Query Details, a Type field set to Find Criteria and a code editor containing the query: DRIVER.CUSTOMER_ID in (${CUSTOMERIDS})](https://cdn.document360.io/77f722a6-2d0a-49fa-8074-572515a6c4b8/Images/Documentation/building-a-drivingsubset-query-for-the-transform-rule-accelerator_page_0002_img_01.png)

### 3. Generate the scripts and run row counts

Fill in the connection details (e.g. SQL Server, source database) and use `create SQL` and run `Preview row count` to execute against your driving criteria. This does two things at once:

- Runs a row count based on the criteria you entered (e.g. a specific set of customers), so you can see the resulting data volumes before committing to anything.
- Builds the underlying SQL scripts. For simple/reference tables (e.g. `Customers`) this is just a straightforward filter (`WHERE 1=1`-style); for related child tables further down the chain (e.g. `Warehouses`), the tool automatically constructs the joins needed to get there, walking from the driving table through every intermediate table (e.g. `Customers -> Orders -> Order Items -> Products -> Inventories -> Warehouses`) without you having to write any of the joins yourself.

![Tabbed interface showing Logs and Results tabs and a Run Results panel listing multiple tables with row counts (e.g. OT.CUSTOMERS 3 Rows of Data, OT.INVENTORIES 251 Rows of Data, TOTAL ROWS FOUND= 5731).](https://cdn.document360.io/77f722a6-2d0a-49fa-8074-572515a6c4b8/Images/Documentation/building-a-drivingsubset-query-for-the-transform-rule-accelerator_page_0003_img_01.png)

### 4. Preview the data before running the full subset

You can substitute a real value into the generated `${...}` placeholder directly in the SQL (e.g. swap it for an actual ID), copy that SQL into a SQL browser/query window, and run it there. This is a quick way to see the exact data your criteria will pull before you commit to running the full subset into a new schema and browsing it there.

![Screenshot of a SQL editor window with a SELECT query visible; a WHERE clause WHERE DRIVER.CUSTOMER_ID IN (1,2,3) is highlighted in red, and a results grid with customer rows is shown below the query.](https://cdn.document360.io/77f722a6-2d0a-49fa-8074-572515a6c4b8/Images/Documentation/building-a-drivingsubset-query-for-the-transform-rule-accelerator_page_0003_img_02.png)

### 5. Handling multiple routes between tables

Sometimes there's more than one way to reach a table - e.g. a `Supplier` table might be reachable both via `Purchase Orders → Purchase Order Lines` and via a `Transactions` table. By default the tool builds an `OR` clause covering every route it finds. If you don't need to distinguish between them, toggle **only find one route between the driving and child tables** so it stops hunting for alternate paths once it's found the first one, this keeps the generated script simpler.

![Screenshot of a dialog titled Data Activity - Create SQL and run Preview row count - Job Parameters showing the Parameters tab with fields like Run SQL script in this connection and Script Types, checkboxes including Subset SQL scripts - Run the row counts as well (highlighted with a red box), and action buttons Cancel and Execute](https://cdn.document360.io/77f722a6-2d0a-49fa-8074-572515a6c4b8/Images/Documentation/building-a-drivingsubset-query-for-the-transform-rule-accelerator_page_0004_img_01.png)

## Using in the Transform Data Activity

Once you have subset SQL you're happy with, it's ready to feed straight into the transform rule accelerator, see step 4 of [Setting Up a Transform Data Activity](/kb/docs/setting-up-a-transform-data-activity)

### Notes

- Use **only find one route between the driving and child tables** if you don't need every alternate path a table can be reached by, it keeps the generated script (and therefore what the accelerator picks up) simpler.
- You can sanity-check a subset query before committing to a full run: substitute a real value for the `${...}` placeholder and run the generated SQL directly in a SQL window.