Data Table Query
Use Data Table Query to read records from a data table. You choose the columns to return, filter which rows match, optionally aggregate and sort them, and read the results back a page at a time.
Results come back a page at a time rather than as one unbounded set. Page Size bounds how many records a page holds, Batch Size subdivides that page for downstream processing, and the output reports whether more records exist beyond the page returned.
The activity has seven tabs: Configuration, Columns, Conditions, Aggregation, Sorting, Input, and Output.
Before you begin
Have a data table in the workspace to query. Selecting it first is what populates the column pickers on the other tabs.
Changing the data table resets your query. Selecting a different table in Data Table clears everything you configured on the Columns, Conditions, Sorting, and Aggregation tabs. Choose the table before building the query.
Configuration
| Field | Required | Description |
|---|---|---|
| Name | Required | Name for this activity. |
| Description | Optional | Description of what this activity does. |
| Data Table | Required | Drag and drop a DataTable from the workspace. |
| Page Size | Required | Number of records in each page. Defaults to 100, and cannot exceed 1000. |
| Batch Size | Required | Number of records in each batch. Defaults to 0, which treats the entire output as a single batch. |
Page Size and Batch Size
The two settings bound different things.
Page Size bounds how many records one page holds. It defaults to 100 and cannot
exceed 1000.
Batch Size subdivides those records for downstream processing. Left at its default of
0, the entire output is treated as a single batch. Set to a number, the records are
emitted in batches of that size.
Batch Size is also what the Aggregation tab's Batch Summary is calculated over, so it affects results as well as processing shape.
Columns
| Field | Description |
|---|---|
| Add Row Number to output | If selected, you will get row number in the output. |
| Select columns to build query | Select columns to include in query results. |
The column list is populated from the table chosen in Configuration. Until a table is selected, the tab shows Select a table in the Configuration tab to view its columns here.
Conditions
Define filter conditions using AND/OR groups. Each condition pairs a column with an operator, and the operators available depend on that column's type.
Conditions within a group combine under that group's operator, and groups combine with each other — so an AND group holding two OR groups reads as (A or B) and (C or D).
| Operator | Applies to |
|---|---|
Equal To (=) | Any column type |
Not Equal To (!=) | Any column type |
Greater Than (>) | Number, Integer, Date, DateTime |
Less Than (<) | Number, Integer, Date, DateTime |
Greater Than or Equal To (>=) | Number, Integer, Date, DateTime |
Less Than or Equal To (<=) | Number, Integer, Date, DateTime |
| In | Any column type |
| Not In | Any column type |
| Between | Number, Integer, Date, DateTime |
| Is Null | Any column type |
| Is Not Null | Any column type |
| Like | String |
| Not Like | String |
Aggregation
Select Summarize to return a Page Summary and Batch Summary of the columns selected in the table below it.
That table configures aggregate functions over pages or batches. Select Add Column to add one. Each row pairs a column with a function, and as with conditions the function list is constrained by column type.
| Function | Applies to |
|---|---|
| SUM | Number, Integer |
| AVG | Number, Integer |
| MIN | String, Number, Integer |
| MAX | String, Number, Integer |
| COUNT | Any column type |
| DISTINCT | Any column type |
| COUNT DISTINCT | Any column type |
Sorting
Select columns to order by builds the sort. Only selected columns can be used for sorting. Select Add Column to add a sort rule.
| Field | Description |
|---|---|
| Function | Aggregate applied before sorting: NONE (default), MIN, MAX, COUNT, SUM, or AVG. |
| Column Name | Column to sort by. |
| Sort Type | Ascending or Descending. |
| Alias | Alternative name for the sorted column. |
Input
The Input tab holds one required mapping, Target. Output from any previous activity in the workflow is available as a source for it.
Output
The activity returns a dtQueryOutput object.
| Field | Data Type | Description |
|---|---|---|
| numRecords | Integer | Total number of records in this page. |
| hasMoreRecords | Boolean | Whether more records exist beyond current page. |
| nextOffset | Integer | Offset for the next page of results. |
| executionTimeMs | Integer | Query execution time in milliseconds. |
| records | Array | The matching records. |