DatamartContext.Query (Price f(x) Core Application 3.7 API)

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

All Methods Instance Methods Abstract Methods
Modifier and Type	Method and Description
`Table`	`getTable()`
`DatamartContext.Query`	`having(Filter... filters)` Adds generic `Filter`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(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(DatamartContext.DataSlice slice)`
`DatamartContext.Query`	`where(Filter... filters)` Adds generic `Filter`s to the query's WHERE clause.
`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(String... whereClauses)` Parses and ANDs one or more SQL-like conditions to the query's WHERE clause.

- 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 generic Filters to the query's WHERE clause.
    
    Parameters:
    
    filters - One or more Filters 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 generic Filters 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 more Filters 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 the externalRowsLimit limit applies.
    
    Returns:
    
    This DatamartContext.Query
  - setOptions
```
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.
    
    Parameters:
    
    options -

Interface DatamartContext.Query

Method Summary

Method Detail

getTable

selectDistinct

selectCount

select

select

selectAll

selectAll

where

where

where

where

where

having

having

orderBy

setMaxRows

setOptions