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

      • 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
      • 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).
      • 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​(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
      • 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' 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.

        Parameters:
        options - 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