Package net.pricefx.formulaengine
Interface DatamartContext.Query
- Enclosing interface:
DatamartContext
public static interface DatamartContext.Query
API for building a PA query in a formula context.
Note that most methods return the
DatamartContext.Query
object being manipulated to allow for builder-like usage.-
Method Summary
Modifier and TypeMethodDescriptiongetTable()
Adds genericFilter
s to the query's HAVING clause.Parses and ANDs one or more SQL-like conditions to the query's HAVING clause.Parses and adds one or more SQL-like conditions to the query's ORDER BY clause.Adds a projection to the query.Adds a projection to the query.Returns all columns.
Note: If the rollup is set to true (see theDatamartContext.newQuery(Table)
), then only columns matching the rollup aggregation will be returned.selectAll
(boolean fieldNameAsAlias) Adds a COUNT(*) projection to the query.Equivalent to the DISTINCT option in an SQL select statement.Projects the internal id of a row.setMaxRows
(Integer maxRows) Limits the number of rows returned in the query result.setOptions
(Map<String, Object> options) Allows for a number of query tweaking options, such as setting the target currency to convert to (if applicable) and requesting additional statistics to be calculated.setUseCache
(Boolean useCache) Allows to disable node level PA query caching just for this query.Adds genericFilter
s to the query's WHERE clause.Adds a condition to the query's WHERE clause.Parses and ANDs one or more SQL-like conditions to the query's WHERE clause.where
(CustomerGroup customerGroup) Adds a CustomerGroup condition to the query's WHERE clause, for example a customer group from an InputEntry("CustomerGroup") parameter.where
(ProductGroup productGroup) Adds a ProductGroup filter condition to the query's WHERE clause, for example a product group from an InputEntry("ProductGroup") parameter.where
(DatamartContext.DataSlice slice) Adds a condition to the query's WHERE clause.
-
Method Details
-
getTable
DatamartContext.Table getTable() -
selectDistinct
DatamartContext.Query selectDistinct()Equivalent to the DISTINCT option in an SQL select statement.- Returns:
- This
DatamartContext.Query
-
selectCount
DatamartContext.Query selectCount()Adds a COUNT(*) projection to the query.- Returns:
- This
DatamartContext.Query
-
select
Adds a projection to the query.- Parameters:
expression
- Expression referencing any queryable field in the query's source FC.- Returns:
- This
DatamartContext.Query
- See Also:
-
select
Adds a projection to the query.- Parameters:
expr
- Expression referencing any queryable field in the query's source FC.alias
- Projection alias; helpful when accessing the query result data rows.- Returns:
- This
DatamartContext.Query
-
select
-
selectAll
DatamartContext.Query selectAll()Returns all columns.
Note: If the rollup is set to true (see theDatamartContext.newQuery(Table)
), then only columns matching the rollup aggregation will be returned.- Returns:
- All columns (when a rollup is set to false).
-
selectAll
-
selectId
Projects the internal id of a row. Note that this can only be done in a line level query (no rollup/aggregation) on a table backed source, such as a DataSource and Datamart.- Parameters:
alias
- The alias for the id projection- Returns:
- This
DatamartContext.Query
-
where
Adds a condition to the query's WHERE clause.- Parameters:
parts
- anyObject
passed to the WHERE clause (rendered in the query based on the type)- Returns:
- This
DatamartContext.Query
- Throws:
Exception
- See Also:
-
where
Adds a condition to the query's WHERE clause.- Parameters:
slice
-DatamartContext.DataSlice
for which the filter representation is to be added to the query's WHERE clause.- Returns:
- This
DatamartContext.Query
- Throws:
Exception
-
where
Adds a ProductGroup filter condition to the query's WHERE clause, for example a product group from an InputEntry("ProductGroup") parameter.- Parameters:
productGroup
- ProductGroup for which the filter representation is to be added to the query's WHERE clause.- Returns:
- This
DatamartContext.Query
- Throws:
Exception
-
where
Adds a CustomerGroup condition to the query's WHERE clause, for example a customer group from an InputEntry("CustomerGroup") parameter.- Parameters:
customerGroup
- CustomerGroup for which the filter representation is to be added to the query's WHERE clause.- Returns:
- This
DatamartContext.Query
- Throws:
Exception
-
where
Parses and ANDs one or more SQL-like conditions to the query's WHERE clause.- Parameters:
whereClauses
- SQL compatible conditions.- Returns:
- This
DatamartContext.Query
-
where
Adds genericFilter
s to the query's WHERE clause.- Parameters:
filters
- One or moreFilter
s to be added to the query's WHERE clause.- Returns:
- This
DatamartContext.Query
-
having
Parses and ANDs one or more SQL-like conditions to the query's HAVING clause.- Parameters:
havingClauses
- SQL compatible conditions.- Returns:
- This
DatamartContext.Query
-
having
Adds genericFilter
s to the query's HAVING clause.Example:
Note: When aliases are defined in the dmQuery.select() calls, you need to use these aliases in the dmQuery.having() calls.def threshold = out.ShowCountThreshold ?: 0 def dmCtx = api.getDatamartContext() def dmQuery = dmCtx.newQuery(dmCtx.getDatamart("SalesDM")) dmQuery.select("customerId") dmQuery.select("COUNT(Material)", "count") dmQuery.having(Filter.greaterThan("count", threshold)) return dmCtx.executeQuery(dmQuery)?.getData()?.collect()
- Parameters:
filters
- One or moreFilter
s to be added to the query's HAVING clause.- Returns:
- This
DatamartContext.Query
-
orderBy
Parses and adds one or more SQL-like conditions to the query's ORDER BY clause.Example:
Note: When aliases are defined in the dmQuery.select() calls, you need to use these aliases in the dmQuery.orderBy() calls.def dmCtx = api.getDatamartContext() def dmQuery = dmCtx.newQuery(dmCtx.getDatamart("SalesDM")) dmQuery.select("customerId") dmQuery.select("COUNT(Material)", "count") dmQuery.orderBy("count DESC") return dmCtx.executeQuery(dmQuery)?.getData()?.collect()
- Parameters:
orderClauses
- SQL compatible conditions. Sorting direction can be specified with a ' ASC' (default) or ' DESC' postfix.- Returns:
- This
DatamartContext.Query
-
setMaxRows
Limits the number of rows returned in the query result. The absolute maximum is determined by the 'datamart.query.externalRowsLimit' Pricefx instance parameter (typically set to 100k rows).- Parameters:
maxRows
- Maximum number of rows to return. If not set, or set to 0, then theexternalRowsLimit
limit applies.- Returns:
- This
DatamartContext.Query
-
setOptions
Allows for a number of query tweaking options, such as setting the target currency to convert to (if applicable) and requesting additional statistics to be calculated.Note: every call to this method fully replaces the previous options map, do not expect any merging.
Example:def filter = out.Filter def dmCtx = api.getDatamartContext() def dm = dmCtx.getDatamart("TransactionsDM") def dmQuery = dmCtx.newQuery(dm, true) dmQuery.setOptions(["currency":"USD"]) dmQuery.select("Calendar_day", "Date") dmQuery.select("C3margin","Margin") dmQuery.where(filter) def result = dmCtx.executeQuery(dmQuery).getData() return result
Possible Options Option Param Result currency
The currency code (String) to convert money values to. Converts money values to the desired currency using the specified currency code. This method only works in DM with an appropriate setup. distinctValues
A list of projections (typically Dimension field aliases). Values sorted by the provided fieldNames (prefix the name with "-" for a descending sorting). discretization
A list of projections (numeric, date, datetime or string field aliases). Histogram values, rowcount belonging to each bin (see the bins
param below)bins
An Integer representing the desired number of bins when the DISCRETIZATION option is set. normalization
A list of projections (measure field aliases). The values for these measures will be scaled to the 0 - 1.0 range. regression
A list of projections (at least 2 measure field aliases). The values of linear regression, which can have more than two dimensions, depending on the number of specified projections. distribution
A list of projections (measure field aliases). Returns statistical values (sum, avg, stdev, min, max, mean, percentiles 5-10-20-25-30-40-50-60-70-75-80-90-95) for the measures in your query. selectData
A list of projections. Only the specified projections will be returned. statistics
A properties Map
-> key (a projection alias) : value (a list of desired stats).Does not provide data into the result matrix itself, but extra statistical projections map is attached to the first data point. statistics
param:
- "min"
- "max"
- "iqrSO" (refers to
PARAM_DM_STATISTICS_IQR_SUSPECTED_OUTLIERS
) - "iqrO" (refers to
PARAM_DM_STATISTICS_IQR_OUTLIERS
) - "iqrLIF" (refers to
PARAM_DM_STATISTICS_IQR_LOWER_INNER_FENCE
) - "iqrUIF" (refers to
PARAM_DM_STATISTICS_IQR_UPPER_INNER_FENCE
) - "iqrLOF" (refers to
PARAM_DM_STATISTICS_IQR_LOWER_OUTER_FENCE
) - "iqrUOF" (refers to
PARAM_DM_STATISTICS_IQR_UPPER_OUTER_FENCE
) - "q."+percentile (refers to quartiles. Examples: "q.01", "q.25", "q.50" (median), "q.99")
- Parameters:
options
- The map of advanced query options.- Returns:
- This
DatamartContext.Query
-
setUseCache
Allows to disable node level PA query caching just for this query.If query caching is disabled globally, this option cannot override that setting. So setting useCache to true actually has no effect.
- Parameters:
useCache
- set to false if caching should be disabled for this query- Returns:
- This
DatamartContext.Query
-
setAlias
- Parameters:
alias
-- Returns:
-