...
Where Filters
Limit the rows that are included in your results by applying a SQL WHERE clause. The rows that are included in the query must satisfy the filter that is provided to the where() method.
| Code Block | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
BigDecimal getExchangeRate(Date validFrom, String currencyFrom, String currencyTo){
def dataMartAPI = api.getDatamartContext()
def table = dataMartAPI.getDataSource('ccy')
def filters = [
Filter.lessOrEqual('CcyValidFrom', validFrom),
Filter.greaterThan('CcyValidTo', validFrom),
Filter.equal('CcyFrom', currencyFrom),
Filter.equal('CcyTo', currencyTo),
]
def query = dataMartAPI.newQuery(table, true)
.select('CcyExchangeRate', 'ExchangeRate')
.where(*filters) // ❶
def result = dataMartAPI.executeQuery(query)
return result.getData().findgetAt(0)?.ExchangeRate
} |
❶ Filter the rows with a WHERE filter.
Aggregation
More often than not, queries to data marts perform some sort of aggregation. Turn the query into an aggregation query by setting the second argument to newQuery() to true. Now you can wrap the fields that you want with an SQL aggregate function.
| Info |
|---|
| You can declare aliases, which are especially useful when you use aggregate functions. |
| Code Block | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
BigDecimal getTotalSales(Date dateFrom, Date dateTo){
def dataMartAPI = api.getDatamartContext()
def table = dataMartAPI.getDatamart('Transaction')
def dateFilters = [
Filter.greaterOrEqual('InvoiceDate', dateFrom),
Filter.lessThan('InvoiceDate', dateTo),
]
def query = dataMartAPI.newQuery(table, true)
.select('SUM(InvoicePrice)', 'TotalSales') // ❶
.where(*dateFilters)
def result = dataMartAPI.executeQuery(query)
return result.getData().findgetAt(0).TotalSales
} |
❶ Aggregate the InvoicePrice field values. The use of an alias means that the result Map will have the key 'TotalSales', instead of InvoicePrice.
...