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 |
|---|---|---|
|
|
Yes |
The kind of business table. The values are described in the Business Table Kinds table on this page. |
|
|
Depends on |
The first identifier of the table, as the |
|
|
No |
The target date of a Company Parameter table, for companyParameterRows only. Empty for every other kind. |
|
|
Yes |
The exact column names to return, as the |
|
|
No |
A list of filter conditions. Each condition is an object: Default: No filter. |
|
|
Yes, for each filter |
The column to filter on. |
|
|
Yes, for each filter |
The comparison: |
|
|
Yes, for each filter |
The comparison value. Use a non-empty list with |
|
|
No |
One column to sort the result by: one of Default: No specific order. |
|
|
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. |
|
|
No |
A list of aggregations, grouped by Default: No aggregation. |
|
|
Yes, except for |
The column to aggregate. The column must not also be in |
|
|
Yes, for each aggregate |
The aggregation method: |
|
|
No |
The name of the result column. Default: |
|
|
No |
A list of other business tables to join, applied in the listed order. Default: No join. |
|
|
Yes, for each join |
The kind of the joined table, one of the kinds in the Business Table Kinds table. |
|
|
Depends on the kind |
The first identifier of the joined table. |
|
|
No |
The target date, for a joined companyParameterRows table only. |
|
|
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. |
|
|
Yes, for each join |
One or more |
|
|
Yes, for each join |
The columns of the joined table to add to the result. Each entry is an object: |
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 |
|---|---|
|
|
The kind of the queried table. |
|
|
The first identifier of the queried table. Empty when the kind needs none. |
|
|
The second identifier of the queried table. Empty when the kind needs none. |
|
|
The columns in each row, in order. When aggregating, the group-by columns come first, followed by the aggregate result columns. |
|
|
The matching rows. Each row contains the values keyed by column name. |
|
|
The number of rows returned. |
|
|
The row limit that the tool applied, after any change to the requested value. |
|
|
The filter conditions that the tool applied. |
|
|
The aggregations that the tool applied: |
|
|
The joins that the tool applied, in order: |
|
|
true when |
Behavior and Limitations
-
The
query_business_datatool reads one business table per call and can join other business tables to it. -
The
query_business_datatool 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
columnsfor 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_rowslimits 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_fieldstool shows the type of each column.
Errors
|
Situation |
Error Message |
|---|---|
|
|
|
|
An identifier is given for a kind that does not take it |
|
|
A required identifier is empty |
|
|
An identifier has the wrong format, for example a Price List ID that is not a number |
|
|
A |
|
|
A join has no |
|
|
Two joined columns have the same output name |
|
|
A |
|
|
The |
|
|
An |
|
|
A filter with any other operator has a list or no value |
|
|
An aggregation uses a method other than |
|
|
The same column is in both |
|
|
Two aggregations have the same result column name |
|
|
An aggregate result column name matches a name in |
|
|
Pricefx rejects the query, for example because a column does not exist or a filter value has the wrong type |
|