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 theDatamartContext.Query
object being manipulated to allow for builder-like usage.
-
-
Method Summary
All Methods Instance Methods Abstract Methods Modifier and Type Method Description DatamartContext.Table
getTable()
DatamartContext.Query
having(Filter... filters)
Adds genericFilter
s to the query's HAVING clause.DatamartContext.Query
having(String... havingClauses)
Parses and ANDs one or more SQL-like conditions to the query's HAVING clause.DatamartContext.Query
orderBy(String... orderClauses)
Parses and adds one or more SQL-like conditions to the query's ORDER BY clause.DatamartContext.Query
select(String expr, String alias)
Adds a projection to the query.DatamartContext.Query
select(String expr, String alias, String... statistics)
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.DatamartContext.Query
selectAll(boolean fieldNameAsAlias)
DatamartContext.Query
selectCount()
Adds a COUNT(*) projection to the query.DatamartContext.Query
selectDistinct()
Equivalent to the DISTINCT option in an SQL select statement.DatamartContext.Query
selectId(String alias)
Projects the internal id of a row.DatamartContext.Query
setMaxRows(Integer maxRows)
Limits the number of rows returned in the query result.DatamartContext.Query
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.DatamartContext.Query
setUseCache(Boolean useCache)
Allows to disable node level PA query caching just for this query.DatamartContext.Query
where(Filter... filters)
Adds genericFilter
s to the query's WHERE clause.DatamartContext.Query
where(String... whereClauses)
Parses and ANDs one or more SQL-like conditions to the query's WHERE clause.DatamartContext.Query
where(CustomerGroup customerGroup)
Adds a CustomerGroup condition to the query's WHERE clause, for example a customer group from an InputEntry("CustomerGroup") parameter.DatamartContext.Query
where(ProductGroup productGroup)
Adds a ProductGroup filter condition to the query's WHERE clause, for example a product group from an InputEntry("ProductGroup") parameter.DatamartContext.Query
where(DatamartContext.DataSlice slice)
-
-
-
Method Detail
-
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
DatamartContext.Query select(String expr, String alias)
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
DatamartContext.Query select(String expr, String alias, String... statistics)
-
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
DatamartContext.Query selectAll(boolean fieldNameAsAlias)
-
selectId
DatamartContext.Query selectId(String alias)
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
DatamartContext.Query where(DatamartContext.DataSlice slice) throws Exception
- Throws:
Exception
-
where
DatamartContext.Query where(ProductGroup productGroup) throws Exception
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
DatamartContext.Query where(CustomerGroup customerGroup) throws Exception
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
DatamartContext.Query where(String... whereClauses)
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
DatamartContext.Query where(Filter... filters)
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
DatamartContext.Query having(String... havingClauses)
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
DatamartContext.Query having(Filter... filters)
Adds genericFilter
s to the query's HAVING clause.Example:
def threshold = api.getElement("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()
Note: When aliases are defined in the dmQuery.select() calls, you need to use these aliases in the dmQuery.having() calls.- Parameters:
filters
- One or moreFilter
s to be added to the query's HAVING clause.- Returns:
- This
DatamartContext.Query
-
orderBy
DatamartContext.Query orderBy(String... orderClauses)
Parses and adds one or more SQL-like conditions to the query's ORDER BY clause.Example:
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()
Note: When aliases are defined in the dmQuery.select() calls, you need to use these aliases in the dmQuery.orderBy() calls.- Parameters:
orderClauses
- SQL compatible conditions. Sorting direction can be specified with a ' ASC' (default) or ' DESC' postfix.- Returns:
- This
DatamartContext.Query
-
setMaxRows
DatamartContext.Query setMaxRows(Integer maxRows)
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
DatamartContext.Query 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.Note: every call to this method fully replaces the previous options map, do not expect any merging.
- Parameters:
options
- map of advanced query options- Returns:
- This
DatamartContext.Query
-
setUseCache
DatamartContext.Query setUseCache(Boolean useCache)
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
-
-