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 Detail

      • getTable

        Table getTable()
      • 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
      • 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
      • 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 -