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 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

      DatamartContext.Query select(String expression)
      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

      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 the DatamartContext.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(Object parts) throws Exception
      Adds a condition to the query's WHERE clause.
      Parameters:
      parts - any Object passed to the WHERE clause (rendered in the query based on the type)
      Returns:
      This DatamartContext.Query
      Throws:
      Exception
      See Also:

    • where

      DatamartContext.Query where(DatamartContext.DataSlice slice) throws Exception
      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

      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 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 = 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()
      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' Pricefx instance parameter (typically set to 100k rows).
      Parameters:
      maxRows - Maximum number of rows to return. If not set, or set to 0, then the externalRowsLimit 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.

      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.
      Possible values for the 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

      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

    • setAlias

      DatamartContext.Query setAlias(String alias)
      Parameters:
      alias -
      Returns: