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 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()
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
setMaxRows(Integer maxRows)
Limits the number of rows returned in the query result.void
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
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
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
- An expression referencing any queryable field in the query's source FC.alias
- The 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()
-
selectAll
DatamartContext.Query selectAll(boolean fieldNameAsAlias)
-
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
- The 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
- The 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' Price f(x) instance parameter (typically set to 100k rows).- Parameters:
maxRows
- The maximum number of rows to return. If not set, or set to 0, then theexternalRowsLimit
limit applies.- Returns:
- This
DatamartContext.Query
-
-