Query Business Data

The query_business_data tool reads rows from one business table, for example Products, Customers, Sellers, or a Product Extension. You can select columns, filter and sort the rows, aggregate values grouped by columns, and join other business tables. The tool returns up to 500 rows per call. To find the tables and their columns, use the List Tables tool and the Get Table Fields tool.

Tool Name – query_business_data

Title – Query Business Data

Type – Private

Access Profile – Operator

Permissions

The tool is available to the Operator access profile when tool access control is on. The query_business_data tool uses your Pricefx permissions. Pricefx applies your permissions to every query, so the tool returns only the rows that you have permission to view.

Parameters

Parameter

Required

Description

kind

Yes

The kind of business table. The values are described in the Business Table Kinds table on this page.

table_id

Depends on kind

The first identifier of the table, as the list_tables tool returns it in tableId. Required for the kinds that have an identifier in the Business Table Kinds table. Empty for every other kind.

table_id_2

No

The target date of a Company Parameter table, for companyParameterRows only. Empty for every other kind.

columns

Yes

The exact column names to return, as the get_table_fields tool returns them. Column names can contain spaces, for example Customer Id. Every column in filters must also be in columns, in aggregates[].column, or be a joined column. When aggregates is set, columns become the group-by columns. In that case columns can be empty ([]) to return one grand-total row.

filters

No

A list of filter conditions. Each condition is an object: {column, operator, value}. The tool combines all conditions with AND.

Default: No filter.

filters[].column

Yes, for each filter

The column to filter on.

filters[].operator

Yes, for each filter

The comparison: equals, notEqual, greaterThan, greaterOrEqual, lessThan, lessOrEqual, inSet, notInSet, like, ilike, notLike, or notIlike. The like operators match a text pattern: % matches any number of characters and _ matches one character. The ilike and notIlike operators ignore case. To filter a range, use two filters on the same column: greaterOrEqual and lessOrEqual.

filters[].value

Yes, for each filter

The comparison value. Use a non-empty list with inSet or notInSet. Use a single text, number, or true/false value with every other operator.

sort_by

No

One column to sort the result by: one of columns, an aggregate result column, or, without aggregation, a joined column. Add - before the name to sort in descending order, for example -Price. Ascending order puts empty values first. Descending order puts empty values last.

Default: No specific order.

max_rows

No

Maximum number of rows to return, from 1 to 500. When aggregating, the limit applies to the number of groups. The tool changes a value below 1 to 1 and a value above 500 to 500.

Default: 50.

aggregates

No

A list of aggregations, grouped by columns. Each entry is an object: {column, method, alias}. Each entry adds one column to the result.

Default: No aggregation.

aggregates[].column

Yes, except for COUNT_ALL

The column to aggregate. The column must not also be in columns.

aggregates[].method

Yes, for each aggregate

The aggregation method: SUM, AVG, MIN, MAX, COUNT_ALL (counts every row, including rows with empty values), COUNT_NON_NULL (counts rows where the column has a value), or COUNT_DISTINCT_NON_NULL (counts the different values of the column).

aggregates[].alias

No

The name of the result column.

Default: {method}_{column}, for example SUM_Price. A COUNT_ALL without a column is named COUNT_ALL.

joins

No

A list of other business tables to join, applied in the listed order.

Default: No join.

joins[].kind

Yes, for each join

The kind of the joined table, one of the kinds in the Business Table Kinds table.

joins[].table_id

Depends on the kind

The first identifier of the joined table.

joins[].table_id_2

No

The target date, for a joined companyParameterRows table only.

joins[].type

Yes, for each join

INNER keeps only rows with a match. LEFT_OUTER keeps all rows and leaves the joined columns empty when there is no match.

joins[].keys

Yes, for each join

One or more {left, right} column pairs. left is a column that exists before this join: one of columns, or a joined column from an earlier join. right is a column of the joined table. With several pairs, all of them must match.

joins[].columns

Yes, for each join

The columns of the joined table to add to the result. Each entry is an object: {column, alias}. alias is the output name, by default the column name. Can be empty ([]) to use the join only to keep or drop rows.

Business Table Kinds

Kind

Tables

table_id

products

Products (Product Master)

–

customers

Customers (Customer Master)

–

sellers

Sellers (Seller Master)

–

productCompetitions

Product Competition

–

productReferences

Product References

–

productBillOfMaterials

Product Bills of Materials

–

productExtensionRows

Product Extensions

Name of the Product Extension

customerExtensionRows

Customer Extensions

Name of the Customer Extension

sellerExtensionRows

Seller Extensions

Name of the Seller Extension

When Pricefx enables pricing tables for your partition, the following kinds are also available:

Kind

Tables

table_id

priceLists

Price Lists

–

priceListLineItems

Line items of a Price List

ID of the Price List

priceGrids

Live Price Grids

–

priceGridLineItems

Line items of a Live Price Grid

ID of the Live Price Grid

calculationGrids

Calculation Grids

–

calculationGridLineItems

Line items of a Calculation Grid

ID of the Calculation Grid

companyParameterTables

Company Parameter tables

–

companyParameterRows

Rows of a Company Parameter table

Unique name of the Company Parameter table

conditionRecords

Condition Records

Unique name of the Condition Record Set

Output

The query_business_data tool returns an object with the following fields.

Field

Description

kind

The kind of the queried table.

tableId

The first identifier of the queried table. Empty when the kind needs none.

tableId2

The second identifier of the queried table. Empty when the kind needs none.

columns

The columns in each row, in order. When aggregating, the group-by columns come first, followed by the aggregate result columns.

rows

The matching rows. Each row contains the values keyed by column name.

rowCount

The number of rows returned.

maxRows

The row limit that the tool applied, after any change to the requested value.

appliedFilters

The filter conditions that the tool applied.

aggregates

The aggregations that the tool applied: {column, method, result_column}. result_column is the column in columns and rows that holds the aggregated value.

joins

The joins that the tool applied, in order: {kind, tableId, tableId2, type, keys, columns}. Each entry in columns shows the output name that was used.

truncated

true when rowCount equals maxRows. More matching rows (or groups, when aggregating) can exist beyond the row limit.

Behavior and Limitations

  • The query_business_data tool reads one business table per call and can join other business tables to it.

  • The query_business_data tool joins only business tables. To read Datamarts, Data Sources, Rollups, or Model Object tables, use the Query Analytics Data tool. Analytics tables and business tables are stored in different databases and cannot be joined.

  • Quotes, Contracts, rebate line items, Rebate Records, compensation tables, claims, action items, and Custom Forms are not available. To read Quotes, use the quote tools, for example quote_search.

  • Line items of a Live Price Grid do not include calculation results.

  • Joins match rows by equal column values only. The tool supports INNER and LEFT_OUTER joins.

  • When one row matches several rows in a joined table, the result has one row for each match. A SUM can then count the same value more than once.

  • The tool uses a join key column that is not in columns for the join, but does not return it.

  • The tool applies filters after the joins and before aggregation. You cannot filter on an aggregated value. To find the top or bottom groups, sort by the aggregate result column instead.

  • The tool combines filter conditions with AND only. A condition that matches one of several values uses inSet.

  • max_rows limits the number of rows after the joins and aggregation.

  • A filter can fail when the type of the filter value does not match the type of the column. The get_table_fields tool shows the type of each column.

Errors

Situation

Error Message

kind is not a business table kind

Unsupported table kind 'X'. Please use one of: Y.

An identifier is given for a kind that does not take it

Table kind 'X' takes no identifier. or Table kind 'X' takes only N identifier(s): Y.

A required identifier is empty

Table kind 'X' needs Y.

An identifier has the wrong format, for example a Price List ID that is not a number

Invalid identifier for table kind 'X' (Y): Z.

A joins[].kind is not a business table kind, for example datamart

Unsupported table kind to join 'X'. Please use one of: Y.

A join has no keys

List should have at least 1 item after validation, not 0

Two joined columns have the same output name

Duplicate joined result column(s): [X]. Each joined column across all `joins` needs a distinct result column — set `alias` on the conflicting one(s) to disambiguate.

A filters[].column is not in columns, aggregates[].column, or the joined columns

[X] used in `filters` but missing from `columns`. Add them to `columns` to filter on them. The message also names aggregate columns and joined columns when the request uses them.

The sort_by column is not in columns or the result columns

'X' used in `sort_by` but missing from `columns`. The message also names the aggregate result columns when the request uses them.

An inSet or notInSet filter has no list or an empty list

'X' on 'Y' needs a non-empty list of values, got Z.

A filter with any other operator has a list or no value

'X' on 'Y' needs a single value, got Z.

An aggregation uses a method other than COUNT_ALL and has no column

aggregate method 'X' requires a column. Only COUNT_ALL can be used without one.

The same column is in both columns and aggregates[].column

'X' is both an aggregate column and one of `columns`. `columns` become the group-by dimensions when aggregating — remove it from `columns`.

Two aggregations have the same result column name

Duplicate aggregate result column(s): [X]. Each entry in `aggregates` needs a distinct result column — set `alias` to pick one explicitly.

An aggregate result column name matches a name in columns

Aggregate result column(s) [X] collide with `columns` of the same name. Set `alias` on the conflicting aggregate to a different name.

Pricefx rejects the query, for example because a column does not exist or a filter value has the wrong type

Query API rejected the request: X